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» в «Справочник материалов»
Справочник материалов | ||
Наименование материала | Код материала | Единица измерения |
Кирпич | шт | |
Краска | кг | |
Лак | кг | |
Стекло | м2 | |
Цемент | т |
10. Переименуйте «Лист 3» в «Ведомость учета поставки материалов» (аналогично действиям пункта 2).
11.Создайте таблицу «Ведомость учета поставки материалов» (аналогично действиям пунктов 3 – 5), рис.6.
12. Введите исходные данные (см. рис. 6).
Ведомость учета поставки материалов | |||||
Код поставщика | Наименование поставщика | Код материала | Вид материала | Дата поставки | Сумма поставки фактическая |
05.09.2010 | 5,00 | ||||
05.09.2010 | 7,00 | ||||
06.09.2010 | 3,00 | ||||
07.09.2010 | 4,00 | ||||
07.09.2010 | 2,00 | ||||
07.09.2010 | 3,00 | ||||
07.09.2010 | 5,00 | ||||
08.09.2010 | 1,00 | ||||
08.09.2010 | 2,00 | ||||
09.09.2010 | 5,00 | ||||
09.09.2010 | 5,00 |
Рис. 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 Фактическое выполнение поставок по поставщикам и датам (сводная таблица)