1.2. Создание списка «Клиент»

Для создания списка «Клиент» выполните следующие знания.

Задание 9. Присвоение имени рабочему листу

Перейдите на второй лист рабочей книги, щелкните по ярлычку «Лист 2» и присвойте ему имя «Клиент».

Задание 10. Создание списка «Справочник клиентов»

Для создания списка проделайте следующее:

• создайте список с заголовком «Справочник клиентов» из пяти столбцов: «Код клиента», «Клиент», «Расчетный счет», «Отделение банка», «Скидка». Введите указанные заголовки в ячейки А2 — Е2;

• отформатируйте ширину колонок;

• введите данные согласно таблице 2.

Данные можно вводить непосредственно в ячейки или воспользоваться командой «Форма» из меню «Данные».

Таблица 2. Справочник клиентов

	А	В	С	D	Е
1	Справочник клиентов
2	Код клиента	Клиент	Расчетный счет	Отделение банка	Скидка
4	11	Магазин № 8	9247090	Белагропромбанк	0,05
5	12	Ветковское ПО	15450506	Агропромбанк	0,03
6	13	Столбуновское ПО	15470602	Агропромбанк	0,03
7	14	Магазин № 29	76554999	Промстройбанк	0
8	15	Магазин № 48	74873458	Промстройбанк	0

После ввода данных отформатируйте таблицу, используя автоформат, например, «Список 3» или по выбору, или применив индивидуальное форматирование. Для этого выполните следующее:

• заголовок выделите, отцентрируйте, назначьте шрифт заголовку;

• выделите строку 2 с заголовками колонок, отцентрируйте, назначьте шрифт по выбору;

• выполните обрамление.

Задание 11. Назначение имен диапазонам

Присвойте имена столбцам А,В, и Е: «Код 1», «Клиент», «Скидка».

1.3. Создание таблицы оперативного контроля по выполненным заказам

Создайте список «Учет», который должен служить подспорьем при управлении данными о заказах, выполненных в текущем году. Максимально автоматизируйте процесс ввода данных. Эти данные проанализируйте с помощью Мастера сводных таблиц.

Задание 12. Присвоение имени списку

Откройте рабочий лист 3 и присвойте ему имя «Учет».

Задание 13. Создание списка «Учет»

Начните с определения структуры списка и подготовки таблицы к вводу данных. Для этого выполните следующее:

• в ячейку А1 введите заголовок таблицы «Учет выполненных заказов»;

• в ячейки А2 — К2 введите заголовки столбцов, как предложено на рис. 2.: «Месяц», «Дата», «Код товара», «Наименование товара», «Цена», «Количество», «Код клиента», «Клиент», «Сумма», «Скидка», «Получено».

Полученную таблицу лучше расположить на листе альбомно. Для этого в меню «Файл» выберите команду «Параметры страницы» и установите альбомную ориентацию.

Выполните авторазбиение на страницы, для чего в меню «Сервис», выберите команду «Параметры», включите вкладку «Вид», /становите флажок «Авторазбиение на страницы».

	A	B	C	D	E	F	G	H	I	J	K
1	Учет выполнения заказов
2	Месяц	Дата	Код товара	Наименование товара	Цена	Кол-во	Код клиента	Клиент	Сумма	Скидка	Получено

Рис. 2. Заголовки столбцов списка «Учет»

При оперативном учете товар отпускается клиенту и данные об этом записываются в строку таблицы.

Но нет необходимости заполнять все столбцы.

Только столбцы А, В, С, F и G («Месяц», «Дата», «Код товара», «Количество», «Код клиента») заполняются при оформлении каждого заказа, что занимает немного времени.

Задание 14. Назначение цвета столбцам

Чтобы при внесении информации не ошибиться столбцами, окрасьте их в разные цвета.

Присвойте светло-желтый цвет столбцам, в которые вносится информация с клавиатуры по мере отпуска товара. Для этого:

• выделите блоки A3 — СЗ и F3 — G3 (выделяются несмежные столбцы с нажатой клавишей «Ctrl». В столбцы А, В, С, F и G информация вносится с клавиатуры);

• на панели инструментов нажмите на раскрывающийся список кнопки «Цвет фона». Выберите светло-желтый цвет.

Выделите блоки D3 — ЕЗ и НЗ — КЗ и окрасьте в светло-голубой цвет. Информация в колонки D, E, H, I, J и К будет заноситься автоматически.

Задание 15. Назначение формата

Перед вводом даты в столбец В, назначьте формат даты.

Для этого:

• в меню «Формат» выберите команду «Ячейка» и вкладку «Число»;

• в «Числовых форматах» выберите «Дата», в правом окне — тип даты, например, 16.04.98.

Задание 16. Ввод формул в таблицу

Заполнение столбцов таблицы начните с ввода формул.

Столбец D

В столбце D должно быть указано наименование товара. Данные можно вставить автоматически с помощью формул, обращаясь к справочнику товаров.

Введите в ячейку D3 формулу:

= ЕСЛИ ($СЗ="";"";ПРОСМОТР($СЗ;Код;Товар)).

Функция «ЕСЛИ» обеспечивает проверку содержимого ячейки СЗ. Если в ячейке СЗ данные отсутствуют, то ячейка D3 тоже останется незаполненной.

Если ячейка СЗ содержит код товара, произойдет поиск товара по диапазону «Код».

Если формула введена правильно, то в ячейке D3 на экране не будет отображено ничего, а в строке формул вы увидите введенную формулу.

Столбец Е

Столбец Е предназначен для указания цены товара. Формула в ячейке ЕЗ должна иметь следующий вид:

= ЕСЛИ ($СЗ="";"";ПРОСМОТР($СЗ;Код;Цена)).

Данную формулу можно не вводить заново, а скопировать.

Для этого:

• выделите ячейку D3 с формулой;

• выберите команду «Копировать»;

• установите курсор в ячейку ЕЗ;

• в меню «Правка» выберите команду «Специальная вставка / Формулы»;

• нажмите клавишу «ОК»;

• используя клавишу «F2», отредактируйте формулу, заменив слово «Товар» на слово «Цена».

Столбец Н

В ячейку НЗ введите следующую формулу (можно также скопировать формулу из ячейки ЕЗ и затем внести нужные изменения): = ЕСЛИ ($33="";"";ПРОСМОТР($СЗ;Код1;Клиент)).

Столбец J

В ячейку J3 введите формулу:

= ЕСЛИ ($СЗ="";"";ПРОСМОТР($СЗ;Код1;Скидка)).

Столбец I (I — латинская буква И, а не единица).

В ячейку 13 введите формулу «Сумма заказов» (умножается «Цена товара» на «Количество») = F3 х ЕЗ.

Столбец К

В ячейку КЗ введите формулу для подсчета, колонки «Получено» = 13 – –13 х J3.

Задание 17. Форматирование таблицы «Учет выполненных заказов»

Проделайте следующее:

• выделите строку с заголовком таблицы, назначьте шрифт по усмотрению, отцентрируйте;

• выделите строку с заголовками столбцов, отцентрируйте, выберите шрифт пункт 8, жирный;

• установите ширину столбцов;

• выделите ячейки A3 — К29 (размер блока по высоте выбирается по необходимости) и в меню «Правка» выберите команду «Заполнить / Вниз» В результате этого, формулы и цвет фона в выделенном блоке размножатся до 29 строки.

Колонки «Сумма» и «Получено» заполнятся символами «#ЗНАЧ!». Это обозначение ошибочного значения. Ошибочное значение — это результат формулы, которую Excel не может разрешить. В Excel определено семь ошибочных значений. «#ЗНАЧ!» означает, что введена математическая формула, которая ссылается на текстовое значение. В нашем примере мы ссылаемся на ячейку, значение которой пока отсутствует.

Задание 18. Присвоение имен

Выделите по очереди столбцы и введите в поле имени имена «Месяц», «Дата», «Код 2», «Товар 1», «Цена 1», «Количество», «Сумма», «Скидка 1», «Оплата».

Проверьте правильность введенных имен, щелкнув по именам колонок, для редактирования воспользуйтесь меню «Вставка», команда «Имя/ Присвоить».

Задание 19. Заполнение таблицы данными

Итак, таблица готова для ведения оперативного учета.

При отпуске товара достаточно заполнять только те столбцы, которые вы видите заполненными в таблице 3, они окрашены в светло-желтый цвет.

Введите несколько записей и проверьте правильность определения значений.

Заполните вашу таблицу данными из таблицы 3.

В результате вы получите данные, представленные в таблице 4.