Начальные сведения Excel 2007

Введение

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

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

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

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

Начальные сведения Excel 2007

Структура Экрана

После запуска программы Excel на экране появится окно. Окно состоит из следующих частей: Строка заголовка, лента Меню, Строка ввода, Строка состояния.

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

 

 

Excel имеет контекстное меню, содержащее часто употребляемые команды для работы с ячейками, диаграммами или другими объектами, активизируется нажатием правой кнопки мыши или одновременным нажатием клавиш Shift+F10. Для выхода из контекстного меню нужно щёлкнуть кнопкой мыши вне его или нажать клавишу Esc;

Документ, созданный программой Excel, называется Рабочая книга.

Рабочая Книга состоит из листов. Количество листов зависит от настройки параметров Excel (по умолчанию 3). Пользователь может добавить листы в книгу. Если создаётся новая книга, то ей автоматически присваивается имя и порядковый номер, например, Книга 1.

Рабочий лист в Excel 2007 состоит из 13384 столбцов и 1048576 строк.

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

Значительная часть работы в Excel приходится на ввод данных, их редактирование и обработку.

Некоторые операции с листами

· Добавление нового листа – команда Главная/ Ячейки/ Вставить/ Вставить лист. Или щёлкнуть по ярлычку листа правой кнопкой мыши и в раскрывшемся списке выбрать Вставить.

· Удаление текущего листа – команда Главная/ Ячейки/ Удалить/ Удалить лист. Или щёлкнуть по ярлычку листа правой кнопкой мыши и в раскрывшемся списке выбрать Удалить.

· Переименование листа – щёлкнуть по ярлычку листа правой кнопкой мыши, в раскрывшемся списке выбрать команду Переименовать и ввести имя.

· Перемещение листа (либо копирование листа) – щёлкнуть по ярлычку листа правой кнопкой мыши и в раскрывшемся списке выбрать команду Переместить/Скопировать лист. Если установить флажок Создавать копию, тобудет создана копия листа.

Сохранение рабочей Книги

Сохранение рабочей книги на диске выполняется аналогично тому, как это действие выполнялось в программе Word.

Типы данных

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

Текст - последовательность символов, при вводе автоматически выравниваются по левому краю ячейки;

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

Под формулой понимают выражение, состоящее из числовых величин, адресов ячеек, функций и связывающих их арифметических операций. Формула всегда начинается со знака = (знак равенства) или +/- (знак плюс или минус). После ввода формулы в активной ячейке виден результат, а сама формула отражается в строке формул. Например, в ячейке В7 введена формула =А7*20. Эту формулу увидим в строке формул, при этом если в ячейке А7 содержится число 5, то в ячейке В7, увидим число 100.

Примеры результатов вычислений:

Формула введена в ячейку В7	Формула отображается в строке формул	Содержимое А7	Содержимое В5	Результат вычислений в ячейке В7
=7+1	=7+1
=25*В5	=25*В5
=А7/В5	=А7/В5
=А7*20	=А7*20

 

Excel вычисляет формулу каждый раз, когда изменяется содержимое ячеек таблицы, на которые в формуле имеются ссылки.

Для постоянного отображения формул в ячейках необходимо выполнить настройку параметров нажать кнопку Office, в открывшемся окне нажать кнопку Параметры Excel, в окне Параметры Excel выбрать параметр Дополнительно, в группе Показать параметры для следующего листа включить параметр Показывать формулы, а не их значения. Это нужно сделать для выявления ошибок в формулах.

Редактирование ячеек

Редактировать содержимое ячеек можно несколькими способами:

· ячейку сделать активной, набрать в нее новую информацию, не удаляя ошибочную;

· дважды щёлкнуть в активной ячейке для перехода в режим редактирования;

· удаление ошибочной информации в активной ячейке осуществляется нажатием клавиши Delete.

Сортировка данных в таблице

Строки в таблице можно отсортировать (упорядочить) по алфавиту, по значениям чисел и дат. Чтобы отсортировать таблицу по одному показателю, надо активизировать заголовок этого показателя или любую ячейку этого столбца. Затем щёлкнуть ПК мыши и выбрать: Сортировка от А до Я (по возрастанию) или Сортировка от Я до А (по убыванию).

Последовательность сортировки определяется содержанием и типом данных в столбцах.

Создание именованных блоков

· При работе с базами данных необходимо использовать именованные блоки.

· В качестве именованного блока могут выступать справочная таблица или её части (отдельные блоки, ячейки).

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

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

Правила присвоения имён блокам и ячейкам:

· имя должно начинаться с буквы;

· в имени блока могут использоваться только буквы, цифры, обратная косая черта и символ подчёркивания (_) вместо пробела;

· нельзя использовать имена, которые могут быть поняты, как ссылки на ячейки;

· в качестве имён могут использоваться одиночные буквы за исключением R и C.

Практическое задание

Часть I. Подготовка данных.

Назвать первый лист рабочей книги Справочники.

В ячейках А1:С9 листа Справочник создать и оформить таблицу.

Присвоение имён блокам

Присвоить имена блокам ячеек: Код_зак (блок ячеек А3:А9),

Наим_зак (блок ячеек В3:В9), Адрес (блок ячеек С3:С9) и

Заказчики (блок ячеек А2:С9).

Создать приведённую ниже таблицу, используя необходимые элементы форматирования.

Выделить блок ячеек А3:А9, которому задать имя Код_зак. Для этого выполнить: в меню Формулы, раздел Определённые имена открыть список: Присвоить имя и выбрать Присвоить имя.

В открывшемся окне Создание имени ввести имя Код_зак, нажать ОК.

Аналогично можно задать остальные имена Наим_зак, Адрес, Заказчики.

На этом же листе в ячейку A12 поместим значение =10%

Ячейке, содержащей значение 10%, присвоить имя Пеня.

Можно воспользоваться и более простым способом задания имён:

прямо в это окно можно ввести имя для выделенного блока или ячейки.

Проверка:

1. Открыть Диспетчер имён в меню Формулы, раздел Определённые имена – (по мере создания появляются имена Код_зак или Наим_зак, Адрес, Заказчики).

2. Для перехода к именованному диапазону выбрать его имя в перечне.

3. Поставить курсор в свободную ячейку. Нажать клавишу F3. Появляется окно Вставка имени с перечнем созданных именованных блоков и ячеек.

Поле со списком

Для создания списка выполнить: В меню Данные, в разделе Работа с данными кликнуть Проверка данных.

В открывшемся окне Проверка вводимых значений в списке Тип данных выбрать Список. Поставить курсор в Источник, нажать клавишу F3, в появившемся списке щёлкнуть по Код_зак, нажать ОК.

В результате в выделенной ячейке появится знакстрелка (признак поля со списком). Знак стрелка позволяет раскрыть список и выбрать из него значение. Эту настройку копировать вниз до конца столбца.

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

Значения для поля со списком можно также задать непосредственно,введя в окно Источник значения списка, разделяя их знаком «;» (точка с запятой). Такой способ задания значений списка используется в том случае, когда значений в списке мало.

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

Применение функции ПРОСМОТР

Вставить в таблицу Ведомость значения поля Наименование заказчика соответствующие коду заказчика из справочной таблицы Заказчики с помощью функции ПРОСМОТР.

Наименование заказчика находится в прямой зависимости от Кода заказчика, введённого из первичного документа. Последовательность настройки функции ПРОСМОТР:

· Установить курсор в ячейку В6 и вызвать функцию ПРОСМОТР.

· В окне «Мастер функций шаг 1 из 2» нажать ОК.

· В следующем окне «Аргументы функции» установить все параметры:

 

- В окно «Искомое_значение» поместить А6 (щелчком по ячейке А6 таблицы Ведомость ).

- В окно «Просматриваемый_вектор» с помощью функциональной клавиши F3 вызвать имя блока ячеек Код_зак.

- В окно «Вектор_результатов» с помощью функциональной клавиши F3 вызвать имя блока ячеек Наим_зак.

- Нажать ОК в главном окне функции ПРОСМОТР.

- Скопировать полученную формулу в ячейки B7:B16 таблицы Ведомость. В результате столбец Наименование заказчика будет заполнен. Аналогично заполнить столбец Адрес (только в поле Вектор_результатов вызвать имя блока Адрес)

 

Применение функции ВПР

Вставить в таблицу Ведомость значения поля Адрес, соответствующиекоду заказчика из справочной таблицы Заказчики,с помощью функции ВПР (вертикальный просмотр).

Для этого выполнить: действия:

· Установить курсор в ячейку С6 и вызвать функцию ВПР.

· В окне «Искомое_значение» поместить А6 (щелчком по ячейке А6 таблицы Ведомость).

· Для заполнения окна «Таблица» нажать функциональную клавишу F3 и выбрать из списка таблицу Заказчики.

· Для заполнения окна «Номер_столбца» ввести цифру 2, (номер столбца в справочной таб. Заказчики).

· При заполнении окна Интервальный просмотр надо учитывать диапазон просмотра. Если просматривать надо последовательно каждое значение, то следует ввести ноль. Нажать ОК в главном окне функции ВПР. Еслив справочной таблице можноискать ближайшее к искомому значение, ввести 1. Значению по умолчанию соответствует 0.

· Окно функции ВПР примет вид:

· Скопировать полученную формулу в ячейки В7:В16. В результате столбец Адрес будет заполнен.

Создание списка для поля Период

· Период создать в виде списка значений: 1 кв;2кв;3кв. Для этого установить курсор в ячейку D6 столбца Период. В пункте меню Данные в разделе Работа с данными кликнуть Проверка данных. В диалоговом окне Проверка вводимых значений в окне «Тип данных» выбрать Список.

· В окне «Источник». Так как список состоит всего из 3-х значений, то их можно ввести прямо в окне Источник, разделяя знаком точка с запятой«;». Нажать ОК.

· Скопировать настройку и заполнить данными поле Период согласно таблице.

Заполнить данными поля Сумма к выплате и Оплачено в соответствии с исходной таблицей.

Расчёт полей Разница и Долг.

Рассчитать поле Разница = Сумма к выплате - Оплачено. Активизировать ячейку G6. Ввести знак = (равно), затем щёлкнуть ячейку Е6, затем знак – (минус), щёлкнуть ячейку F6. В ячейке получится выражение = Е6 - F6.. Нажать Enter. Затем протащить маркер по всем ячейкам столбца, в которых надо получить результат, это ячейки G6:G16.

Рассчитать значения поля Долг, используя логическую функцию ЕСЛИ. При значении поля Разница больше нуля Долг равен Разнице, в противном случае Долг равен нулю.

Логические функции

Логические функции ЕСЛИ, НЕ, И, ИЛИ используют логические выражения для определения истинности заданного условия. Например, каждая из приведённых формул является логическим выражением:

= А1>A2 = СРЗНАЧ(В1:В6) =СУММ(6;7;8) = С2=”Среднее’

Любое логическое выражение должно содержать, по крайней мере, один оператор сравнения, который определяет отношение между элементами логического выражения. Например, в логическом выражении А1>А2 оператор больше (>). В качестве операторов сравнения могут быть: =, >, <, >=, <=, <> (не равно). Результатом логического выражения является логическое значение ИСТИНА или логическое значение ЛОЖЬ.

Защита листа

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

Защита Книги (команда рядом с командой защиты листа) – ограничения доступа к книге, то есть установка запрета на создание новых листов, предоставление доступа определённым пользователям и многое другое.

Вставка новых листов

Вставить новый лист, расположив его за листом Ведомость, назвать новый лист Рабочая ведомость. Скопировать с листа Ведомость основную таблицу А5: j16 с листа Ведомость на лист Рабочая ведомость в ячейку А1.

Сравнить значения полей таблицы на листе Ведомость стаблицей на листе Рабочая ведомость .

Дать имя Раб_вед блоку ячеек А1: j12 на листе Рабочая ведомость.

Часть II

Сортировка таблицы

На листе Сортировка выполнить сортировку таблицы последовательно по трём полям: полю Адрес, затем по полю Наименование заказчика, затем по полю Период.

Поставить курсор в любую ячейку таблицы.

В меню Главная, в разделе Редактирование кликнуть Сортировка и Фильтр, выбрать Настраиваемая сортировка.

В окне «Сортировка»:

· в «Сортировать по» открыть список и выбрать Адрес,

· в «Сортировка» - выбрать Значения,

· в «Порядок» - выбрать От А до Я.

Щёлкнуть кнопку Добавить уровень (в левом верхнем углу). В появившейся строке:

· «Сортировать по» открыть список и выбрать Наименование заказчика,

· «Сортировка» - выбрать Значения,

· в «Порядок» - выбрать От А до Я.

Добавить третий уровень и выбрать: Период, Значения, От А до Я.

Получим:

Фильтрация данных

Перейти на лист Автофильтр. Отфильтровать данные в поле Период по значению 1 кв и 2 кв, в поле Долг вывести значения, не равныенулю.

Поставить курсор в любую ячейку таблицы.

В меню Главная, в разделе Редактирование кликнуть Сортировка и Фильтр, выбрать Фильтр. У каждого столбца таблицы появится стрелка. Раскроем список в столбце Период и выберем Текстовые фильтры, дальше Настраиваемые фильтры. Появится окно, в котором выполним установки:

Далее требуется из выбранных строк отобрать те, в которых Долг не равеннулю. В столбце Долг выберем из Числовые фильтры, дальше Настраиваемые фильтры. В окне Пользовательский автофильтр сделаем установки:

 

После этого получим:

Создадим новый лист Фильтр.

Из таблицы Рабочая_ведомость с помощью расширенного фильтра отобрать записи, у которых Период – 1 кв и Долг+Пеня>0. Результат получить в виде таблицы на листе Фильтр:

Код заказчика

Наименование заказчика

Долг+Пеня

Шапку таблицы результата создать копированием из таблицы Рабочая_ведомость на листе Фильтр, начиная с ячейки А5.

На листе Фильтр создадим диапазон условий в указанных ниже ячейках. Названия полей и значения периодов обязательно копировать с листа Рабочая_ведомость. Если выделяемые блоки несмежные, то при выделении применить клавишу Ctrl.

Присвоим имя этому диапазону Условие_долг.

Выполним команду Данные/Фильтр/Дополнительно.

Появится диалоговое окно:

Исходный диапазон и диапазон условий задать с помощью клавиши F3.

Поместить полученные результаты на листе Фильтр в ячейку А5 ( выделить ячейки А5:С5).

Получим результат:

Формирование итогов

Перейти на лист Итоги. Получить итоги (операция сумма) по полям Сумма к выплате, Оплачено и Долг для каждого периода. Для этого предварительно отсортировать таблицу по полю Период (в противном случае итоговые значения будут определены для каждой строки). Затем в меню Данные, в разделе Структура команда Промежуточные итоги. Появится окно Промежуточные итоги. Сделать в окне указанные установки. Нажать ОК.

 

При вычислении итогов таблица структурируется. Получим:

 

Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке 2 (второго уровня структуры), вследствие чего данные третьего уровня (исходные значения) будут скрыты.

Получим:

 

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

Построение гистограммы.

Построим гистограмму, изобразив на ней по периодам Сумму к выплате, Оплачено и Долг. Порядок созданиядиаграммы как в предыдущем задании. Однако здесь удобно скрыть столбец Разница на листе Итоги. Для этого активизируем любую ячейку этого столбца. Выполним команду Формат/Столбец/Скрыть. Выделим необходимые столбцы:

 

Построим гистограмму:

 

Чтобы получить смешанную диаграмму и представить Долг в виде линейного графика, надо щёлкнуть по одному из столбцов Долга.

Затем выполнить команду правой кнопкой мыши Изменить тип диаграммы для ряда. Выбрать закладку График, а после этого из списка выбрать График. Диаграмма примет такой вид.

Для линейного графика удобно создать дополнительную ось Y-ов справа на графике. Это тем более необходимо, если значения для линейного графика несоизмеримы со значениями других столбцов гистограммы. Щёлкнуть по линейному графику и выполнить команду Формат ряда данных.

В открывшемся окне: открыть закладку Параметры ряда и установить флажок по вспомогательной оси. После этого на графике появится дополнительная ось Y – (справа). Нажать кнопку ОК.

На столбцы можно нанести соответствующие им значения. Для этого щёлкнуть по одному из столбцов правой кнопки мыши и выбрать Добавить подписи данных.

Фильтрация (выборка) данных

Перейти на лист Автофильтр. Отфильтровать данные в поле Период по значению 1 кв и 2 кв, в поле Долг вывести значения, не равныенулю.

Выполнение. Сделать активной любую ячейку таблицы листа Автофильтр. Выполнить команду Данные /Фильтр/ Сортировка и фильтр У каждого столбца таблицы появится стрелка. Раскроем список в заголовке столбца Период и выберем Текстовые фильтры, затем равно. Появится окно Пользовательский автофильтр, в котором выполним установки:

Далее требуется из выбранных строк отобрать те, в которых Долг не равеннулю.

В заголовке столбца Долг выберем из списка Числовые фильтры, затем Настраиваемый фильтр. Откроется окно Пользовательский автофильтр, в котором сделаем установки:

После этого получим:

 

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

Команда Расширенный фильтр (дополнительный), в отличие от команды Фильтр, требует задания условий отбора строк в отдельном диапазоне рабочего листа или на другом листе. Диапазон условий включает в себя заголовки столбцов условий и строки условий. Заголовки столбцов в диапазоне условий должны точно совпадать с заголовками столбцов в исходной таблице. Поэтому заголовки столбцов для диапазона условий лучше копировать из таблицы. В диапазон условий включаются заголовки только тех столбцов, которые используются в условиях отбора. Если к одной и той же таблице надо применить несколько диапазонов условий, то диапазонам условий (как именованным блокам) удобно присвоить имена. Эти имена затем можно использовать вместо ссылок на диапазон условий. Примеры диапазонов условий (или критериев отбора):

Сумма к выплате	Адрес
>10000
	Пермь

Если условия расположены в разных строках, то это соответствует логическому оператору ИЛИ. Если Сумма к выплате больше 100000, а Адрес – любой (первая строка условия). ИЛИ если Адрес - Пермь, а Сумма к выплате – любая, то из списка будут отобраны строки, удовлетворяющие одному из условий.

Другой пример диапазона условий (или критерия отбора):

Сумма к выплате	Адрес
>10000	Пермь

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

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

Создадим новый лист Фильтр.

Пример 1. Из таблицы на листе Рабочая_ведомость с помощью расширенного фильтра отобрать записи, у которых Период – 1 кв и Долг+Пеня >0. Результат нужно получить в новой таблице на листе Фильтр.

На листе Фильтр для выводарезультата фильтрации создадим шапку таблицы копированием заголовков из таблицы Рабочая_ведомость и расположив, начиная с ячейки А5:

Код заказчика

Наименование заказчика

Долг+Пеня

На листе Фильтр создадим диапазон условий в верхней части листа Фильтр в ячейках А1:В2. Названия полей и значения периодов обязательно копировать с листа Рабочая_ведомость. Если выделяемые блоки несмежные, то при выделении применить клавишу Ctrl.

Присвоим имя этому диапазону условий Условие1.

Выполним команду: Данные/Сортировка и Фильтр/ Дополнительно.

Появится диалоговое окно:

Исходный диапазон и диапазон условий вставьте с помощью клавиши F3.

Установить флажок скопировать результат в другое место. Поместить полученные результаты на листе Фильтр в диапазон А5:С5 (выделить ячейки А5:С5). Получим результат:

Пример 2. Из таблицы на листе Рабочая_ведомость с помощью расширенного фильтра отобрать строки с адресом Омск за 3 кв с суммой к выплате больше 5000 и с адресом Пермь за 1 кв с любой суммой к выплате. На листе Фильтр создадим диапазон условий в верхней части листа в ячейках D1:F3.

Присвоим имя этому диапазону условий Условие_2.

Названия полей и значения периодов обязательно копировать с листа Рабочая ведомость. Затем выполнить команду Данные/Сортировка и Фильтр/Дополнительно.

В диалоговом окне сделать следующие установки:

Получим результат:

Пример 3. Выбрать сведения о заказчиках с кодами - К-155, К-347 и К-948, долг которых превышает 5000.

На листе Фильтр в ячейках H1:I4 создадим диапазон условий с именем Условие3.

Названия полей обязательно копировать с листа Рабочая_ведомость.

После выполнения команды Данные/ Сортировка и Фильтр/ Дополнительно в диалоговом окне сделать следующие установки:

Получим результат:

Вычисляемые условия

Диапазон условий может содержать вычисляемые критерии. Правила создания диапазона вычисляемого условия:

· Заголовок столбца вычисляемого критерия не должен совпадать с заголовками столбцов таблицы или не заполняется вовсе.

· В ячейку, где формируется критерий, вводится знак «=»(равно).

· Затем вводится формула, которая вычисляет логическую константу (ЛОЖЬ или ИСТИНА).

Пример 4. Из таблицы на листе Рабочая ведомость отобрать строки, в которых значения Оплачено больше среднего значения по этому столбцу. Результат получить на листе Фильтр в новой таблице:

 

· На листе Фильтр создадим «шапку » новой таблицы копированием с листа Рабочая ведомость.

· Для удобства создания вычисляемого условия расположим на экране два окна: одно – лист Рабочая ведомость, другое – лист Фильтр. Для этого выполним команду Вид/ Окно/Новое окно. Затем команду Вид/ Окно/Упорядочить всё. Установим флажок слева направо. На экране появятся два окна, в первом из которых расположим лист Рабочая ведомость, а во втором – лист Фильтр. Благодаря этому удобно создавать формулу для критерия отбора на листе Фильтр.

· Сделаем активной ячейку E22 листа Фильтр, создадим в ней выражение:

· Введем знак = (равно), щёлкнем по ячейке F2 на листе Рабочая ведомость ( F2 - первая ячейка столбца Оплачено ).

· Введем знак >(больше).

· Введем функцию СРЗНАЧ с помощью мастера функций.

· В окне аргументов этой функции поместим диапазон ячеек F2:F12 (выделим его на листе Рабочая ведомость). Так как диапазон, для которого находим СРЗНАЧ, не меняется, то адреса диапазона должны быть абсолютными, то есть $F$2:$F$12. Знак $ можно установить с помощью функциональной клавиши F4. В окне функции СРЗНАЧ нажать О К.

Для проверки выполнения условия со средним значениемсравнивается значение каждой ячейки столбца F. Поэтому в левой части неравенства адрес F2 – относительный (он меняется). СРЗНАЧ в правой части неравенства – величина постоянная. Поэтому диапазон ячеек для этой функции имеет абсолютные адреса $F$2:$F$12.

· В ячейке E22 листа Фильтр сформируется константа Истина или Ложь:

 

 

· Сделаем активной любую свободную ячейку листа Фильтр и выполним команду Данные/Сортировка и Фильтр/Дополнительно.

· В диалоговом окне сделаем установки. Исходный диапазон определим клавишей F3. Длявводадиапазона условий выделим ячейки Е21:Е22 листа Фильтр (заголовок столбца вычисляемого условия не заполняется, но выделяется вместе с условием). Для диапазона результата выделим ячейки А21:С21 на листе Фильтр.

· Получим результат:

Сводные таблицы

Сводная таблица – это один из мощных инструментов обработки данных, так как в этом случае сразу выполняются функции и заданные вычисления, подводятся итоги, выполняется сортировка и фильтрация данных. Построение сводной таблицы выполняется с помощью Мастера сводных таблиц, который вызывается командой Вставка /Сводная таблица.

На первом шаге выбирается источник данных. На втором шаге – диапазон исходных данных. На третьем строится макет сводной таблицы.

Макет сводной таблицы определяет её структуру и состоит из областей:

· Фильтр отчёта (область страниц) - для размещения полей, по которым выполняется отбор записей;

· Названия строк – для полей, по которым выполняется группировка;

· Названия столбцов – для размещения полей группировки;

· Значения (итоговые данные) – для размещения полей, по которым выполняются вычисления. Операции: сумма, среднее значение, количество значений, максимум, проводятся вычисления и т.д. На приведённых далее примерах рассмотрим создание сводных таблиц.

Пример 1. Создать сводную таблицу на основе таблицы листа Рабочая ведомость следующего вида:

Выполним команду Вставка/Сводная таблица. Затем Далее.

В окне указать имя Ведомость_список. (нажать F3 и выбрать Ведомость_список ).

Все названия полей таблицы расположены справа в окне макета. Каждое поле можн