Решение задачи средствами MSExcel

1. Вызовите Excel:

· нажмите кнопку «Пуск»;

· выберите в главном меню команду «Программы»;

· вменю Microsoft Office выберитеMS Excel.

2. Переименуйте «Лист 1» в «Справочник поставщика»:

· установите курсор мыши на ярлык «Лист 1» (нижняя часть экрана) и нажмите правую кнопку мыши;

· выберите в контекстном меню команду «Переименовать» и нажмите левую кнопку мыши;

· наберите на клавиатуре «Справочник поставщика»;

· нажмите клавишу «Enter».

3. Введите заголовок таблицы «Справочник поставщика»:

· сделайте ячейку A1 активной (установите курсор мыши на пересечение столбца A и строки 1 и нажмите левую кнопку мыши);

· наберите на клавиатуре «Справочник поставщика»;

· нажмите кнопку в строке формул (установите курсор мыши на эту копку и нажмите левую кнопку мыши).

4. Отформатируйте заголовок:

· выделите ячейки A1÷D1 (сделайте активной ячейку A1, затем нажмите левую кнопку мыши и, не отпуская ее, переместите курсор на ячейку D1) (рис. 2 – здесь и далее в описании инструкции использована собственная нумерация рисунков);

Рис. 2 Пример выделения группы ячеек

· на панели инструментов в закладке «Главная» выберите раздел «Выравнивание» и нажмите кнопку .

5. Отформатируйте ячейки A2÷C2 под ввод длинных заголовков:

· выделите ячейки A2÷C2;

· выполните команду «Выравнивание» в разделе «Формат ячеек» меню «Главная» на панели инструментов;

· выберите закладку «Выравнивание»;

· в группе опций «Отображение» установите флажок опции «переносить по словам» (рис. 3);

Рис. 3 Задание переноса слов при вводе в ячейку

длинных предложений

· нажмите кнопку «OK».

6. Введите в ячейки A2÷D2 информацию, представленную на рисунке 4 (см. рис. 3).

Рис. 4 Имена полей таблицы «Справочник поставщика»

7. Организуйте контроль вводимых данных в колонку «Код поставщика»:

· выделите ячейки B3÷B7;

· выполните команду «Проверка данных» в меню «Данные»;

· в поле «Тип данных» нажмите кнопку (рис. 5).

Рис. 5 Выбор типа данных

Замечание. Выбор типа данных вводимых значений в списке «Тип данных» позволяет определить, какие условия можно установить для значений ячеек. Если для определения допустимых значений требуется ввести формулу, выражение или ссылку на вычисления в другой ячейке, выберите в списке строку «Другой».

· выберите «Целое число»;

· задайте в поле «Минимум»: 100;

· задайте в поле «Максимум»: 105 (рис. 6);

Рис. 6 Задание интервала допустимых значений целых чисел

Замечание. Если есть необходимость видеть постоянно на экране подсказку об ограничениях ввода в выбранную ячейку (рис. 7), то выбирайте закладку «Сообщение для ввода». Если нужно, чтобы сообщение появлялось только после ошибки, выбирайте закладку «Сообщение об ошибке».

· выберите закладку «Сообщение для ввода»;

· введите в поля «Заголовок» и «Сообщение» информацию, приведенную на рисунке 6;

· нажмите кнопку «ОК»;

Информация, введенная в данное поле (до 33 знаков), отображается полужирным шрифтом при выборе пользователем ячейки, имеющей ограничения на вводимые значения

	Поле для ввода текста сообщения (до 255 знаков). Для перевода строки в окне сообщение следует нажать «Enter»

Рис. 7 Задание сообщения для ввода

Рис.8 Вид экрана с сообщением для ввода

· выберите закладку «Сообщение об ошибке». Если установлен флажок «Выводить сообщение об ошибке», то при попытке ввода в ячейку недопустимых значений выдается сообщение об ошибке или запрещается ввод неверных данных. Тип предупреждения, задаваемый в поле «Вид», определяет действия пользователя в ответ на сообщение о вводе неверных данных в ячейку, для которой заданы ограничения на водимые значения. Назначение полей «Заголовок» и «Сообщение» было описано выше;

· в поле «Вид» выберите «Останов»;

· поставьте курсор в поле «Заголовок» и наберите на клавиатуре «Код поставщика ограничен»;

· поставьте курсор в поле «Сообщение» и наберите на клавиатуре «Код поставщика может принимать значения 100-105» (рис. 9)

· нажмите кнопку «OK»

Рис. 9 Сообщение об ошибке вида «Останов»

В случае ввода ошибочных данных на экран монитора выводится сообщение (рис. 10):

Рис. 10 Вид сообщения «Останов»

При выборе в поле «Вид» типа «Предупреждение» (рис. 11), в случае ошибки на экран выводится сообщение (рис. 12):

Рис. 11 Сообщение об ошибке «Предупреждение»

Рис. 12 Вид сообщения «Предупреждение»

Аналогичные сообщения выводятся на экран при выборе в поле «Вид» типа «Сообщение» (рис. 13, 14).

Рис. 13 Сообщение об ошибке «Сообщение»

Рис. 14 Вид сообщения «Сообщение»

8. Отформатируйте ячейки D3÷D7 для ввода текстовых символов:

· выделите ячейки D3÷D7;

· на панели инструментов в меню «Главная» выберите «Ячейки», где в пункте «Формат» выполните команду «Формат ячеек»;

· выберите закладку «Число»;

· выберите формат «Текстовый» (рис.15);

Рис. 15 Выбор формата ячеек

· нажмите кнопку «OK».

9. Введите информацию, приведенную в таблице 1. Обязательным условием ввода данных в колонку «Наименование поставщика» является их упорядочение по алфавиту (названию).

Таблица 1

Справочник поставщиков

Наименование поставщика	Код поставщика	Адрес поставщика	Расчетный счет
Аврора		Казань
Азов		Тула
Восход		Пермь
Заря		Москва
Космос		Тверь

10. Организуйте проверку ввода данных в графу «Код поставщика» с выдачей сообщения об ошибке (аналогично действиям пункта 7).

11. Присвойте имя группе ячеек:

· выделите ячейки A3÷D7;

· выберите команду «Присвоить имя» в разделе «Определенные имена» меню «Формулы (рис. 16);

Рис. 16 Вид окна «Создание имени»

· нажмите кнопку «OK».

12. Переименуйте «Лист 2» в «Ведомость учета поставки материалов» (аналогично действиям пункта 2).

13.Создайте таблицу «Ведомость учета поставки материалов» (аналогично действиям пунктов 3 – 5), рис.17.

14. Введите исходные данные (см. рис. 17).

Рис. 17 Вид таблицы «Ведомость учета поставки материалов»

15. Заполните графы «Наименование поставщика» и «Код поставщика»:

· сделайте ячейку B3 активной;

· в меню «Данные» выберите команду «Проверка данных», в поле «Тип данных» которой выберите «Список»;

· введите значение в поле «Источник», выделив диапазон A3÷A7 в «Справочнике поставщика» (рис. 18);

Рис. 18 Настройка списка поставщиков

· нажмите кнопку «OK»;

· для того чтобы ввод наименования поставщика из списка осуществлялся в каждой ячейке столбца B(«Наименование поставщика») сделайте ячейку B3 активной и, установив курсор на маркер в правом нижнем углу, щелкните левой клавишей мыши и протяните его до ячейки B13 (рис. 19);

Рис. 19 Вид листа «Ведомость учета поставки материалов»

при настройке списка

· воспользуйтесь командой «Вставить функцию» меню «Формулы»;

· в поле «Категория:» выберите «Ссылки и массивы»;

· в поле «Выберите функцию» нажмите «ВПР» (рис. 20);

Рис. 20 Вид первого окна мастера функций

· нажмите кнопку «OK».

· введите в поле «Искомое_значение», щелкнув по ячейке B3;

· нажмите «Enter»;

· введите информацию в поле «Таблица»;

· воспользуйтесь командой «Использовать в формуле» меню «Формулы», выбрав «Вставить имена»;

· выделите «Имя:» «Наименование_поставщика» (рис. 21)

Рис.21 Ввод имени массива в качестве аргумента формулы

· нажмите кнопку «OK»;

· нажмите «Enter»;

· введите информацию – цифру 2 в поле «Номер_столбца»;

· введите информацию – цифру 0 в поле «Интервальный_ просмотр» (рис. 22)

Рис.22 Вид второго окна мастера функций

· нажмите кнопку «OK».

· установите курсор на маркер в правом нижнем углу ячейки A3, щелкните левой клавишей мыши и протяните его до ячейки А13.

Функция ВПР работает только после ввода наименования поставщика в соответствующую ячейку столбца B.

16. Введите наименования поставщиков в ячейки В3÷B13:

· сделайте ячейку B3 активной;

· щелкните на кнопку рядом с ячейкой B3 и из предложенного списка выберите наименование поставщика – Заря. Ячейка A3 – «Код поставщика» будет заполнена автоматически (рис.23)

Рис. 23 Автоматическое заполнение кода поставщика

по его наименованию

· аналогично заполните ячейки В4÷B13, ячейки А4÷А13 будут также заполнены автоматически.

Заполненная таблица выглядит следующим образом (рис. 24).

Рис. 24 Результат заполнения таблицы «Ведомость учета поставки материалов»

17. Создайте сводную таблицу «Фактическое выполнение поставок»:

· установите курсор в поле таблицы «Ведомость учета поставки материалов»;

· воспользуйтесь командой «Сводная таблица» из меню «Вставка»;

· в окне «Создание сводной таблицы» (MSOffice 2010) нажмите кнопку «OK» (рис 25).

Рис. 25 Создание сводной таблицы

Чтобы вставить поле в сводную таблицу, его необходимо перетащить в одну из четырех областей: «Фильтр отчета», «Названия столбцов», «Названия строк» и «Σ Значения»:

· перенесите в поле «Фильтр отчета» надпись «Код материала» (поставьте курсор на поле «Код материала», нажмите левую клавишу мыши и, не отпуская, перенесите в поле «Фильтр отчета»);

· перенесите в поле «Названия строк» надпись «Наименование поставщика»;

· перенесите в поле «Σ Значения» надпись «Сумма поставки фактическая»;

· в результате выполнения перечисленных действий получится сводная таблица (рис. 26);

Рис. 26 Фрагмент листа «Фактическое выполнение поставок»

· переименуйте лист со сводной таблицей в «Фактическое выполнение поставок».

18. Создайте ведомость «Фактическое выполнение поставок по поставщикам и датам»

· установите курсор в поле таблицы «Ведомость учета поставки материалов»;

· воспользуйтесь командой «Сводная таблица» из меню «Вставка»;

· перенесите в поле «Названия строк» надпись «Наименование поставщика»;

· перенесите в поле «Названия строк» надпись «Дата поставки»;

· перенесите в поле «Фильтр отчета» надпись «Код поставщика»;

· перенесите в поле «Σ Значения» надпись «Сумма поставки фактическая»;

· переименуйте лист со сводной таблицей в «Фактическое выполнение поставок по поставщикам и датам» (рис. 27).

Рис. 27Фактическое выполнение поставок по поставщикам и датам (сводная таблица)