2015-03-27
8482
| Код ошибки | Причина возникновения |
| # # # # # | Числовые данные не помещаются в ячейку по ширине или при вычислении по формуле из даты вычисляется более поздняя |
| #ДЕЛ/О | В знаменателе формулы получается нулевое значение или стоит ссылка на пустую ячейку. Последнее может оказаться связано с неверным использованием относительной ссылки |
| #ЗНА Ч! | У функции пропущен обязательный параметр или же в качестве параметра использована ссылка на пустую ячейку или ячейку с данными, тип которых не соответствует типу нужного параметра
 
|
| #ЗНАЧ! | В качестве аргументов функции, которые должны быть единичным значением, использована ссылка на интервал |
| #ИМЯ? | Опечатка в имени функции или имени ячейки (диапазона) может быть вызвана пропуском двоеточия при указании диапазона или кавычек при указании тестового параметра. Например, =СУММ(A1D10) |
| #ЧИСЛО! | Использован неприемлемый аргумент в функции с числовым аргументом. Например, КОРЕНЬ(-1). При вычислении получилось число, слишком большое или слишком маленькое для программы Excel. Как правило, это следствие неправильности формулы Использована функция, при вычислении которой применяются итерации и при этом итерации не сходятся и результат не может быть получен |
| #ПУСТО! | В формуле использовано пересечение диапазонов, не включающее ни одной ячейки |
| #Н/Д | Значение недоступно функции или формуле: § В функциях ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР указано неверное значение аргумента «искомое_значение» § Для просмотра значений в несортированной таблице используются функции ВПР, ГПР или ПОИСКПОЗ § В формуле массива используется аргумент, не соответствующий диапазону, определяющемуся числом строк и столбцов, который указан в формуле массива § Не заданы один или несколько необходимых аргументов стандартной или пользовательской функции листа § Используется недоступная пользовательская функция |
| #ССЫЛКА! | Формула указывает на ячейку, которая отсутствует на рабочем листе. Чаще всего такая ошибка возникает вследствие копирования формулы с относительной адресацией влево или вверх, из-за чего возникают ссылки на столбцы предшествующие столбцу А, и строки, предшествующие первой. |
Упр. 2. Вычисление n-ого члена и
суммы арифметической прогрессии
|
|
|
Данное упражнение выполняем на соответствующем листе в книге Работа (см. Упр. 1). Вычислите n-ый член и сумму арифметической прогрессии. При расчетах в таблице воспользуйтесь формулой an=an-1+d, где an -n-ый член прогрессии и Sn=(a1+an)*n/2 –сумма n первых членов прогрессии. Разность арифметической прогрессии d=0,725, а первый член а1=-2. При вводе данных обратите внимание на настройки Excel!
|
|
|
Технология работы:
1. Введите в ячейку А1 заголовок таблицы (Рис. 9).
2. В ячейку A2 введите d, в ячейку B2 – n, в C2 - an, в D2- Sn. Примените полужирный стиль начертания символов и выровняйте их по центру. Для набора нижних индексов воспользуйтесь командой из контекстного меню Формат ячейки, Шрифт или на ленте Шрифт.
3. В ячейку А3 введите величину разности d (0,725) и заполните столбец ячеек тем же числом. Для этого выделите ячейку А3. При этом данная ячейка окаймляется рамкой, в правом нижнем углу которой располагается маркер заполнения. Подведите указатель мыши к маркеру, мышь примет вид черного креста, протяните маркер на несколько ячеек вниз, выделенные ячейки заполнятся данными.
4. В ячейку В3 введите 1 и протяните за маркер заполнения на 10 ячеек вниз, при этом удерживайте клавишу Ctrl.
5. В ячейку С3 введите значение первого члена прогрессии, которое равно -2.
6. При n>1, an=an-1+d, поэтому в ячейку С4 введите формулу =С3+А3. Для этого в ячейке С4 наберите знак равенства, щелкните мышью по ячейке С3, в строке формул появится ее адрес, затем введите с клавиатуры знак “+” и щелкните мышью по ячейке А3. Зафиксируйте ввод формулы нажатием клавиши Enter, в ячейке окажется результат вычисления по формуле, а в Строке формул - сама формула. Если вы неправильно набрали формулу, то исправить ее можно в Строке формул, предварительно выделив ячейку.
7. В расчетах будем использовать формулу sn=a1+a2+a3+…+an, здесь s1=a1, поэтому в ячейку D3 введите число равное -2. В ячейку D4 введите формулу = D3 + C4. Для ввода формулы воспользуйтесь советами пункта № 6 этого упражнения.
8. Для оформления ячеек с данными выделите эти ячейки, на ленте инструментов выбрать Формат, Автоподбор ширины столбца.
9. Для оформления заголовка выделите интервал ячеек от А1 до D1 и выполните команду контекстного меню Формат Ячеек, вкладка Выравнивание и установите переключатели Объединение ячеек и Переносить по словам. Если заголовок таблицы виден не полностью, то увеличьте ширину данной строки (Рис. 10).
10. Установите границы таблицы, выполнив команду контекстного меню Формат Ячеек, вкладка Граница.
Рис. 9. Таблица к упр.2
Рис. 10. Изменение ширины строки
11. Просмотрите таблицу, для этого выполните команду Office, Предварительный просмотр.
Упр. 3. Создание таблицы
Cведения о товарах и ценах с учетом налога с продаж
Составьте таблицу, содержащую сведения о товарах и ценах с учетом налога с продаж. Подсчитайте налог с продаж и цену продажи (названную в таблице суммой) для каждого товара (Рис. 11).
Технология работы:
Значение налога с продаж задается в виде константы, записанной в процентном формате в ячейке С1. Так как эта константа неизменна для всех строк таблицы, значит, она не должна меняться при копировании формулы, т.е. должна быть абсолютной, в данном примере записываемой в виде $C$1.
Рис. 11. Таблица, содержащая сведения о товарах и ценах с учетом налога с продаж
Для выполнения расчета, необходимо ввести формулы для первой строки с данными первого товара и заполнить их на все строки диапазона. Это можно сделать, например, следующим образом:
1. В ячейку D5 введите формулу =C5*$C$1. Для получения абсолютной ссылки $C$1 нажмите клавишу F4 после того, как поместите в строку формул ссылку C1.
2. В ячейку E5 введите =C5+D5
3. Выделите блок из двух ячеек D5:E5 с формулами для первого товара и скопируйте его на диапазон D5:E9, используя маркер заполнения.
Для того чтобы увидеть в ячейках расчетные формулы, необходимо перейти в режим отображения формул (Рис. 12).
Для просмотра таблицы в режиме отображения формул выполните следующие действия: на ленте инструментов выберите Формулы, Зависимости формул, Показать формулы.
Рис. 12. Таблица в режиме отображения формул
Упр. 4. Создание таблицы умножения
|
|
|
Создайте таблицу умножения чисел от 1 до 10 (Рис. 13).
Для этого в ячейку А1 введите заголовок таблицы. В ячейки В2 по K2 и А3 по А12 введите значения от 1 до 10, используя маркер заполнения. В ячейку В3 введите формулу произведения ячеек В2 и А3, применив нужные ссылки. Растяните данную формулу, используя маркер заполнения по всей площади таблицы.
Рис. 13. Бланк для заполнения таблицы умножения
Упр. 5. Создание бланка Счёта
Создайте бланк Счета, используйте как образец, приведенную ниже таблицу (Рис. 14). Бланк Счета заполняется произвольно. Пунктирные линии в примере служат только для наглядной и правильной разметки таблицы, которой не должно быть в таблице при печати. В документе сетку Excel отключить.
Технология работы:
1. Создайте документ по образцу, используя объединение ячеек.
2. Текст: “Грузоотправитель и адрес”, “Грузополучатель и адрес”, “К Реестру №”, “Поставщик:”, “Адрес:”, “Р/счет №”, “Дополнения:”, “ИТОГО”, “Руководитель предприятия”, “Главный бухгалтер” - внесите в ячейки самого левого столбца и примените выравнивание по левому краю.
3. “Фамилии” и “Дата получения” внесите в ячейки самого правого столбца и примените выравнивание по правому краю.
4. Текст “Счет № ” внесите в ячейку самого левого столбца и примените объединение на нужные ячейки и выравнивание по центру.
5. К заголовкам в таблице Бланка примените выравнивание по центру, как по горизонтали, так и по вертикали.
6. К записям таблицы Бланка примените выравнивание по левому краю.
7. Строки в столбце под “№” в таблице Бланка пронумеруйте от 1 до 10, используя маркер заполнения.
8. Заполните бланк своими данными.
9. В столбцах “Цена”, ”Сумма” таблицы Бланка установите денежный формат числа и число десятичных знаков равно 2.
Рис. 14. Бланк Счета с разметкой таблицы
III.РАБОТА СО ВСТРОЕННЫМИ ФУНКЦИЯМИ
III.1. Описание и способы ввода функций
В любых версиях Excel для различных типов вычислений имеется большое число встроенных функций: математических, статистических, логических, текстовых, информационных и др.
|
|
|
Чтобы использовать функцию, нужно ввести ее как формулу или часть формулы в ячейку рабочего листа. После имени функции в круглых скобках указывается список аргументов. Аргументы отделяются друг от друга точкой с запятой «;», запятой «,» или двоеточием «:» в зависимости от способа представления. В случае если аргументы функции перечисляются напрямую, то используем точку с запятой. Если же в качестве аргумента функции выступает интервал ячеек, то начальную и конечную ячейки интервала отделяем друг от друга двоеточием.
Математические функции выполняют арифметические и тригонометрические вычисления.
Математические функции используют в качестве аргументов числовые значения. Например, функция СУММ(D1:D10; 23; D15) вычисляет сумму числовых значений диапазона ячеек D1: D10, числа 23 и значения ячейки D15.
При использовании тригонометрических функций аргумент (величина угла) указывается в радианах.
Если аргументы функции не попадают в область допустимых значений, то функция принимает значение #Н/Д (неопределенные данные, см.Таблица 3).
Функции в Exсel вводятся обычным набором с клавиатуры или более предпочтительным способом - с помощью диалогового окна Мастер функций (Рис. 15), которое вызывается: вкладка Формулы, на ленте из группы Библиотека функций выбрать команду Вставить Функцию.
Рис. 15. Диалоговое окно Мастер функций для выбора категории и вида функции
Все функции разделены на категории, каждая из которых включает в себя определенный набор функций.
Выбирается категория функции и имя функции, внизу дается краткий синтаксис функции. Если функция использует несколько однотипных аргументов, то указывается символ многоточия (...).
После нажатия кнопки ОК появляется следующее диалоговое окно и осуществляется построение функции, т.е. указание ее аргументов. Каждый аргумент вводится в специально предназначенную для него строку, например, как показано на Рис. 16.
Формулу можно вводить в ячейку. Для вставки в формулу вложенных функций предусмотрена кнопка вызова функции 
, которая находится в левой части строки формул. Глубина вложенности зависит от вида функции. Узнать глубину вложенности функции можно через встроенную справку MS Excel.
Рис. 16. Диалоговое окно для задания аргументов
логической функции ЕСЛИ
Упр. 6. Расчёт расхода материалов для покраски
Данное упражнение выполняем на соответствующем листе в книге Работа (см. Упр. 1).
Создайте таблицу, позволяющую рассчитывать расход материалов для покраски, в зависимости от площади поверхностей (Рис. 17).
Рассчитайте расход материалов для двери (RД) и подоконника (RП), воспользовавшись формулами RД=S1*SД / 10 и RП=S2*SП / 10, где S1 и S2 свои значения. Вычислите максимальный и минимальный по столбцам (D, G) расход материалов для двери и подоконника. Подсчитайте суммарный расход материалов для двери и подоконника в строке Всего, воспользовавшись кнопкой на ленте С умма (илиAlt+=) из вкладкиГлавная.
Рис. 17. Таблица расход материалов для покраски
Для подсчета общего расхода в строке Итого сложите суммарный расход материалов для двери и подоконника.
Упр. 7. Использование математических и
статистических функций
Дана последовательность чисел. Вычислите: сумму чисел, среднее значение, посчитайте общее количество чисел, количество нулевых элементов, количество положительных и сумму отрицательных чисел:
– 5; -41; 0; 71; 13; -11; 20; 0; 15.
Технология работы:
В ячейки В2:J2 внесите предложенные данные.
Для вычисления формул в диапазон В4:В11 введите соответствующие функции:
В4 = СУММ (B2:J2); В5 = СЧЕТ (B2:J2);
В6 = СЧЁТЕСЛИ (B2:J2; “=0”); В7 = СЧЁТЕСЛИ (B2:J2; “>0”); В8 = СУММЕСЛИ(B2:J2; “<0”); В11 = СРЗНАЧ(B2:J2).
В ячейки А4:А11 введите названия функций и проанализируйте работу.
Упр. 8. Изучение итоговых функций
Технология работы:
В ячейки А1:А4 введите исходные данные на основе которых будут вычисляться функции. В столбец С введите значения для проверки результатов, которые получатся в результате выполнения функций приведённых в столбце D (Рис. 18) и проанализируйте их работу.
| A | B | C | D | |
| =СЧЕТ(A1:A4) | ||||
| =СЧЕТЗ(A1:A4) | ||||
| куб | =МИН(A1:A4) | |||
| =МИНА(A1:A4) | ||||
| =МАКС(A1:A4) | ||||
| =МАКСА(A1:A4) | ||||
| =СРЗНАЧ(A1:A4) | ||||
| =СРЗНАЧА(A1:A4) | ||||
| =ДИСП(A1:A4) | ||||
| =ДИСПА(A1:A4) | ||||
| =ДИСПР(A1:A4) | ||||
| 7,5 | =ДИСПРА(A1:A4) | |||
| =СТАНДОТКЛОН(A1:A4) | ||||
| 3,162 | =СТАНДОТКЛОНА(A1:A4) | |||
| =СРОТКЛ(A1:A4) | ||||
| =НАИБОЛЬШИЙ(A1:A4;1) | ||||
| =НАИМЕНЬШИЙ(А1:А4;1) |
Рис. 18. Список итоговых функций
III.2. Применение условного форматирования для таблиц
Условный формат – это формат (например, заливка ячейки или цвет шрифта), который Microsoft Excel автоматически применяет к ячейке, если выполняется указанное условие. Для использования условного форматирования выберите на ленте команду Условное форматирование из вкладки Главная. Откроется меню условного форматирования (Рис. 19):
Рис. 19. Команды условного форматирования
Упр. 9. Показатели производства
Предприятие имеет заказ на выпуск некоторого вида продукции, план выпуска которой определен для каждого месяца календарного года. По приведенным в таблице (Рис. 20) данным необходимо выполнить анализ итогов работы предприятия за год: определить и представить в таблице итоговые данные за год и другие статистические показатели.
Технология работы:
1 Заполните таблицу исходными данными (Рис. 20). Обратите внимание на три столбца под общим заголовком Месяцы. Эти столбцы дублируют друг друга, обозначая одни и те же временные промежутки, существенное отличие все же есть. Здесь использованы альтернативные способы работы в режиме Автозаполнения: построение числовых рядов, рядов из дат, использование стандартного списка. Поэтому рекомендуется в учебных целях применить все три способа, отрабатывая навыки работы в этом режиме. Рассмотрим эти способы:
Рис. 20. Показатели производства
· в ячейку А5 введите первое значение и заполните до ячейки А16 включительно с нажатой клавишей CTRL (см. Упр. 2)
· введите в ячейку В5 дату и за маркер заполнения правой кнопкой мыши протяните до ячейки В16 включительно, выберите Заполнить по месяцам
· для диапазона ячеек С5:С16 воспользуйтесь стандартным списком из названий месяцев: наберите в ячейке С5 текст Январь; выберите ячейку С5 и установите курсор мыши на маркер заполнения, нажмите левую кнопку мыши и протяните ее до ячейки С16 включительно
2 Переходим к расчету и анализу итогов работы предприятия. Введите в ячейку F5 формулу для вычисления процента выполнения плана за месяц: =Е5/D5. Выделите ячейку F5 и выполните автоматическое заполнение формулами диапазона ячеек F5:F16, используя маркер заполнения. Представьте полученные данные в процентном формате с тремя десятичными знаками.
3 В ячейке D18 вычислите значение планового задания по выпуску на год — сумму значений диапазона D5:D16, используя инструмент Сумма на вкладке Главная. Выберите ячейку D18 и протяните формулу на ячейку E18.
4 В ячейку F18 скопируйте формулу из ячейки F16.
5 В диапазоне ячеек G5:G16 вычислите для каждого месяца его долю (в процентах) в годовом выпуске, которая вычисляется как отношение выпущенного в каждом месяце к выпущенному за год. Так как во всех формулах диапазона в качестве делителя выступает одна и та же ячейка (сумма за год), то в формуле адрес этой ячейки должен быть задан в виде абсолютной ссылки, чтобы он не изменялся при копировании формулы. Формула в ячейке G5 должна иметь вид: =Е5/$Е$18. Выберите ячейку G5 и протяните формулу до ячейки G16, включительно. Представьте полученные данные в процентном формате с двумя десятичными знаками.
6 В ячейке Е20 посчитайте максимальное значение диапазона ячеек E5:E16.
7 В ячейке Е21 посчитайте минимальное значение диапазона ячеек E5:E16.
8 В ячейке Е22 посчитайте среднее значение диапазона ячеек E5:E16.
9 Выделите диапазон ячеек Е20:Е22 и скопируйте его на диапазон F20:F22, используя маркер заполнения.
10 Для диапазона F20:F22 установите процентный формат с двумя цифрами дробной части.
11 Рассмотрите возможности условного форматирования.
· Можно выполнить условное форматирование для диапазона F5:F16 с целью выделения тех ячеек, в которых значение меньше 1 (или, что тоже самое, меньше 100%). Для этого выделите диапазон F5:F16 и выберите на ленте Главная, Условное форматирование, Правила выделения ячеек..;
· Раскройте список и выберите строку Меньше, введите значение 1 или 100%, в поле выбора ф орматавыберите Пользовательский формат …, появится диалоговое окно Формат ячеек с вкладками Шрифт, Граница, Вид. Определите формат для значений, удовлетворяющих заданному условию, например, измените цвет и начертание. Закройте диалоговые окна, нажмите кнопку ОК.
· Значения в диапазоне ячеек С5:С16 можно выровнять по центру.
· Расположите заголовок таблицы Показатели производства по центру таблицы. Для этого выделите диапазон А1:G1 и выберите из контекстного меню команду Формат ячеек, Выравнивание, Объедение ячеек.
· Так как при печати таблицы линии сетки не видны, то для лучшего восприятия таблицы обведите ее рамками. При желании можно изменить шрифт, его размер и начертание, как для числовых данных, так и для текстов в таблице.
· Можно изменить цвет фона и цвет шрифта отдельных элементов таблицы.
III.3. Использование логической функции Если
Функция ЕСЛИ используется при проверке условий для значений и формул. Логическая функция ЕСЛИ позволяет выбрать результат в зависимости от выполнения/невыполнения некоторого условия. Результатом может быть число, текст или логическое значение.
Синтаксис функции ЕСЛИ:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение. Если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.
Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент — строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.
Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент — строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.
Примечание :
§ До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования более сложных проверок.
§ Когда значения аргументов значение_если_истина и значение_если_ложь вычислены, функция ЕСЛИ возвращает полученное значение.
§ Если один из аргументов функции ЕСЛИ является массивом, при выполнении функции ЕСЛИ вычисляются все элементы массива.
§ Excel предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для вычисления числа появлений текстовой строки или числа в диапазоне ячеек используйте функцию СЧЁТЕСЛИ (см. Упр. 7). Для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, используйте функцию СУММЕСЛИ (см. Упр. 7).
Рассмотрим задачи с использованием логической функции ЕСЛИ.
Упр. 10. Вычисление логарифмической функции
Вычислите ln (x – 2) на интервале от 0 до 7 с шагом 1 при условии, если аргумент X>2, иначе в вычисляемой ячейке написать АРГУМЕНТ МЕНЬШЕ ИЛИ РАВЕН 0
Технология работы:
Решение логической задачи желательно сначала обдумать с использованием числовой прямой (Рис. 21).
Рис. 21. Решение задачи упр.10 на числовой прямой
Введите значения аргумента X в диапазон ячеек D3:D10 с шагом 1.
В ячейку E3 введите формулу, выполняющую условие:
=ЕСЛИ(D3>2; LN(D3-2); “АРГУМЕНТ МЕНЬШЕ ИЛИ РАВЕН 2”)
Протяните данную формулу за маркер заполнения до ячейки E10.
Упр. 11. Использование вложенной функции Если
Вычислите комиссионные, которые торговая фирма выплачивает своим агентам в зависимости от объема продаж. Продажа на сумму до 1.тыс. дает 3% комиссионных, от 1 до 5 тыс. - 5%, а продажи на сумму свыше 5 тыс.- 12%.
Технология работы:
Решим задачу с использованием числовой прямой (Рис. 22).
Рис. 22. Решение задачи упр.11 на числовой прямой
Если ячейка F5 содержит сумму продаж, то процентную ставку комиссионных определяет формула
=ЕСЛИ (F5<1000; 3%; ЕСЛИ(F5>5000; 12%; 5%))
III.4. Использование функций из категории Дата и время
При работе с датами электронные таблицы хранят их в виде целого числа, соответствующего количеству дней, прошедших с 01.01.1990 до указанной даты. Но на экране даты представляются в привычной нам форме, если для соответствующей ячейки задан формат Дата.
В экономических задачах обычно требуется определить продолжительность периодов между двумя датами. Для этого подойдут функции даты (календарные функции):
СЕГОДНЯ () - определение текущей даты по внутрисистемным часам;
ДЕНЬ (дата) - выделение дня из даты;
МЕСЯЦ (дата) - выделение месяца из даты;
ГОД (дата) - выделение года из даты;
ДНЕЙ360 (дата начала; дата конца) – количество дней между двумя датами из расчета Год + 12 месяцев по 30 дней.
Упр. 12. Определение возраста по дате рождения
Вычислите свой возраст по дате рождения.
Технология работы:
В ячейке D5 запишите Вашу дату рождения в формате даты. Определите в ячейке F5 возраст, учитывая только год, т.е. возраст как разность между текущим годом и годом рождения.
Для этого в ячейку F5 необходимо ввести формулу
=ГОД (СЕГОДНЯ ()) – ГОД (D5)
Может так случиться, что результат покажется вам странным, так как EXCEL установил для него формат Даты. Для перехода к числовому формату выделите ячейку F5 и установите для нее формат общий.
Для получения полных лет возраста введите в ячейку G5 формулу
= ЕСЛИ (МЕСЯЦ (СЕГОДНЯ ())>МЕСЯЦ (D5);F5;F5-1)
III.5. Применение формул с использованием массивов
Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива обрабатывает несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, с той разницей, что для ввода такой формулы используются клавиши CTRL+SHIFT+ENTER.
Упр. 13. Расчёт оклада и начисление премии преподавателей
Даны две таблицы: Единая тарифная сетка (ETC) и Список преподавателей небольшой кафедры (Рис. 23). Данные таблицы размещены в разных книгах.
Рис. 23. Таблицы: Единая тарифная сетка (ETC) и Список преподавателей кафедры
Нужно рассчитать оклад каждого преподавателя и начислить им премию в размере 19% от оклада. Оклады рассчитываются на основе единой тарифной сетки (ETC). Чтобы вычислить оклад, например для 9-ого разряда, нужно умножить минимальный оклад (Х) на коэффициент 3,53.
Технология работы:
1. Создайте рабочую книгу. Сохраните её под именем Работа1в папке Excel.
2. На листе ets книги Работа1 введите в диапазон ячеек A1:B16 таблицу ЕТС (Рис. 23).
3. В третьем столбце (C) таблицы ETS рассчитайте оклады для каждого разряда сразу для всего столбца (работа с массивом). Выделите блок ячеек C4:C14 введите знак = с помощью мышки выделите ячейку В2 затем знак * и блок ячеек B4:B14. В строке формул в результате появится формула: =В2*B4:B14. Подтвердите ввод формулы, нажав комбинацию клавиш Ctrl+Shift+Enter.
4. Полученному блоку А4:С14 дайте имя ets, для этого выделите блок ячеек А4:С14 и выберите на ленте вкладку Формулы, Присвоить имя, напишите имя ets и нажмите OK. Теперь при выделении блока ячеек А4:С14 в строке имени появится имя ets.
5. На листе Упр_13 книгиРаботасоздайте таблицу Cписок преподавателей кафедры в диапазоне ячеек A1:C14. Осуществите подгонку ширины столбцов (см. Упр. 2).
6. Внимание! Книги Работа и Работа1 должны быть открыты в одном приложении Excel.
7. В столбце D таблицы Список преподавателей кафедры рассчитайте оклады для каждого преподавателя. Для этого используйте Мастер функций, выберите функцию ВПР.
8. При вводе второго аргумента функции переключитесь в рабочую книгу Работа1 (для этого нажмите клавиши Ctrl+F6- переход к другому открытому документу). В формуле уместно указать четвертый аргумент ВПР равный нулю, так как следует искать точное соответствие разрядов.
9. Окончательно формула имеет вид: =ВПР(С3;Работа1.xls!ets;3;0).
В процессе набора второй аргумент имеет вид [Работа1.xls]ets!ets, т.е. в рабочей книге Работа1.xls. выбирается рабочий лист ets, а на нём блок с именем ets; но так как имя est является глобальным для рабочей книги, то окончательная ссылка упрощается. Скопируйте формулу с помощью маркера заполнения для остальных преподавателей.
10. Рассчитайте премию. Введите в пустую ячейку 19 и присвойте имя Процент (Формулы, Присвоить и мя напишите имя Процент ).
11. Вычислите (массивом) в столбце Е сумму премии. Формула будет иметь следующий вид: =D3:D14*Процент. В ячейку E2 впишите Премия. Наложите на столбец D и E формат с двумя цифрами после точки (т.е. рубли-копейки) (см. Раздел II.1).
12. Сохраните рабочие книги Работа.xls и Работа1.xls. Закройте обе книги. Вновь откройте книгу Работа.xls. На вопрос об обновлении связи ответьте Да. Убедитесь в работоспособности таблицы (например, замените какому-нибудь преподавателю разряд - оклад этого преподавателя изменится).
III.6. Разрешение вопросов, связанных с исследованием зависимости формул
Упр. 14. Вычисление основных параметров для треугольника
Даны три стороны треугольника a, b, с. Требуется вычислить его площадь по формуле Герона 
,
где p – полупериметр 
, а также радиус вписанной окружности 
и радиус описанной окружности 
.
Технология работы:
1. Введите данные, как показано на Рис. 24. В ячейку B6 введите формулу =(В2+В3+В4)/2.
| A | B | C | |
| Стороны треугольника | |||
| a | |||
| b | |||
| c | |||
| p | |||
| S |
Рис. 24. Проектирование расчетов
2. Присвоим имена ячейкам B2, B3, B4, B6. Выделите блок A2:B6, выберите на ленте вкладку Формула, из группы Определенные имена выберите Создать из выделенного фрагмента. Выберите вариант в столбце слева, нажмите ОК.
Теперь, выделяя ячейку B2, в строке формул в поле Имя вы увидите не адрес B2, а имя a. Для ячейки B4 имя не c, как можно, было ожидать, а c_. Это связано с тем, что имена c и r в Excel зарезервированы (c - column - столбец, r - row - строка). Поэтому Excel ввел в имя символ подчеркивания.
3. Введите в B8 формулу Герона
= КОРЕНЬ(p*(p–a)*(p–b)*(p–c_)), через ниспадающий список поля имён. Если бы не введенные имена, нам пришлось бы набирать формулу
=КОРЕНЬ(B6*(B6–B2)*(B6–B3)*(B6–B2)),
что намного труднее для восприятия.
4. Перейдем к форматированию ячеек. Выровняем названия величин по правому краю. Выделите блок А2:A8 и нажмите кнопку По правому краю.
Введите длину стороны a, равную 2. Тогда S = 3.799671 (что бы увидеть число целиком, увеличьте ширину столбца B). Для отображения точности три знака после точки необходимо выделить B8 и несколько раз нажмите кнопку 
- Уменьшить разрядность, пока число не приобретет нужный формат 3.800.
Отмените форматирование нажав на кнопку отмена 
несколько раз или Ctr+Z и добейтесь того же результата другим способом. Для этого, выберите вкладку Число на ленте инструментов Главная, в списке Числовые форматы: – Числовой. Далее самостоятельно разберитесь, как задать нужное количество разрядов.
5. Дополним таблицу вычислением радиусов вписанной и описанной окружностей. Присвойте ячейке B8 имя - S. Для этого выделите B8 и в поле имя введите имя S, нажмите Enter. В ячейки D10 и F10 введите r и R, а в E10 и G10 – соответствующие формулы (см. выше). Наложите на эти ячейки такие же форматы, как и ранее. Для этого воспользуйтесь кнопкой 
- Формат по образцу. Например, выделите A8, нажмите кнопку 
и "покрасьте" кистью E10. У Вас должен получиться следующий результат (Рис. 25).
| A | B | C | D | E | F | G | |
| Стороны треугольника | |||||||
| a | |||||||
| b | |||||||
| c | |||||||
| p | 5.5 | ||||||
| S | 3.800 | ||||||
| r | 0.691 | R | 2.632 |
Рис. 25. Результат выполнения упр.14
6. Исследование зависимостей ссылок в формулах. Выделите G10 и выберите на ленте вкладку Формулы, Зависимости формул, Влияющие ячейки. На экране протянутся синие стрелки от ячеек, содержащих длины сторон и площадь треугольника, к ячейке G10. Исследуйте зависимости и для других ячеек. Проанализируйте полученный результат. Уберите стрелки соответствующей командой меню.
Если у Вас возникнут затруднения при выполнении этого упражнения, обратитесь к cправке Excel.
Задайте длину стороны a, равную 10. В ячейках с результатами·появится сообщение об ошибке #ЧИСЛО!. Дело в том, что стороны 10, 4, 5 не образуют треугольника. При вычислении площади под корнем получается отрицательное число.
Выделите ячейку G10 и выберите Формулы, Зависимости ячеек, Проверка наличия ошибок, Источник ошибки. Вы наглядно увидите за счет, каких влияющих ячеек получен неверный результат.
7. Сообщение об ошибочных данных. Будем вычислять отдельно подкоренное выражение p*(p–a)*(p–b)*(p–c_) и определять его знак. Если оно положительно, вычисляем S, R и r. Если же нет, то в ячейке B8 выведем текстовую строку Это не треугольник!, а в ячейках E10 и G10 выведем пустые строки.
Перетащите мышью содержимое B8 в B7. Отредактируйте B7, убрав КОРЕНЬ.
В ячейке останется формула:
=p*(p–a)*(p–b)*(p–c_).
Теперь имя S имеет ячейка B7. Выделите ячейку В8, войдите Диспетчер имен на вкладке Формулы и измените ссылку для S с $B$7 на $B$8.
В ячейку B8 разместите формулу
=ЕСЛИ(B7>0;КОРЕНЬ(B7);"Это не треугольник!").
В E10 разместим формулу =ЕСЛИ(B7>0;S/p;""). Аналогично измените формулу в G10 для R.
8. В 6-й и 7-й строках расположены результаты промежуточных вычислений, видеть которые пользователю таблицы ни к чему. Выделите целиком 6 и 7 строки и скройте их командой контекстного меню Скрыть (Таблица 2,п.13).
Если Вы захотите вернуть эти строки на экран, выделите 5-ю и 8-ю строки и выберите из контекстного меню Показать.
9. Защита листа. Чтобы предохранить таблицу от непреднамеренной порчи пользователем (вдруг он попытается задать радиус вписанной окружности и при этом уничтожит формулу), нужно защитить рабочий лист.
Выделите целиком лист (Таблица 2, п.3), нажмите Ctr+1, выберите вкладку Защита или Главная, Формат, Защитить лист. Изучите эти команды.
10. Разрешите пользователю вводить только положительные длины сторон треугольника. Для этого выделите блок ячеек B2:B4 и войдите на вкладке Данные, в группе Работа с данными, Проверка данных. В поле Тип данных: выберите Действительное, в поле Значение: - больше, в поле Минимум: - 0. Попробуйте ввести отрицательные (или текст) данные в диапазон B2:B4 и посмотрите реакцию Excel.
11. Выберите в меню кнопки Office, Печать, Предварительный просмотр. Изучите назначение кнопок в окне предварительного просмотра. Нажмите кнопку Закрыть окно предварительного просмотра.
Рабочий лист разбит пунктирными линиями на прямоугольники, соответствующие листам формата А4. Если печатаемая таблица не помещается целиком на один лист, необходимо войти в Разметка страницы, Параметры страницы, Размер, Другие размеры страниц и установить переключатель разместить не более чем на одной странице в ширину и высоту.
12. Подбор параметра. Например, мы хотим определить величину a при R=3 для этого выделите ячейку G10 на ленте выбрать Данные, вкладка Работа с данными, кнопка Анализ “что-если”, Подбор параметра… в поле Значение: введите 3 и в поле Изменяя значение ячейки: введите адрес ячейки B2, содержащей величину стороны a (если мы щелкнем мышью по этой ячейке, то в поле ввода окажется адрес $B$2). Нажмите на кнопку ОК. Посмотрите получившиеся результаты.
13. Для выявления ошибок в процессе вычислений используют три функции ЕОШ, ЕОШИБКА и ЕНД, с которыми можно познакомиться через встроенную справку MS Excel.
В ячейку С8 введите формулу
=ЕСЛИ(ЕОШ(корень(p*(p–a)*(p–b)*(p–c_)));“”;корень(p*(p–a)*(p–b)*(p–c_))), которая возвращает пустую строку, если вычисление площади привело к ошибке.
В ячейку Е9 введите формулу для вычисления радиуса вписанной окружности
= ЕСЛИ(ЕЧИСЛО(С8); C8/р; “”).
В ячейку G9 введите формулу для вычисления радиуса описанной окружности. Сравните результаты.
IV.РАБОТА С ДИАГРАММАМИ
В МS Ехсеl данные можно представлять в графическом виде. Для помещения диаграммы или графика на листе Excel нужно выделить таблицу с данными, перейти на вкладку ленты Вставка. В группе инструментов Диаграммы выбрать интересующий тип диаграммы, раскрыть соответствующую палитру щелчком мыши и выбрать нужную диаграмму (Рис.26).
Рис. 26. Выбор типа диаграммы.
Упр. 15. Построение линейного графика
Протабулируйте функцию Y=f(x) на отрезке [-3:3] с шагом ∆=0,25, т.е. составьте таблицу значений линейной функции у=2х+1. Постройте линейный график зависимости Y=f(x). График должен содержать легенду, заголовок, подписи осей и данных.
Технология работы:
1. Прежде чем строить прямую, необходимо составить таблицу данных Y=f(x). Для этого значения х и у следует представить в виде таблицы, где столбцами являются соответствующие показатели. Пусть в рассматриваемом примере первый столбец будет значениями х, а второй соответствующими показателями у. Для этого в ячейку А1 вводим слово Аргумент X, а в ячейку В1 — слово Функция Y.
Начнем с введения значений аргумента. В ячейку А2 введите первое значение аргумента — левая граница диапазона -3. В ячейку АЗ введите второе значение аргумента — -2,75.
Затем, выделите блок ячеек А2:АЗ и за маркер заполнения протяните до получения значения равного 3 (Таблица 2, пт. 7).
2. Далее введите значения функции. В ячейку В2 введите ее уравнение: = 2*А2 + 1. Затем автозаполнением скопируйте эту формулу в диапазон В2:В26.
Выделите диапазон ячеек A1:B26. Выберите тип диаграммы – Точечная, Точечная с гладкими кривыми.
Самостоятельно разберитесь с возможностями изменения содержания и форматирования легенды, заголовков, осей и подписей данных и т.д.
Упр. 16. Графическое представление данных
Представьте данные Упр. 9 в наглядном графическом виде.
Для этого скопируйте лист Упр_9, вставьте его в конец и переименуйте его в Упр_16.
В рассматриваемой задаче для отображения данных используйте следующие типы диаграмм:
· Гистограмм (плоских и объёмных), позволяющих сравнивать значения планового и фактического выпуска по месяцам;
· Круговых диаграмм (плоских и объемных), которые можно использовать для отображения значений фактически выпущенной продукции в каждом месяце как доли в годовом выпуске;
· Графиков, отображающих процесс изменения значений фактического выпуска по месяцам;
· Смешанной диаграммы, позволяющей на одной диаграмме отобразить плановые и фактические значения выпуска по месяцам в виде гистограммы, а выполнение плана по месяцам - в виде графика.
Технология работы:
1. Постройте гистограмму. Для лучшего представления данных на диаграмме в исходных данных не должно быть объединенных ячеек. Выделите диапазон ячеек С4:Е16, содержащий два ряда числовых данных: план выпуска и фактический выпуск по месяцам, а также ряды с названиями строк и заголовками столбцов. Названия в строках будут использоваться в качестве обозначений на оси X (категорий), а заголовки столбцов - в качестве легенды.
· Выберите тип диаграммы – Гистограмма, Гистограммас группировкой.
· Для ввода названия диаграммы необходимо выделить область диаграммы при этом измениться вид ленты (Работа с диаграммами, Конструктор) и выбратьгруппу Макеты диаграмм, Макет1. Для изменения названия диаграммы, необходимо щелкнуть курсором мыши на название и ввести новое при помощи клавиатуры. Так же для изменения и редактирования вида диаграммы (добавления названий осей, названия диаграммы, легенды и т.д.) необходимо воспользоваться инструментами вкладки Макет.
· Дайте Название диаграммы - Показатели производства, в поле Ось X (Категорий): — Месяцы, в поле Ось У (Значений): — Количество; укажите место размещения легенды – снизу; установите Подписи данных – у вершины внутри.
· Выполните на своё усмотрение редактирование диаграммы для лучшего восприятия данных.
2. Постройте круговую диаграмму.
· Выделите блок ячеек, состоящий из двух несмежных столбцов С4:С16 и Е4:Е16, содержащих соответственно названия месяцев (для оформления диаграммы) и фактический выпуск по месяцам (собственно данные). Несмежные столбцы выделяются при нажатой клавише Ctrl.
· Выберите тип диаграммы- Вставка, Круговая, Разрезанная круговая.
· Дайте диаграмме название. Добавьте легенду и выберите место размещения легенды справа.
· Около каждого сектора диаграммы необходимо установить значения в долях в процентах или категория и доля (тогда будут выводиться названия месяцев и доля в процентах). Работа с диаграммами, вкладка Конструктор, группа Макеты диаграмм, Макет6 или Макет1.
· Переместите диаграмму в подходящее место листа. Если необходимо, то измените ее размеры и выполните редактирование отдельных элементов диаграммы
· Если секторы круга на диаграмме оказались маленького размера, то увеличьте размер области построения диаграммы
· Отформатируйте вид круговой диаграммы, заголовок диаграммы, легенду и подписи данных (значения долей выводятся в процентном формате с двумя цифрами дробной части)
· Сравните полученный результат с изображением на Рис. 27 а) или б).
а). 
б). 
Рис. 27. Диаграмма Круговая плоская
3. Постройте график.
· Выделите диапазон ячеек, состоящий из двух несмежных столбцов С4:С16 и Е4:Е16, содержащих названия месяцев и фактический выпуск по месяцам (несмежные столбцы выделите при нажатой клавише Ctrl). Диаграмма должна иметь вид, как на Рис. 28.
Рис. 28. Диаграмма типа График
4. Построение смешанной диаграммы.
· Выделите диапазон ячеек С4:F16, содержащий заголовки строк, столбцов и две группы разнотипных рядов данных: одна группа – значения планового и фактического выпуска, другая - процент выполнения плана по месяцам.
· Выберите вкладку Вставка, Гистограмма, Гистограммас группировкой.
· Для выделения ряда “процент выполнения плана” необходимо перейти на вкладку Макет в группе Текущий фрагмент в Области диаграммы выбрать Ряд “процент выполнения плана” см. рис. 29.
Рис. 29. Выбор нужного ряда диаграммы
· Выбрать Формат выделенного фрагмента и установить переключатель в позиции по вспомогательной оси.
· Далее для выбранного ряда меняем тип диаграммы – график (Вкладка Вставка, График, График).
· Вид диаграммы со всеми настройками показан на рис. 30.
Рис. 30. Смешанная диаграмма
V. РАБОТА С БАЗОЙ ДАННЫХ В MS EXCEL
Одной из типичных задач, выполняемых с помощью электронных таблиц, является ведение списков. Список (база данных) – это таблица, в которой столбцы имеют заголовки (поля) и строки (записи).
Обратите внимание на правила создания списка:
1. Каждый столбец должен содержать информацию одного типа
2. Одна или две верхних строки списка должны содержать заголовки, описывающие содержимое расположенного ниже столбца
3. В заголовках таблицы недопустимо объединение ячеек
4. Не включать в список пустых строк и столбцов
5. Отводить для списка отдельный лист
6. Не размещать данные слева или справа от списка
V.1. Сортировка и фильтрация данных
Сортировка данных используется для упорядочения информации. Для сортировки заданного диапазона можно воспользоваться кнопками ленты вкладки Данные (Рис. 31).
Рис. 31. Сортировка и фильтрация.
Сортировка от минимального к максимальному, Сортировка от максимальному к минимальному. Внимание надо обратить только на один момент: если в выделенный для сортировки диапазон попадает одновременно и формулы, и ячейки, на которые эти формулы ссылаются, результат может оказаться непредсказуемым.
Фильтрация (выборка) данных позволяет отобразить в таблице только те строки, содержимое ячеек которых отвечает заданному условию (или нескольким условиям). Эта операция может выполняться с помощью: Автофильтра и Расширенного фильтра.
Автофильтр применяется для простых условий отбора. Для включения автофильтра необходимо на ленте инструментов нажать на кнопку 
во вкладке Данные.
Расширенный фильтр позволяет осуществлять более сложную выборку данных электронной таблицы с заданием одновременно нескольких условий (Вкладка Данные, группа Сортировка и фильтр, Дополнительно см. рис.31). Расширенные условия отбора вводятся в отдельный диапазон условий листа над диапазоном ячеек или таблицей, которые требуется отфильтровать.
Упр. 17. Вычисление плотности населения стран мира
Данное задание предназначено для ознакомления с операциями сортировки и фильтрации.
Технология работы:
1. Откройте ранее созданную в Упр. 13 книгу Работа 1 в папке Excel. Создайте 8 рабочих листов и переименуйте их в Задание_ 1, Задание_ 2 и т.д. Выделите одновременно все 8-мь листов и создайте приведенную на Рис. 32 таблицу.
Вычислите сумму по столбцам Площадь и Население. Для каждой страны вычислите: Плотность населения и Долю в % от всего населения Земли.
2. На листе Задание_1 скройтедве последние строки таблицы с помощью контекстного меню, выделите всю таблицу (строки с 1 по 15) и скопируйте их ниже на этом же листееще 4 раза.
Рис. 32. Вычисление плотности населения стран мира
На листе Задание_1 во втором экземпляретаблицы выполните сортировку по данным столбца Плотность населения(по убыванию).
В третьем экземпляре таблицы расположите страны по алфавиту.
В четвертом экземпляре таблицы проведите сортировку по данным последнего столбца (%).
В последнем экземпляре таблицы расположите страны по численности населения, а затем в этом же экземпляре отсортируйте данные по первому столбцу (по номерам).
Должен получиться исходный вариант таблицы. Проанализируйте полученные результаты.
3. Произведите фильтрацию записей таблицы на листах со 2 -5 (Задание_2 - Задание_5) согласно следующим критериям:
• На листе 2 выберите страны с площадью более 5 000 тыс. км2.
• На листе 3 - страны с населением меньше 150 млн. чел.
• На листе 4 - выберите страны с плотностью населения от 100 до 300 чел./км2.
• На листе 5 - страны, население которых составляет более 2% от всего населения Земли.
На листе 2 восстановите исходный вариант таблицы и отмените режим фильтрации.
4. На листе Задание_6 раскройте список автофильтра для столбца к которому хотите применить Пользовательский автофильтр и выберите пункт Числовые фильтры, Настраиваемый фильтр см. рис.33.
Рис. 33. Окно Пользовательский автофильтр
В диалоговом окне Пользовательский автофильтр введите два условия отбора и используйте любой из операторов сравнения Excel. Например: выведите список стран отвечающих условию фильтра площадь страны - меньше 1000 тыс.км2 и население меньше 100 000 тыс. чел.
5. Выделите на листе Задание_7 строку 1 и вставьте перед ней 3 новые строки.
На этом же листе (под таблицей) создайте рамку для текстового поля, используя ленту вкладки Вставка, группа Иллюстрации, Фигуры, Надпись 
.
Найдите в справочной системе Excel раздел Фильтры, Фильтрация списка с помощью расширенного фильтра и скопируйте содержимое этого раздела в текстовое поле.
Отредактируйте размер текстового поля и текст справки.
Ознакомьтесь с технологией выборки данных с использованием расширенного фильтра и на основе полученных знаний на листе Задание_6выберите из таблицы страны, начинающиеся с буквы "К" и имеющие численность населения более 1 млрд чел.Сохраните работу.
5. На листе Задание_8 выделите в таблице столбцы с названиями стран и численностью населения. Постройте круговую диаграмму по данным выделенных столбцов.
Постройте различные гистограммы по данным остальных столбцов.
V.2. Создание сводных таблиц и сводных диаграмм
Подробный анализ данных из таблицы базы данных требует возможности получения сводной информации по записям базы данных. Фактически, речь идет о совмещении средств фильтрации и итоговых вычислений. Результаты такого анализа могут быть представлены в виде сводной таблицы или сводной диаграммы. При создании сводной диаграммы автоматически создается и сводная таблица, содержащая данные, на основе которых строится диаграмма.
Для построения сводной таблицы необходимо: выделить диапазон ячеек таблицы; далее Вставка, Сводная таблица при этом откроется диалоговое окно Создание сводной таблицы (см. Рис. 34).
Рис. 34. Создание сводной таблицы.
Укажите местоположение сводной таблицы - переключатель на новый лист. Нажмите Ок. Появится на новом листе диалоговое окно Список полей сводной таблицы (см. Рис. 35а)и макет сводной таблицы на листе Excel (см. Рис.35б). Макет сводной таблицы состоит из четырех областей: подписи Страниц – Фильтр отчёта, подписи Строк – Название строк, подписи Столбцов - Название столбцов и подписи Данных - Значения (Рис. 35а и б).
а). 
б). 
Рис. 35. Макет сводной таблицы.
Каждой области сводной таблицы должны соответствовать одно или несколько полей базы данных. Исключение делается только для поля Страница, которое можно оставить пустым. При заполнении сводной таблицы, данные из соответствующих полей берутся автоматически. Для заполнения этих полей необходимо методом перетаскивания из диалогового окна Список полей свободной таблицы поместить в макет на Excel лист в нужное поле или в область полей в этом же окне. В сводной таблице можно использовать только те поля, которые необходимы. Незадействованные поля в отборе и вычислениях не участвуют.
Область Страница играет роль внешнего фильтра. Данные в остальной части таблицы относятся к значению, выбранному в этом поле. Область Строка содержит значения, определяющие содержимое строки таблицы, и задает второй уровень сортировки. Аналогичным образом, область Столбец определяет содержимое столбцов. В отличие от области Страница, в сводную таблицу помещаются данные для всех возможных значений полей, указанных в этих областях.
Область Данные определяет собственно содержимое таблицы. В отличие от всех остальных областей, к данным, попадающим в ячейку таблицы, применяется функция для итоговых вычислений (по умолчанию — суммирование). Если необходимо изменить эту функцию, надо в диалоговом окне Список полей свободной таблицы в области полей Значения из раскрывающего списка выбрать команду Параметры полей значений и в открывшемся окне нужную операцию из списка Операция или Дополнительные вычисления. После выбора и настройки данных следует щелкнуть на кнопке ОК.
Для настройки внешнего вида сводной таблицы надо: Работа со сводными таблицами, вкладка Параметры, щелкнуть на Параметры, откроется диалоговое окно Параметры сводной таблицы. Здесь можно задать имя листа сводной таблицы, включить режим суммирования данных по строкам и столбцам, разрешить доступ к информации, использованной при вычислении, выполните другие настройки. По их завершении требуется щелкнуть на кнопке ОК.
Если требуется изменить вид сводной таблицы, это можно сделать с помощью кнопок, помещенных в таблице в соответствующих областях. При щелчке на соответствующей раскрывающей кнопке появляется небольшое окно, в котором можно отключить некоторые из отображаемых показателей. Для области Страница справа отображается раскрывающийся список, позволяющий выбрать разные условия фильтрации.
Сводная таблица остается динамически связанной с данными, на основании которых она была построена. Это, в частности, означает, что для любого значения, приведенного в сводной таблице, можно выяснить, как оно было получено. Для этого надо дважды щелкнуть на соответствующей ячейке таблицы. При этом автоматически создается новый рабочий лист, содержащий выборку записей, использованных при вычислении данного значения.
Динамическая связь с исходными данными проявляется и в том, что при изменении данных не требуется заново формировать сводную таблицу. Достаточно щелкнуть в пределах таблицы правой кнопкой мыши и выбрать в контекстном меню пункт Обновить.
Впрочем, это не относится к часто встречающейся ситуации, когда записи базы данных добавляются или удаляются. В этом случае необходимо изменить диапазон ячеек для этого нужно выделить любую ячейку уже построенной сводной таблицы, Работа со сводными таблицами, вкладка Параметры, щелкнуть на Изменить источник данных После щелчка появиться диалоговое окно Изменить источник данныхсводной таблицы в котором необходимо изменить диапазон ячеек и нажать кнопку Готово. Данные сводной таблицы будут обновлены с учетом изменения числа записей.
Сводная диаграмма представляет собой диаграмму, построенную на основе сводной таблицы. При создании сводной диаграммы соответствующая ей сводная таблица создается автоматически, даже если явного запроса на ее создание не было. Для создания сводной диаграммы по уже существующей сводной таблице необходимо: Работа со сводными таблицами, вкладка Параметры, Сводная диаграмма, откроется диалоговое окно Вставка диаграммы в котором выбираем тип и вид диаграммы. Далее щелкните по кнопке ОК.
Упр. 18. Создание сводных таблиц и диаграмм для таблицы
Показатели производства
Создайте сводную таблицу и сводные диаграммы для задачи, на листе Упр_18, описанной в Упр. 9. Преобразуйте таблицу в список.
Технология работы:
1. Выделите необходимый диапазон ячеек и создайте сводную таблицу на другом листе и переименуйте его на “Сводная таблица”.
2. Перетащите кнопки в соответствующие области макета сводной таблицы: кнопку Месяц — в область Строка, кнопку План выпуска — в область Столбец, кнопку Фактически выпущено — в область Данные - Значения.
3. Область Значения в диалоговом окне Список полей сводной таблицы будет иметь вид Сумма по полю Фактически выпущено.
4. Для присвоения имени сводной таблицы выбираем Работа со сводными таблицами, вкладка Параметры, Сводная таблица и в поле Имя: вводим текст Показатели производства.
5. На полученной сводной таблице раскрывающие кнопки рядом с именами полей таблицы позволяют выполнить сортировку по соответствующему полю. Выберите конкретный месяц в раскрывающемся списке Месяц, чтобы увидеть данные, относящиеся к этому месяцу. Посмотрите на новый вид сводной таблицы.
6. Постройте сводные диаграммы на новых листах и переименуйте их в соответствии с названием упражнения и диаграммы (Упр_18(диаграмма 1), Упр_18(диаграмма 2) и т. д.)
Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:
|
|
