Связывание рабочих листов с помощью формул, содержащих адресные ссылки

Необходимо поместить цену товара - телефона (в долларах) в ячейку С7. Для этого:

Ø Щелкните мышью в ячейку С7 и вызовите диалоговое окно Мастера функций, рис.10;

Ø В списке Категория выберите строку Ссылки и массивы;

Ø В списке Функция выберите строку Индекс, откроется диалоговое окно, рис.11;

Ø В поле ввода Массив введите ячейки G3:G15 из рабочего листа Телефон;

Ø В поле ввода Номер строки – ячейку G7 из рабочего листа Стоимость заказа, рис.12;

Ø В итоге должна получиться формула:

= ИНДЕКС (Телефоны! G3:G15;G7)

Ø Нажмите кнопку Ок. Диалоговое окно исчезнет, а в ячейке С7 появится цена выбранного телефона, пока она равна нулю;

ð Если ввести, на листе Телефон в столбце F – Реализовано, количество реализованного товара, то цена будет подсчитана автоматически

Ø Установите в ячейке С7 листа Расчет стоимости числовой формат для суммы в долларах;

Рис.10.Вызов Мастера функций

Рис.11.Выбор аргумента функции

Рис.12.Задание массива и номера строки функции

ð

Обратите внимание, что ячейка G7 служит для технических надобностей; по этому номеру строки происходит связывание цены с выбранным наименованием товара. Поэтому рекомендуется содержимое ячейки G7 сделать невидимым, например, открыть список цветов шрифта и выбрать белый цвет. В итоге наша смета примет вид, как на рис.13.

Рис.13. Расчет стоимости телефона.

5. Связывание рабочих листов: Стоимость заказа –Автомагнитола, Утюг, Электрочайник, Кухонный комбайн

Для связывания остальных рабочих листов, необходимо повторить все действия, выполненные в задании 2:

Ø Последовательно, для каждого наименования товара, установить на основном рабочем листе Стоимость заказа элемент управления, с помощью которого будут просматриваться списки товара;

Ø Связать последовательно ячейки на основном рабочем листе с ценой выбранного наименования товара;

Ø Оформить надлежащим образом внешний вид ячеек, как показано на рис.14.

Рис.14. Расчет стоимости каждого вида товара

ð Для просмотра и выбора из списка товара можно использовать другой тип элемента управления – Счетчик. При заполнении диалогового окна Форматирование объекта, необходимо указать Начальное значение, Мин. значение, Мах. значение и Шаг изменения, которые можно ввести с клавиатуры. Смысл параметров понятен из их назначения. Они задают интервал значений счетчика, начальное его значение и шаг изменения при каждом нажатии на стрелку. Интервал значений счетчика представляет собой количество строк списка товара. Результат – выбранное значение счетчика, - помещается в указанную ячейку для дальнейшего использования.

ЗАДАНИЕ 4. СОЗДАНИЕ ОСНОВНОГО РАБОЧЕГО ЛИСТА «СКЛАДСКОЙ УЧЕТ»

Построение основного рабочего листа Складской учет можно сравнить с так называемой базой данных. Под базой данных понимается совокупность сведений о конкретных объектах реального мира в какой-либо предметной области. В данном случае мы имеем базу данных складского учета. Данными здесь выступает номенклатура товара.

При работе с базой данных можно осуществлять различные операции, такие как, сортировка списка товара, фильтрация списка, вывод промежуточных итогов, применение вычисляемых критериев, формирование итогов по заданным условиям и т.д.

Можно предположить следующую экономическую ситуацию. Фирма не имеет возможности или нет желания приобрести дорогостоящий программный продукт по ведению складского учета. Тем не менее, такие операции, как поступление товара, отпуск товара, реализация, остаток на складе должны быть, по возможности, автоматизированы. Полностью автоматизировать складской учет в Excel не представляется возможным. Какие инструменты и функции для этого может предложить Excel? Во-первых, использование математических и логических функций. Во-вторых, связывание рабочих листов и работа как с базой данных. И, наконец, автоматизация часто повторяемых действий, т.е. использование макроса.

ð Наша рабочая схема будет предполагать следующее:

- Остаток на начало рабочего периода (месяц) и приход (поступление в течении месяца) будут заноситься в соответствующие колонки вручную;

- Количество реализованного товара на листе «Складской учет» будет выполняться автоматически нарастающим итогом, по мере того, как будет выполнен заказ. Для этого, на рабочем листе «Стоимость заказа», необходимо создать дополнительную кнопку «Подтвердить заказ». Эта кнопка позволит обнулять количество реализации товара на листах «Телефон», «Автомагнитола» и т.д. по мере выполнения заказа и, одновременно, учитывать его (прибавлять) на листе «Складской учет»; выполнение этого пункта предполагается в задании 8;

- Остальные колонки – остаток на конец периода и т.д. – будут считаться по формулам.

Дата добавления: 2015-08-27