Технология решения задачи

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 2 Задание переноса слов при вводе в ячейку длинных предложений

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

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

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

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

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

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

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

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

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

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

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

Таблица 1

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

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

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

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

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

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

Примечание. Выполните п.п. 2-9 для таблицы, переименовав «лист 2» в «Справочник материалов»

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ВПР ищет значение, заданное в первом аргументе, в крайнем левом столбце диапазона, указанного во втором аргументе. Возвращает она значение из строки, содержащей найденное в первом аргументе значение, но находящегося в столбце, который задан в третьем аргументе функции. Четвертый аргумент - логическое выражение ЛОЖЬ, задается для поиска точно соответствующей информации, введенной в первом аргументе.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание. Аналогичные операции выполните для столбцов C и D, начиная с п. 13.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Не нашли, что искали? Воспользуйтесь поиском по сайту: