Макросы

Создание макросов. Выбрать из меню Вид Команду Макросы/Запись макроса. В открывшемся окне дать имя макросу (без пробелов) и назначить сочетание клавиш на Запуск макроса. Началась запись макроса. Все выполняемые действия будут записаны в этот макрос. По окончании следует остановить запись, для этого выбрать Команду Макросы\Остановить запись. Запустить макрос на выполнение можно: либо с помощью сочетания клавиш, либо с помощью меню Вид Команда Макросы. В открывшемся окне выбрать нужный макрос и команду Выполнить.

Промежуточные итоги.

Промежуточные итоги подводятся по каким-либо общим параметрам, поэтому предварительно необходимо отсортировать таблицу по нужному параметру. Все нужные команды находятся в меню Данные. Сначала надо выбрать Команду Сортировка и отсортировать таблицу по нужному столбцу (по которому потом будем группировать). Затем выбрать Команду Промежуточные итоги. В открывшемся окне выбрать При каждом изменении в – столбец, по которому надо группировать данные, выбрать нужную операцию и на какими данными выполнить.

Сводная таблица.

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

Вызвать команду Сводная через Меню Вставка. Сводную строим на существующем листе, указав одну ячейку.

Поиск решения.

Надстройка MS Excel "Поиск решений" позволяет решать широкий круг задач на оптимизацию. Поиск решений в Excel позволяет в считанные секунды находить оптимальные решения достаточно сложных моделей, кстати не только линейных. Оптимизационные модели широко используются в экономике и технике. Среди них задачи подбора сбалансированного рациона питания, оптимизации ассортимента продукции, транспортная задача и пр., и пр.

Модели всех задач на оптимизацию состоят из следующих элементов:

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

2. Целевая функция - величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели (выражается формулой, в которой отражается зависимость от изменяемых переменных).

3. Ограничения - условия, которым должны удовлетворять переменные.

Способы фильтрации списков

Фильтрация или выборка - очень частая операция во время работы со списками. Суть ее в том, чтобы отобрать из списка все строки (записи), удовлетворяющие определенным условиям. Условий может быть много, они могут быть простыми и сложными, связанными друг с другом или независимыми.

Способ 1. Автофильтр

Отфильтровать список автофильтром - значит скрыть все строки за исключением тех, которых удовлетворяют заданным условиям отбора. Для выполнения такой операции необходимо выделить любую ячейку списка и, выбрать в меню Данные - Фильтр – Автофильтр. В первой строке, содержащей заголовки столбцов, появятся кнопки со стрелками - кнопки автофильтра:

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

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

К сожалению, ни одна версия Excel, кроме Excel 2007 не позволяет выбрать из выпадающего списка более одного варианта.

Мы можем также использовать автофильтр, чтобы найти заданное число (или процент) наибольших или наименьших элементов в списке. Для этого необходимо щелкнуть по кнопке автофильтра в соответствующем поле и выбрать Первые 10. Откроется окно, в котором пользователь может задать количество элементов и выбрать из выпадающих списков их тип - наибольшие или наименьшие:

Пользовательский автофильтр

Мы можем использовать автофильтр для выборки записей по более сложным условиям. Допустим, необходимо выбрать из списка строки, где оклад сотрудника лежит в диапазоне от 1000 до 2000 или меньше/больше определенной величины. Для выполнения такой фильтрации из выпадающего списка поля "Стоимость заказа" необходимо выбрать пункт Условие. Появится окно Пользовательского автофильтра:

При помощи выпадающих списков необходимо задать условие отбора и нажать кнопку ОК - Excel отобразит только те строки, которые удовлетворяют заданным условиям.

Пользовательский автофильтр способен также работать с текстом - для этого в списке условий есть операторы Начинается с..., Заканчивается на..., Содержит..., Не содержит... и т.д.

Расширенный фильтр

В отличие от Автофильтра и Пользовательского автофильтра - Расширенный фильтр практически не имеет ограничений на количество условий, налагаемых на список. Но требует некоторых подготовительных операций.

1. Необходимо предварительно создать таблицу критериев (условий). Эта таблица должна состоять из двух строк: заголовка и самого условия, поэтому минимум может быть 2 ячейки: заголовок и условие.

Критерии бывают двух типов.

• Критерии сравнения – это набор условий для поиска, используемый для извлечения данных. Критерий сравнения может быть последовательностью символов (константой) или выражением (например, Цена > 700).

Порядок создания таблицы критериев сравнения:

• В любую свободную область (лучше всего вставить над списком несколько пустых строк) нужно скопировать из "шапки" списка заголовки столбцов, по которым будут вводиться условия.

• Затем, ниже скопированных заголовков в пустые ячейки вводятся условия для фильтрации. Причем, условия, введенные в ячейки одной строки Excel будет связывать логическим "И", а в ячейки разных строк - логическим "ИЛИ".

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

Excel отберет всех мужчин с окладом 1000 и более, неженатых и женщин не старше 1 января 1950 года рождения, незамужних, с детьми.

• Критерии вычисления – это критерии, которые являются результатом вычисления формулы. Формула должна ссылаться хотя бы на один столбец списка.

Таблица критериев вычисления отличаются от таблицы критериев сравнения тем: что заголовки этой таблицы не должны совпадать ни с одним заголовком исходной таблицы, т.е. может отражать суть поиска.

выше среднего

=F7>СРЗНАЧ($F$7:$F$21)

Например, диапазон критериев =F7>СРЗНАЧ($F$7:$F$21) выводит на экран строки, имеющие в столбце F значения большие, чем среднее значение величин в ячейках F7:F21. Формула должна возвращать логическое значение ЛОЖЬ или ИСТИНА. При фильтрации будут доступные только те строки, значения которых будут придавать формуле значения ИСТИНА.

2 . Для того, чтобы отфильтровать данные по критериям из нашей таблицы, выделите любую ячейку исходного списка сотрудников и выберите в меню Данные - Фильтр - Расширенный фильтр. Откроется окно Расширенного фильтра, в которое необходимо ввести адрес исходного диапазона, адрес диапазона условий и указать место, куда поместить результаты фильтрации:

Флажок Только уникальные записи служит для отбора неповторяющихся данных

Значение переключателей и полей окна Расширенный фильтр следующие:

фильтровать список на месте – переключатель, скрывающий строки, которые не удовлетворяют указанному критерию;

скопировать результат в другое место – копирует отфильтрованные данные на другой рабочий лист или на другое место на этом же рабочем листе;

Исходный диапазон – поле, определяющее диапазон, который содержит список, подлежащий фильтрации;

Диапазон условий – поле, определяющее диапазон ячеек на рабочем листе, который содержит необходимые условия;

Поместить результат в диапазон – поле, определяющее диапазон ячеек, в который копируются строки, удовлетворяющие определенным условиям; это поле активно только в том случае, если выбран переключатель скопировать результат в другое место;

Только уникальные записи – переключатель, который выводит только строки, удовлетворяющие критерию и не содержащие неповторяющихся элементов. Если диапазон критериев не определен, то в этом случае все строки списка, содержащие дубликаты, будут скрыты.

Лабораторная работа №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

РАБОТА СО СПРАВОЧНИКАМИ. ИСПОЛЬЗОВАНИЕ ФУНКЦИИ ВПР

Функции для работы со ссылками и массивами

ВПР (искомое_значение; таблица; номер_столбца; интервальный_просмотр)- ищет значение в крайнем левом столбце и возвращает значение ячейки, находящейся в указанном столбце той же строки. (просматривает сверху вниз левый столбец таблицы пока не встретит искомое_значение, а затем просматривает строку до столбца с номером номер_столбца)

искомое_значение- значение, которое должно быть найдено в первом столбце массива (значение, ссылка или строка текста)

таблица- таблица с текстом, числами или логическими значениями, в котором производится поиск данных; может быть ссылкой или имя диапазона.

номер_столбца- номер столбца в таблице, из которого нужно вернуть значение. Первый столбец значений имеет номер 1.

интервальный_просмотр- логическое значение, определяющее, точно (ЛОЖЬ) или приближенно (ИСТИНА или отсутствие значения) должен производиться поиск в первом отсортированном столбце (отсортированном по возрастанию)

Функций МS Ехсеl - ВПР, облегчает работу по заполнению таблиц при необходимости использования справочных таблиц. Примером может служить заполнение накладной, в которой при вводе кода товара должно автоматически появляться наименование товара, цена товара, фирма производитель и т.д.

На первом шаге, на отдельном листе создайте справочную таблицу и присвойте ей имя «Справочник», например: __________________________________

Код товара	Название товара	Цена
0001	Товар1	$200,00
0002	Товар2	$250,00
0101	Товар3	$280,00
1002	Товар3	$300,00

Табл. 3. Справочник товаров

Предположим, на другом листе требуется вводить данные о проданных товарах. Создайте таблицу с заголовками столбцов: Код товара, Название товара, Цена, Кол-во, Сумма:________

Код товара	Название товара	Цена	Кол-во	Сумма
0101	=ВПР(А2;справочник;2;0)	=ВПР(А2;справочник;3 ;0)

Табл. 4. Фрагмент накладной на продажу товаров

Введите код товара 0001. В первую строку табл. 4 в столбце «Название товара» введите формулу =ВПР(А2;справочник;2;0).

Функция ВПР имеет следующие аргументы:

Искомое_значение: в этом окошке указывается значение (код товара), определяющее остальные параметры (наименование товара, его цену). Это значение будет искаться в первом столбце таблицы-справочника.

Таблица: в этом окошке указывается адрес или имя справочной таблицы (в нашем примере - это имя «справочник»).

Номер_столбца: в этом окошке указывается номер столбца, содержащий соответствующую характеристику значения, введенного в первом окошке (в нашем случае номер 2 определяет столбец «Наименование СРШ)

Интервальный_просмотр: имеет два значения «0», если таблица-справочник не отсортирована по первому столбцу, и «1», если она отсортирована по первому столбцу.

Для определения цены товара, код которого будет введен в ячейке А2, следует ввести формулу: ВПР(А2;справочник;3;0).

При копировании функции ВПР вниз при отсутствии данных в столбце А в ячейках появляется сообщение об ошибке #Н/Д. Чтобы избежать его, следует предусмотреть ввод "пробела" в случае, если код соответствующего СР11 еще не выбран. Реализовать это можно с помощью функции ЕСЛИ. В рассматриваемой ситуации функция будет иметь вид: =ЕСЛИ(А2=0;""; ВПР(А2;справочник;2;0)).При вводе кода товара в столбцах «Наименование товара и «Цена» появятся соответствующие коду значения. Для ввода кодов товаров мы рекомендуем использовать возможность МS Ехсеl по вводу значений из фиксированного списка с помощью инструмента Данные\Проверка.

Использование функций в Ехсеl

Задание 1. Расчет значения премии сотрудникам с использованием функции ЕСЛИ

А	В	С	D	Е
ФИО	Тарифная ставка(руб.)	Отработано часов	Начислено	Премия
Сидоров В.И.	100	25
Андреева И.Т.	150	30
Ковалева О.А.	ПО	15
Лобанов А.О.	150	20

Если сотрудник отработал больше 20 часов, то назначим премиальный коэффициент равный 20%, в противном случае -10%.

Для расчета Начислено первому сотруднику щелкните в ячейке В4, введите формулу В2*С2 Для расчета премии первому сотруднику щелкните в ячейке Е4. Вызовите Мастер функций.

В категории Логические выберите функцию ЕСЛИ. В соответствующем диалоговом окне следует указать:

• в окошке Лог_выражение: С4>20;

• в окошке Значение_если_истина: Б4*$Е$2;

• в окошке Значение_если_ложь: 04*$Е$1. Нажмите кнопку ОК.

Задание 2. Начисление «сложной» премии.

Рассмотрим пример начисления премии по следующему алгоритму: Если отработано меньше или 10 часов, то премия не назначается.

Если отработано больше или 20 часов, то назначается премия в размере 20% от Начислено. В противном случае (отработано больше 10 часов и меньше 20 часов) назначается премия в размере 10% от Начислено.

Щелкните в ячейке Е4. Вызовите функцию ЕСЛИ. Введите:

• в окошке Лог_выражение: С4>=20;

• в окошке Значение_если_истина: Э4*$Е$2;

Щелкните в окошке Значение_если_ложь и вызовите вторую функцию ЕСЛИ. Появится чистое диалоговое окно вложенной функции ЕСЛИ, в котором нужно ввести:

• в окошке Лог_выражение: С4<=10;