Задание 1: Связывание листов рабочей книги с использованием ссылок в формулах и функций из категории ССЫЛКИ и МАССИВЫ.
1. Загрузить EXCEL. Нажать кнопку OFFIСE 
, и выбрать пункт ПАРАМЕТРЫEXCEL.
2. Установить параметры отображения информации: в меню ПАРАМЕТРЫEXCEL, на вкладке ДОПОЛНИТЕЛЬНО включить флаги: ОТОБРАЖАТЬ СЕТКУ, ЗАГОЛОВКИ СТРОК И СТОЛБЦОВ, ГОРИЗОНТАЛЬНАЯ/ВЕРТИКАЛЬНАЯ ПОЛОСА ПРОКРУТКИ, ЯРЛЫЧКИ ЛИСТОВ, ПО УМОЛЧАНИЮ: эти флаги должны быть уже включены. Выключить их. Закрыть окно настройки параметров, нажав ОК, и убедиться в том, что данные настройки применены. В некоторых случаях работа в таком режиме может быть удобна, но для выполнения данного задания удобнее включить все ранее снятые значки. Выполнить самостоятельно.
Рис. 1
3. Переименовать листы рабочей книги, назвав их соответственно Прайс, Затраты, Предложение. В контекстном меню листа выбрать пункт ПЕРЕИМЕНОВАТЬ и задать новое имя листа, например ПРАЙС. Аналогично переименовать два других листа рабочей книги. Контекстное меню позволяет проводить и другие операции с листами рабочей книги: УДАЛИТЬ, ПЕРЕИМЕНОВАТЬ, ПЕРЕМЕСТИТЬ, КОПИРОВАТЬ, ДОБАВИТЬ. Отработать эти операции самостоятельно.
4. Набрать на листе ПРАЙС информацию, представленную на рис. 2. Стандартная ширина колонок в таблице EXCEL равна 8 символам. Для её изменения необходимо выполнить следующие действия: установить курсор мыши на разделительную линию в заголовке столбца и убедиться, что он принял вид двунаправленной стрелки; нажать левую кнопку мыши и, удерживая её, переместить мышь влево/вправо, отпустить кнопку мыши. Также для изменения ширины столбца можно установить курсор мыши на разделительную линию в заголовке столбца и сделать двойной щелчок левой кнопкой мыши. То же можно выполнить на вкладке ГЛАВНАЯ/ФОРМАТ и выбрать АВТОПОДБОР ШИРИНЫСТОЛБЦА. Самостоятельно отработать изменение ширины/высоты столбца/строки рабочей книги. На рис. 3 для того, чтобы текст в ячейках A3:D3 переносился по словам и был расположен в центре ячейки, выполнены установки в окне ФОРМАТ ЯЧЕЕК: в контекстном меню выделенного диапазона ячеек выбрать окно ФОРМАТ ЯЧЕЕК/ВЫРАВНИВАНИЕ и выполнить установки, представленные на рис. 3.
Рис. 2
Рис. 3
На рис. 4 представлена процедура установки рамки таблицы. Для этого выделить всю таблицу. Затем на панели ГЛАВНАЯ на значке ГРАНИЦЫ выбрать ВСЕ ГРАНИЦЫ. Другой способ задания рамки таблицы: в контекстном меню ФОРМАТ ЯЧЕЕК, вкладка ГРАНИЦЫ. Наименование таблицы ПРАЙС-ЛИСТ ввести в ячейку А1, затем выделить блок ячеек А1:D2, и нажать кнопку ОБЪЕДИНИТЬ И ПОМЕСТИТЬ В ЦЕНТРЕ 
на панели ГЛАВНАЯ, как показано на рис. 5. Дальнейшее выравнивание выполняется на панели ГЛАВНАЯ, вкладка ВЫРАВНИВАНИЕ 
.
Рис. 4
Рис. 5
5. Выполнить форматирование чисел в диапазоне С4:С12. Для этого нужно: выделить этот блок ячеек и в контекстном меню выбрать пункт ФОРМАТ ЯЧЕЕК (рис. 6). На вкладке ЧИСЛО выбрать пункт ВСЕ ФОРМАТЫ. Из списка предлагаемых форматов выбрать шаблон, представленный на рис. 6 стрелкой. В поле ТИП изменить стандартный шаблон и нажать ОК. Результат показан на рис. 7.
Рис. 6
6. В ячейки D4:D12 ввести формулы для вычисления цены товара в рублях. Для этого ввести формулу =C4*$G$3 в ячейку D4 (знак $ набирать на клавиатуре или в момент нахождения курсора ввода на фрагменте этой формулы G3 нажать на клавиатуре F4). Затем формулу следует копировать из ячейки D4 на диапазон ячеек D5:D12. Копирование можно выполнять автозаполнением. Для этого нужно: выделить ячейку D4, установить курсор на маркер автозаполнения – прямоугольник в левом нижнем углу ячейки (при нахождении на нём маркер имеет вид креста), нажать левую кнопку мыши и, удерживая её, протянуть указатель мыши до ячейки D12 (рис. 7), отпустить кнопку мыши. Результат представлен на рис. 8.
Рис. 7
7. В ячейку G5 ввести формулу =ТДАТА() для вычисления текущей даты. Формулу можно вводить непосредственным набором или с использованием панели ФОРМУЛЫ/ВСТАВИТЬ ФУНКЦИЮ или про помощи кнопки fx в строке ввода формул. Её следует выбирать из категории ДАТА/ВРЕМЯ.
8. На листе ЗАТРАТЫ набрать таблицу, представленную на рис. 9. В ячейках столбца С - формулы, содержащие ссылку на ячейки листа ПРАЙС. В ячейке С8 формула =СУММ(С3:С7). Она может быть непосредственно набрана в эту ячейку или введена автосуммированием: двойной щелчок левой кнопкой мыши на кнопке Автосумма (∑) на панели ГЛАВНАЯ.
Рис. 8
Рис. 9
9. Для автоматического поиска информации на листе ПРАЙС можно использовать функции из категории ССЫЛКИ И МАССИВЫ. Пример такой функции на рис. 10. Образец для поиска функция ПРОСМОТР берёт из ячейки А5. Этот образец она ищет на диапазоне ячеек В4:В12 листа ПРАЙС. Результат поиска – значение цены для товара заданного артикула функция ищет в диапазоне D4:D12 листа ПРАЙС. Формулу можно вводить непосредственным набором или с использованием панели ФОРМУЛЫ/ВСТАВИТЬ ФУНКЦИЮ или при помощи кнопки fх в строке ввода формул. Необходимое условие применения функции ПРОСМОТР состоит в том, что в диапазоне поиска все записи должны быть отсортированы по возрастанию значений поля АРТИКУЛ.
Рис. 10
10. На листе ПРЕДЛОЖЕНИ Е ввести данные, представленные на рис. 11
Рис. 11
Задание 2: Формула массива
Вычисление обратной матрицы с использованием возможностей MS EXCEL.
1. Вычисление обратной матрицы. В ячейки B16:D18 вводим исходную матрицу. Выделить диапазон ячеек G16:I18. Нажать на кнопку fx 
в строке ввода и выбрать категорию МАТЕМАТИЧЕСКИЕ, функция МОБР (рис. 12)
Рис. 12
В поле МАССИВ вводим адреса ячеек исходной матрицы (рис. 13) и нажимаем сочетание клавиш CTRL+SHIFT+ENTER. Результат представлен на рис. 14
Рис. 13
Рис. 14
2. Умножение матрицы А на матрицу В. В диапазоне ячеек А7:В8 вводим матрицу А. В диапазоне ячеек D7:E8 вводим матрицу В. Выделить ячейки в диапазоне G7:H8. Нажать на кнопку fx в строке ввода и выбрать категорию МАТЕМАТИЧЕСКИЕ, функция МУМНОЖ (рис. 15)
Рис. 15
Для ввода формулы нажимаем сочетание клавиш CTRL+SHIFT+ENTER. Результат вычисления представлен на рис. 18.
Рис. 17
Рис. 18
Самостоятельно вычислить определитель матрицы (рис. 18)
Задание 3: Логические функции
1. Создать таблицу следующего вида (рис. 19).
Рис. 19
2. Рассчитать цену товара после уценки
Рекомендации по выполнению задания: Ввести в ячейку Е7:Е8 проценты уценки товаров: соответственно для октябрьской и более ранней даты договора – 15 %; для декабрьской и ноябрьской даты договора – 0%. В ячейки F7:F8 ввести соответствующие формулы. Например, F7: =D7-D7*E7. На экране должно появиться следующее (рис. 20).
Рис. 20
3. Рассчитать цену товара после уценки в зависимости от даты составления АКТА УЦЕНКИ, причём если между датой уценки и датой договора меньше 62 дней, то процент уценки будет 0%, в остальных случаях – 15%.
Рекомендации по выполнению задания: Ввести дату уценки 29.12.2008 года. Далее удалить проценты уценки из таблицы и вставить соответствующие формулы для процента уценки в зависимости от даты договора. Для выполнения задания необходимо использовать логическую функцию ЕСЛИ. Формат этой функции можно посмотреть с помощью МАСТЕРА ФУНКЦИЙ. Нажать на кнопку fx в строке ввода и выбрать категорию ЛОГИЧЕСКИЕ, функция ЕСЛИ (рис. 21). Записать в ячейку Е5 столбца ПРОЦЕНТ УЦЕНКИ формулу =ЕСЛИ($E$2-A5<62;0%;15%) (рис. 22).
Рис. 21
Рис. 22
Затем нужно скопировать формулу на диапазон ячеек Е6:Е9 автозаполнением ячеек. Результат представлен на рис. 23.
Рис. 33
Расшифровка формулы следующая: если разница между датой Акта уценки и датой договора меньше 62 дней, то уценки товара не произойдет, иначе процент уценки товара будет установлен и равен 15%.
4. Скопировать эту формулу в диапазон E5:E12, а формулу из F5 в диапазон F5:F12.
Анализ показывает, что формулы в столбцах E и F корректно работают для заполненных строк Акта уценки (5,6,7,8,9 строки), а для строк, начиная с десятой, появляется лишняя информация, т.е. формула не проверяет, введены ли все данные для расчета процента и цены после уценки.
5. Изменить формулу в ячейке E5 на следующую, добавив проверку внесения необходимой для расчетов информации:
=ЕСЛИ(ИЛИ($C$2=0;A5=0;D5=0);’ ‘;ЕСЛИ($C$2-A5<31;0%;15%))
Расшифровка формулы следующая: если не введены даты Акта уценки, или дата договора, или цена согласно договору, то процент уценки не может быть установлен, иначе процент уценки будет установлен в зависимости от разницы между датами Акта уценки и договора.
С помощью мастера функций изучите формат логического оператора ИЛИ.
6. Скопировать новую формулу в диапазон E5:E12.
7. Изменить формулу в ячейке F5 на следующую:
=ЕСЛИ(E5=’ ‘;’ ‘;D5-D5*E5).
Расшифровка формулы следующая: если нет процента уценки, то не будет цены после уценки, иначе расчет будет осуществлен согласно формуле.
8. Скопировать новую формулу в диапазон F5:F12 и добавить новые записи.
9. Самостоятельно рассчитать цену товара после уценки в зависимости от даты составления Акта уценки, причем, если между датой уценки и датой договора меньше 31 дня, то процент уценки будет 0%, если между датой уценки и датой договора меньше 61 дня, то процент уценки будет 15%, в остальных случаях - 30%.
Проверить правильность работы электронного Акта уценки и сохранить в личной папке.
Задание 3: Консолидация данных.
1. Добавить в рабочую книгу новые листы, так, чтобы общее количество листов было 8. Назвать листы рабочей книги следующим образом : Лист1 – МЕНЮ, Лист2 – СЕВЕР, Лист3 – ЮГ, Лист4 – ЗАПАД, Лист5 – ВОСТОК, Лист6 – НЕСВЯЗАННАЯ КОНСОЛИДАЦИЯ, Лист7 – СВОДНЫЙ ОТЧЁТ (Рис. 34).
Рис. 34
2. Сгруппировать листы СЕВЕР – СВОДНЫЙ ОТЧЁТ рабочей книги. Для этого, удерживая клавишу CTRL, щёлкать мышкой на ярлыках листов. Если листы сгруппированы, то они выделяются белым цветом. При внесении информации на один из листов группы, информация автоматически заносится на все листы группы.
3. Набрать таблицу следующего вида (рис. 35). Таблица содержит сведения о выполнении плана продаж товаров по одному из регионов компании. Она автоматически заносится на все листы выделенной группы, поэтому набирать нужно только постоянную информацию, общую для всех таблиц группы листов (всех регионов).
Рис. 35
Рис. 36
4. Разгруппировать листы. Для этого, нажав клавишу CTRL, щёлкать левой клавишей мыши на ярлыках всех листов. Они должны стать серого цвета. Внести на листы Север, Юг, Запад, Восток переменную информацию: количество товаров, стоимости товаров и в заголовке внести названия регионов. Пример заполнения одного листа представлен на рис. 36. Для других листов (Юг, Запад, Восток) внести новые значения в поля Количество и Стоимость.
5. Перейти на лист Сводный отчёт. Выделить диапазон ячеек В6:С15. Вызвать диалоговое окно Консолидация: Вкладка ДАННЫЕ/РАБОТА С ДАННЫМИ/КОНСОЛИДАЦИЯ (Рис. 37). В поле Функция выбрать Сумма. Щёлкнуть мышкой в поле Ссылка и ввести ссылку на консолидируемый диапазон ячеек: перейти на лист Север, выделить диапазон ячеек Стоимость и Количество без заголовка и итоговой строки (Рис. 38), нажать кнопку Добавить в окне Консолидация. Аналогично ввести данные для листов Юг, Запад. Восток. Включить флаг Создавать связи с исходными данными. Результат представлен на рис. 39.
Рис. 37
Рис. 38
6. Для выполнения консолидации данных нажать клавишу ОК. Результат представлен на Рис. 39. Слева создана структура: клавиши с изображением знака плюс. При нажатии на такую клавишу структура разворачивается: выдаются строки, показывающие данные с листов Север, Юг, Запад, Восток, на основе которых получены результаты вычисления суммы.
Рис. 39
7. Самостоятельно выполните несвязанную консолидацию данных на листе Несвязанная консолидация. Флаг Создавать связи с исходными данными отключите
Задание 5: Макросы
1. На листе МЕНЮ рабочей книги КОНСОЛИДАЦИЯ разместить объект Word Art: надпись ГЛАВНОЕ МЕНЮ. Для этого надо открыть панель ВСТАВКА / ТЕКСТ вкладка Word Art, выбрать нужный стиль написания и ввести текст надписи: главное меню. Разместить друг под другом несколько прямоугольников с помощью панели ВСТАВКА, вкладка ФИГУРЫ, как показано на рис. 40.
Рис. 40
2. С помощью кнопки ИЗМЕНИТЬ ТЕКСТ в контекстном меню прямоугольников, внутри фигур выполнить надписи: названия листов рабочей книги КОНСОЛИДАЦИЯ. Установить выравнивание надписи ПО ЦЕНТРУ (рис. 41). Выполненные объекты предназначены для создания кнопочного меню. Кнопочное меню будет использовано для быстрого перехода к объектам рабочей книги.
Рис. 41
3 Для каждой кнопки меню нужно записать макросы: программы на языке VISUAL BASIС, позволяющие автоматизировать выполнение определённых операций. В нашем случае макросы будут автоматизировать операции перехода на выбранные при помощи кнопок листы рабочей книги. Для записи макроса необходимо на панели ВИД выбрать вкладку МАКРОСЫ/ЗАПИСЬМАКРОСА 
. Затем ввести имя макроса, например, для макроса, прикрепляемого на кнопку СЕВЕР можно ввести имя Север (рис. 42) и закрыть диалоговое окно Запись макроса.
Рис. 42
4. После этого перейти на лист СЕВЕР и выбрать в панели ВИД, вкладку МАКРОСЫ команду ОСТАНОВИТЬ ЗАПИСЬ. Аналогично написать макросы для кнопок ЮГ, ЗАПАД, ВОСТОК и ОТЧЁТЫ. Прикрепить макрос СЕВЕР к одноимённой кнопке на листе МЕНЮ. Для этого в контекстном меню кнопки СЕВЕР выбрать пункт НАЗНАЧИТЬ МАКРОС и в появившемся диалоговом окне выбрать из списка макросов соответствующий: СЕВЕР (рис. 43) и нажать кнопку ОК. Аналогично прикрепить все макросы к кнопкам меню.
Рис. 43
5. На листах СЕВЕР, ЮГ, ЗАПАД, ВОСТОК и ОТЧЁТ создать кнопки или объекты (например объекты Word Art) для возврата на лист МЕНЮ. Затем написать макрос с именем ВОЗВРАТ, осуществляющий переход на лист меню. Прикрепить этот макрос к кнопкам (или другим объектам, выполняющим их функции), как показано на рис. 44. Выполнить аналогичные действия для всех листов рабочей книги КОНСОЛИДАЦИЯ, кроме МЕНЮ.
Рис. 44
7. На листе Меню установить параметры отображения (Кнопка OFFICE, Параметры EXCEL): не выводить Сетку, Заголовки строк и столбцов, линейки прокрутки. Результат представлен на рис. 45. После привязки всех макросов к кнопкам меню возможно его использование для перехода на листы рабочей книги Консолидация и для возврата обратно на лист Меню. При наведении указателя мышки на кнопку, он принимает вид руки.
Рис. 45
Задание 6: Базы данных
1. Переименовать лист 1 рабочей книги EXCEL назвав его Список и выполнить таблицу представленную на рис. 46.
Рис. 46
2. Переименовать лист 2 рабочей книги EXCEL, назвав его Сортировка. Скопировать всю информацию с листа Список на лист Сортировка. Выполнить сортировку данных таблицы Автосалон. Для этого выделить диапазон ячеек A2:F9, и на панели ДАННЫЕ выбрать вкладку СОРТИРОВКА и заполнить диалоговое окно Сортировка как показано на рис. 47.
Рис. 47
Результат выполнения трехуровневой сортировки представлен на рис. 48. Сортировка выполняется по полю Продавец, затем по продавцам с одной фамилией по полю Дата и затем по строкам таблицы с одинаковыми датами по полю Марка.
Рис. 48
5. Переименовать лист рабочей книги EXCEL, назвав его Итоги. Скопировать всю информацию с листа Сортировка на лист Итоги. Удалить строку Итого таблицы Автосалон, установить курсор в таблице Автосалон и на вкладке ДАННЫЕ/СТРУКТУРА и выбрать пункт ПРОМЕЖУТОЧНЫЕ ИТОГИ (Рисунок 49). Появится таблица ИТОГИ. В диалоговом окне Промежуточные итоги заполнить поля как показано на рис. 50.
Рис. 49
Рис. 50
Результат выполнения этой операции представлен на рис. 51.
Рис. 51
Нажатием на кнопки свернуть структуру, представленную на рис. 51 и получить результат, представленный на рис. 52.
Рис. 52
Удерживая нажатой клавишу CTRL, щелкать левой кнопкой мыши на заголовках колонок B, C, D и E для их выделения. В контекстном меню выделенных столбцов выбрать пункт Скрыть – рис. 53.
Рис. 53
Результат представлен на рис. 54.
Рис. 54
По этой таблице построить круговую диаграмму. Для этого выделить ячейки A6:F12, выбрать вкладку ВСТАВКА и выбрать КРУГОВУЮ ДИАГРАММУ. Результат показан на рис. 55
Рис. 55
6. Переименовать лист рабочей книги EXCEL на Сводная таблица1. Скопировать на него всю информацию с листа Список. Установить курсор в таблицу Автосалон и выбрать на вкладке ВСТАВКА кнопку СВОДНАЯ ТАБЛИЦА 
(рис. 56).
Рис. 56
7. В диалоговом окне Мастера согласиться с заданным по умолчанию диапазоном $A$2:$F$10 или, если задан другой диапазон, исправить его на нужный. Результат задания диапазона исходных данных представлен на рис. 56. В окне установить флаг Существующий лист. Нажать кнопку ОК. Поле ПРОДАВЕЦ перетащить мышкой в поле Фильтр отчёта. Поле Марка перетащить мышкой в поле НАЗВАНИЕ СТРОК, поле Дата перетащить в поле НАЗВАНИЕ СТОЛБЦОВ, а поле Стоимость с НДСв у.е. перетащить в поле ∑ значения. После переноса поля Стоимость с НДСв у.е. в поле ∑ значения оно принимает название Сумма по полю стоимость, так оно становится вычисляемым полем (рис. 57).
Рис. 57
После заполнения закрыть Список полей сводной таблицы. Результат представлен на рис.58.
Рис. 58
Рис. 59
На рис. 60 представлена сводная таблица подготовленная для группировки строк.
Рис. 60
В ней выделены все строки, которые мы будем объединять в группу 1. После выполнения команды группировать таблица будет представлена как показано на рис.61. Здесь Группа 1 была переименована в Филиал 1. Затем были выделены все строки, относящиеся к группе 2, выполнена операция группировки для этих строк и аналогично переименована группа 2.
Рис. 61
По этим группам также могут быть определены итоги. Для подведения итогов нужно выделить группу и выбрать в контекстном меню пункт Параметры поля (рис. 62).
Рис. 62
Результат представлен на рис.63.
Рис. 63
Задание 7: Вычисление таблицы подстановок.
1. Ввести данные, представленные на рис 64. Для ввода функции ПЛТ, возвращающей сумму периодического платежа, нажать кнопку fx в строке ввода данных и выбрать категорию ФИНАНСОВЫЕ. Ввести аргументы функции. Результат представлен на рис 65.
Рис. 64
2. В ячейки D11:D17 ввести тестируемые значения годовой процентной ставки, представленные на рис. 58. В ячейке E10 укажите адрес формулы, для которой требуется получить список результатов =E8. Результат: для формулы из ячейки E8 будет вычислен для каждого значения процентной ставки в таблице.
Рис. 65
3. Выделите ячейки D10:E17. Выбрать панель ДАННЫЕ вкладку РАБОТА С ДАННЫМИ/АНАЛИЗ «ЧТО-ЕСЛИ»/ТАБЛИЦА ДАННЫХ так, как это показано на рис. 66: в поле ПОДСТАВЛЯТЬ ЗНАЧЕНИЕ ПО СТРОКАМ укажите адрес ячейки $Е$4, и нажмите ОК. Е4 – это ячейка, в которую мы последовательно вводили бы значения процентной ставки, если бы проводили исследования выплаты вручную. EXCEL автоматически подставит вычисленные значения выплат. Сверить результат с рис. 67.
Рис. 66
Рис. 67
4. Выполнить таблицу подстановок с двумя изменяющимися переменными и одной формулой. Результат представлен на рис. 68.
Рис. 68
Задание 8: Финансовые функции EXCEL.
1. Набрать таблицу, представленную на рис. 69, содержащую сведения об износе имущества предприятия. 
Рис. 69
Рис. 70
2. В графах СУММА АМОРТИЗАЦИИ будут вводиться формулы для вычисления амортизационных отчислений. В EXCEL возможны три варианта расчёта амортизационных отчислений, поэтому на рис. 69 представлены три столбца для формул различного вида. Для ввода формул в столбец Е требуется установить курсор в ячейку Е4 и вызвать МАСТЕР ФУНКЦИЙ, нажав кнопку 
в строке ввода формул. Далее нужно из категории ФИНАНСОВЫЕ выбрать функцию АСЧ и задать её параметры в виде, представленном на рис. 70. Затем нажать ОК и скопировать формулу из ячейки Е4 на диапазон ячеек Е5:Е11 с использованием автозаполнения. Результат представлен на рис. 71.
Рис. 71
3. В ячейку F4 ввести формулу с использованием ещё одной функции для вычисления амортизационных отчислений – ФУО. Функция находится в категории ФИНАНСОВЫЕ. Пример заполнения окна параметров функции ФУО представлен на рис. 72. После ввода формулы её нужно скопировать на диапазон ячеек F5:F11 автозаполнением. Результат представлен на рис. 73.
Рис. 72
Рис. 73
4. В ячейку G4 ввести формулу с использованием финансовой функции для определения амортизационных отчислений – ДДОБ. Пример заполнения окна параметров функции ДДОБ представлен на рис. 74. Затем нужно скопировать формулу на диапазон ячеек G5:G11 автозаполнением ячеек. Результат представлен на рис. 75.
Рис. 74
Рис. 75
5. По трём видам начисления амортизации имущества построить линейную диаграмму с помощью МАСТЕРА ДИАГРАММ. Для этого нужно на панели ВСТАВКА выбрать вкладку ДИАГРАММЫ. Результат представлен на рисунке 76.
Рис. 76
Название диаграммы и подписи осей задать на вкладке МАКЕТ при выделенной диаграмме.