РАБОТА СО СПРАВОЧНИКАМИ. ИСПОЛЬЗОВАНИЕ ФУНКЦИИ ВПР
Макросы
Создание макросов. Выбрать из меню Вид Команду Макросы/Запись макроса. В открывшемся окне дать имя макросу (без пробелов) и назначить сочетание клавиш на Запуск макроса. Началась запись макроса. Все выполняемые действия будут записаны в этот макрос. По окончании следует остановить запись, для этого выбрать Команду Макросы\Остановить запись. Запустить макрос на выполнение можно: либо с помощью сочетания клавиш, либо с помощью меню Вид Команда Макросы. В открывшемся окне выбрать нужный макрос и команду Выполнить.
Промежуточные итоги.
Промежуточные итоги подводятся по каким-либо общим параметрам, поэтому предварительно необходимо отсортировать таблицу по нужному параметру. Все нужные команды находятся в меню Данные. Сначала надо выбрать Команду Сортировка и отсортировать таблицу по нужному столбцу (по которому потом будем группировать). Затем выбрать Команду Промежуточные итоги. В открывшемся окне выбрать При каждом изменении в – столбец, по которому надо группировать данные, выбрать нужную операцию и на какими данными выполнить.
Сводная таблица.
Вызвать команду Сводная через Меню Вставка.Сводную строим на существующем листе, указав одну ячейку.
Поиск решения.
Надстройка MS Excel "Поиск решений" позволяет решать широкий круг задач на оптимизацию. Поиск решений в Excel позволяет в считанные секунды находить оптимальные решения достаточно сложных моделей, кстати не только линейных. Оптимизационные модели широко используются в экономике и технике. Среди них задачи подбора сбалансированного рациона питания, оптимизации ассортимента продукции, транспортная задача и пр., и пр.
|
|
|
Модели всех задач на оптимизацию состоят из следующих элементов:
1. Переменные - неизвестные величины, которые нужно найти при решении задачи.
2. Целевая функция - величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели (выражается формулой, в которой отражается зависимость от изменяемых переменных).
3. Ограничения - условия, которым должны удовлетворять переменные.
Лабораторная работа №4 Сортировка таблиц, использование промежуточных итогов и расширенного фильтра.
Задание1. Сортировка таблицы.
| Месяц | Продукция | Продавец | Район | Объем (шт.) | Выручка |
| янв | Напитки | Марченко | Северный | 11111 | 2577752 |
| янв | Напитки | Марченко | Восточный | 3214 | 745648 |
| янв | Напитки | Марченко | Южный | 3200 | 742400 |
| фев | Напитки | Марченко | Северный | 567 | 131544 |
| март | Напитки | Марченко | Северный | 567 | 131544 |
| фев | Напитки | Николаев | Западный | 45677 | 10597064 |
| янв | Напитки | Николаев | Западный | 45670 | 10595440 |
| янв | Мясо | Ивин | Южный | 543 | 125976 |
| янв | Мясо | Ивин | Восточный | 5678 | 1317296 |
| фев | Мясо | Ивин | Южный | 4444 | 1031008 |
Приведенная таблица может быть отсортирована сначала по виду Продукции, а затем по фамилии Продавца.
|
|
|
Такая сортировка означает, что каждая группа записей с одинаковым видом Продукции будет отсортирована, в свою очередь, по фамилии Продавца.
Для сортировки таблицы нужно
1. выделить таблицу, включая заголовки ее столбцов, и выполнить команду Данные\ Сортировка;
2. в появившемся диалоговом окне в поле Сортировать по следует указать первое поле сортировки - «Продукция»;
в поле Затем понужно указать «Продавец». Нажмите кнопку ОК.
Задание 2.
Одним из простых инструментов анализа данных является инструмент Промежуточные итоги.Если требуется определить итоговую выручку, полученную каждым продавцом, то следует: 1. Отсортировать таблицу по полю «Продавец».
Выполнить команду Данные\Промежуточные итоги. Впоявившемся диалоговом окне нужно указать:
• При каждом изменении в- «Продавец»;
• Операция- Сумма;
• Добавить итоги по«Выручка в руб.».
Поскольку после предварительной сортировки таблицы, записи с одинаковыми фамилиями продавцов стоят рядом, результат суммирования выручки для каждого продавца появится перед группой записей с фамилией другого продавца. В качестве функций анализа в Промежуточных итогахмогут быть использованы функции Среднее, Максимум, Минимум, Количество и др.
|
|
|
Задание 3.Фильтрация по простым критериям реализуется с помощью автофильтра.
После выделения всех данных таблицы, включая заголовки столбцов, следует выполнить команду Данные\Фильтр\Автофильтр. Около заголовка каждого из столбцов появится стрелка.
Если щелкнуть на стрелке, например, в столбце «Продавец» , то в появившемся окне можно выбрать любую из фамилий, например, фамилию Ивин.
В результате этих действий в таблице останутся только строки, содержащие фамилию продавца «Ивин».
Если затем щелкнуть на стрелке «Район» и выбрать район «Южный», то количество записей уменьшится, т.к. теперь останутся только записи, удовлетворяющие обоим условиям: Продавец = Ивин Район = Южный
Если щелкнуть на стрелке в столбце «Выручка» и выбрать команду «Условие», то появится диалоговое Пользовательский автофильтр.
Вэтом окне, можно задать условия на значения столбца «Выручка». Укажите Выручка вруб. больше 1000000. Нажмите ОК.
|
|
|
Номера отфильтрованных строк приобретают голубой цвет. Строки, не удовлетворяющие критериям фильтрации, не удаляются, а временно скрываются. Чтобы вернуть на экран все строки таблицы, нужно выполнить команду Данные\Фильтр\Отобразить все в списках условий около каждого заголовка.
Задание 3. Пример формирования условий для применения расширенного фильтра Рассмотрим следующую задачу:_____________________________________
| Месяц | Продукция | Продавец | Район | Объем (шт.) | Выручка |
| янв | Напитки | Марченко | Северный | 11111 | 2577752 |
| янв | Напитки | Марченко | Восточный | 3214 | 745648 |
| янв | Напитки | Марченко | Южный | 3200 | 742400 |
| фев | Напитки | Марченко | Северный | 567 | 131544 |
| март | Напитки | Марченко | Северный | 567 | 131544 |
| фев | Напитки | Николаев | Западный | 45677 | 10597064 |
| янв | Напитки | Николаев | Западный | 45670 | 10595440 |
| янв | Мясо | Ивин | Южный | 543 | 125976 |
| янв | Мясо | Ивин | Восточный | 5678 | 1317296 |
| фев | Мясо | Ивин | Южный | 4444 | 1031008 |
В табл. найти записи, соответствующие январю месяцу, имеющие данные по Выручке, большие 1000000 и записи, соответствующие февралю месяцу, имеющие данные по Выручке, большие 150000.
Создадим Диапазонусловий. Он будет иметь следующий вид:
| Месяц | Выручка |
| янв | >1000000 |
| фев | >150000 |
Далее следует выполнить команду Данные\Фильтр\Расширенный фильтр. В появившемся диалоговом окне нужно указать подготовленный диапазон условий, в качестве диапазона, в которыйбудет помещен результат,можно указать любую свободную ячейку вне таблицы, и нажать кнопку ОК.
Лабораторная работа №8
РАБОТА СО СПРАВОЧНИКАМИ. ИСПОЛЬЗОВАНИЕ ФУНКЦИИ ВПР
Дата добавления: 2018-04-05; просмотров: 247; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!