«Искомое_значение» - это значение, которое должно быть найдено в первом столбце табл_массива. Искомое_значение может быть числовым, логическим значением, ссылкой или текстовой строкой.
«Табл_массив» - это таблица с информацией, в которой ищется строка, где содержимое первого поля соответствует Искомому_значению. Можно использовать ссылку на интервал или имя интервала. «Табл_массив» должен быть упорядочен по первому столбцу. Данные можно упорядочить следующим образом: в меню Данные выбрать команду Сортировка и установить переключатель По Возрастанию. Регистр не учитывается (т. е. строчные и заглавные буквы не различаются).
«Номер_индекса_столбца» - это номер столбца в «табл_массиве», из которого должно быть возвращено соответствующее значение. Если «номер_индекса_столбца» равен 1, то возвращается значение из первого столбца аргумента «табл_массив»; если «номер_индекса_столбца» равен 2, то возвращается значение из второго столбца аргумента «табл_массива» и так далее. Если «номер_индекса_столбца» меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если «номер_индекса_столбца» больше, чем количество столбцов в аргументе «табл_массив», то функция ВПР возвращает значение ошибки #ССЫЛ!.
«Диапазон _просмотра» - это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА (1) или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем «искомое_значение». Если этот аргумент имеет значение ЛОЖЬ (0), то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
Для более наглядного представления табличных данных часто используют графики и диаграммы. Средства программы Excel позволяют создать диаграмму, основанную на данных из электронной таблицы, и разместить ее в той же самой рабочей книге.
Вопросы для самоконтроля
1 Для чего предназначен табличный процессор Microsoft Excel?
2 Опишите элементы рабочего окна программы.
3 Информацию каких типов можно заносить в ячейки табличного процессора Microsoft Excel?
4 Что в Excel понимается под формулой?
5 Опишите порядок создания формул.
6 Как просмотреть электронную таблицу в формате формул?
7 Какие категории функций входят в состав табличного процессора Microsoft Excel? Охарактеризуйте их.
8 Опишите работу с Мастером функций.
9 Что включает понятие Формат, как отформатировать данные в ячейках электронной таблицы?
10 Как отсортировать данные в таблице по заданному критерию?
11 Как выполнить условное форматирование данных?
12 В каких случаях необходимо использование команды «Специальная вставка»?
13 Какие типы диаграмм можно построить средствами табличного процессора Microsoft Excel?
14 Опишите порядок работы с Мастером диаграмм.
15 Назовите отличия в процессе распечатки документов Word и Excel.
Лабораторная работа 1 Создание, редактирование и форматирование электронной таблицы, работа с формулами и функциями
Цель: приобретение практических навыков работы с табличными документами в среде Microsoft Excel
Материалы и оборудование: ПК
Задание
1) Загрузите ТП Microsoft Excel и ознакомьтесь с элементами окна программы. Изучите главное меню и панели инструментов.
2) До создания таблиц установите параметры страницы.
3) Сформируйте таблицу 1 и заполните ее 10 записями:
Таблица 1 - Расчётная ведомость по заработной плате
| Таб. № | ФИО | Начислено | Удержано | К выдаче | |||||
| Оклад | Надбавка | Премия | ИТОГО начислено | Подоходный налог | Пенсионный фонд | ИТОГО удержано | |||
| Итого: |
4) Сохраните таблицу под именем Расчётная ведомость в своей папке на сервере Math_server
5) Сформируйте и заполните таблицу 2:
Таблица 2 - Производство и реализация продукции растениеводства
| Показатели | Урожайность, ц\га | Площадь, га | Валовой сбор, ц | Реализовано, ц | Уровень товарности, % | |||||
| 2005г. | 2006г. | 2005г. | 2006г. | 2005г | 2006г. | 2005г. | 2006г. | 2005г. | 2006г | |
| А | ||||||||||
| Зерновые, всего: в т.ч. | ||||||||||
| Озимые зерновые | 28,3 | 22,6 | ||||||||
| Яровые зерновые | 30,1 | 34,2 | ||||||||
| Зернобобовые | 24,9 | 38,2 | ||||||||
| Картофель | 128,2 | 158,9 | ||||||||
| Овощи открытого грунта | 199,8 | |||||||||
| Рапс | 11,5 | 22,2 | ||||||||
| Кормовые корнеплоды | 418,4 | |||||||||
| Кукуруза на силос | 235,6 | - | - |
4) Сохраните таблицу под именем Продукция растениеводства в своей папке на сервере Math_server;
5) Сформируйте и заполните таблицу 3:
Таблица 3 - Расчёт прибыли и уровня рентабельности по группам
и видам продукции.
| № п/п | Группа и вид продукции | Выручка, млн. руб. | Полная себестоимость млн. руб. | Прибыль, млн. руб | Уровень рентабельности, % | ||||
| 2005 г. | 2006 г. | 2005 г. | 2006 г. | 2005 г. | 2006 г. | 2005 г. | 2006 г. | ||
| 1. | Зерновые в т.ч. | 148,21 | 912,83 | 103,28 | 766,19 | ||||
| 1.1. | пшеница | 87,5 | 375,5 | 65,9 | 324,12 | ||||
| 1.2. | ячмень | 37,9 | 124,6 | 22,6 | 101,5 | ||||
| 1.3. | рожь | 13,4 | 274,63 | 8,88 | 231,5 | ||||
| 1.4. | овес | 9,41 | 138,1 | 5,9 | 109,07 | ||||
| 2. | Картофель | 34,33 | 64,34 | 22,39 | 41,54 | ||||
| 3. | Овощи | 26,04 | 87,76 | 25,311 | 39,44 | ||||
| 4. | Молоко | 189,34 | 592,58 | 193,52 | 687,3 | ||||
| 5. | Мед | ||||||||
| 6. | Прирост живой массы КРС | 132,15 | 192,94 | 135,79 | 113,61 | ||||
| Всего | |||||||||
| Среднее | |||||||||
| Максимум | |||||||||
| Минимум |
6) Сохраните таблицу под именем Показатели по продукции растениеводства в своей папке на сервере Math_server;
7) Оформите и защитите отчёт по лабораторной работе.
Ход работы
Порядок работы пользователя по Установке параметров страницы, Сохранению таблицы на магнитном диске, Распечатке на принтере, Завершению работы с программой аналогичен порядку работы в текстовом процессоре Microsoft WORD.
Выделение диапазона ячеек таблицы:
1 способ:
Удерживая нажатой клавишу Shift и нажимая клавиши управления курсором
[ ], [ ¯ ], [ ® ], [ ]
2 способ:
Нажать левую клавишу мыши и, не отпуская, перемещать курсор мыши по ячейкам.
Для того чтобы снять пометку, следует щелкнуть левой клавишей мыши вне пометки.
Значения таблицы 1 получите с использованием следующих рекомендаций:
Надбавка к окладу составляет 20% от оклада.
Премия – 17% от оклада
Подоходный налог = 9% от (Итого начислено – базовая величина)
Отчисления в пенсионный фонд = 1% от Итого начислено
В таблице 2 выполните следующие расчеты:
- Валовой сбор = Урожайность * Площадь;
- Уровень товарности = Реализовано / Валовой сбор * 100
В таблице 3 выполните следующие расчеты:
- Прибыль = Выручка – Себестоимость;
- Уровень рентабельности = Прибыль / Себестоимость * 100%
Создание формул
- Поместить курсор в ячейку, в которой будет находиться формула
- Ввести знак «=»
- Щелкнуть на ячейке, содержимое которой используется в формуле. Вокруг нее появится «муравьиная дорожка» и адрес ячейки поместится в формулу
- Ввести нужный арифметический знак
- Щелкнуть на другой ячейке, которая используется в формуле
- Нажать клавишу Enter
Форматирование ячеек
- выделить диапазон ячеек
- выбрать команду меню Формат ®Ячейки…
Также можно воспользоваться панелью инструментов ФОРМАТИРОВАНИЕ
Работа с мастером функций:
- Поместить курсор в ячейку, в которую нужно вставить функцию
- Выбрать команду Вставка-Функция или щелкнуть на кнопке 
на панели инструментов. На экране появится окно Мастера функций.
- Выбрать из списка категорию и имя функции.
- Ввести необходимые аргументы.
Наиболее часто используемые функции (минимум, максимум, среднее) можно выбрать на панели инструментов СТАНДАРТНАЯ.
Рисунок 3 – Меню кнопки Автосумма
Лабораторная работа 2 Создание, редактирование и форматирование электронных таблиц на разных рабочих листах книги Excel, работа со ссылками и функциями в формулах
Цель: приобретение практических навыков работы со ссылками и функциями в среде Microsoft Excel
Материалы и оборудование: ПК
Задание
1) Лист 1 рабочей книги переименуйте в «Исходные данные» и сформируйте таблицу предложенной формы (Таблица 1).
| Таблица 1 - Исходные данные для расчета экономических показателей | |||||
| Наименование хозяйства | Основные производственные фонды, млн. руб. | Площадь сельхозугодий, га | Валовая продукция, тыс. руб. | Полная себестоимость зерна, млн. руб. | Выручка за зерно, млн. руб. |
| А | |||||
| Береговое | |||||
| Бобовичи | |||||
| Братское | |||||
| Городня | |||||
| Дружба | |||||
| Звёздное | |||||
| Красная гвоздика | |||||
| Красная гора | |||||
| Маяк | |||||
| Озёрное |
| А | |||||
| Октябрь | |||||
| Отрадное | |||||
| Рассвет | |||||
| Речное | |||||
| Светлый путь | |||||
| Свиричи | |||||
| Солнечное | |||||
| Тепличное | |||||
| Терешковичи | |||||
| Урицкое |
2) Второй рабочий лист книги EXCEL переименуйте в «Расчеты» и создайте Таблицу 2, которая должна формироваться на основании: а) ссылок на ячейки исходной таблицы 1, б) функции ВПР, в) формул расчета. В таблице 2 графы, которые должны иметь ссылки, выделены курсивом; графы, которые должны содержать функцию ВПР, выделены подчеркиванием. В таблице выполните все необходимые расчеты.
| Таблица 2 - Экономические показатели эффективности работы предприятий | |||
| Наименование хозяйства | Уровень фондооснащенности, млн.руб.\га | Уровень производства валовой продукции, тыс. руб\га | Уровень рентабельности по зерну, % |
| А | гр.1 / гр.2 * 100 | гр.3 / гр.2 | (гр.5 - гр.4) / гр.4 * 100 |
| В среднем | |||
| Минимум | |||
| Максимум |
3) В таблице 2 отсортируйте хозяйства по возрастанию по уровню производства валовой продукции.
К данным таблицы примените условное форматирование: уровень рентабельности меньше 0 % выделите красным цветом; от 0-100% синим, свыше 100% - зеленым.
4) Переименуйте лист 3 в «Средние». Скопируйте из Таблицы 2 указанные ячейки и создайте таблицу 3:
| Таблица 3 - Статистические показатели по совокупности хозяйств | |||
| Показатели | в среднем | максимум | минимум |
| Уровень фондооснащенности, млн.руб./га | |||
| Уровень производства валовой продукции, тыс. руб/га | |||
| Уровень рентабельности по зерну, % |
5) Сохраните книгу Excel под именем «Экономические показатели» в своей папке на сервере Math_server.
6) Оформите и защитите отчёт по лабораторной работе.
Ход работы
Сортировка данных в таблице:
- выделить диапазон ячеек, подлежащих сортировке (только числовые данные);
- выбрать команду Данные → Сортировка, где указать в поле «Сортировать по» столбец, по которому будет проводиться сортировка и способ сортировки (по возрастанию или убыванию);
Рисунок 4 – Окно команды Сортировка диапазона
Условное форматирование:
- выделить столбец, для которого будет использовано форматирование;
- в меню выбрать команду Формат → Условное форматирование, и указать условия форматирования (см. пример). Имеется возможность указывать до трех условий, каждое новое условие появляется при нажатии кнопки А также>>. Параметры форматирования (Шрифт, Границы, Вид) указываются в диалоговом окне, которое появляется при нажатии кнопки Формат.
Рисунок 5 – Окно команды Условное форматирование