Введение
Excel — это программа для работы с электронными таблицами, входящая в состав пакета Микрософт Office. Посредством Excel возможно отслеживать эти, разрабатывать модели анализа данных, создавать формулы для вычислений с этими данными, сводить эти множеством способов, и отображать их на профессионально выглядящих диаграммах разных видов. Замечательные вычислительные возможности Excel возможно применять для работы с разными денежными документами, к примеру отчётами о перемещении денежных средств, отчётами о доходах либо о убытках и прибылях, использовать для задач управления данными о продажах и выставлении счётов.
Программа нужна для отслеживания о ходе исполнения проекта, расхождениях между предполагаемыми и фактическими результатами, создавать отчёты разных типов, в которых анализируются либо обобщаются эти, и создавать отчёты для прогнозирования данных. Программа Excel замечательно подходит для опытных замыслов, для эргономичных планировщиков, таких как, расписание занятий на 7 дней, замысел исследований рынка, замысел налогообложения, и ведения учёта рабочего времени и ведения складского учёта.
Рабочая область 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.
Практическое задание
Назвать первый лист рабочей книги Справочники.
В ячейках А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.
Вычислить значения поля Долг, применяя логическую функцию В случае, если. При значении поля Отличие больше нуля Долгравен Разнице, в другом случае Долг равен нулю.
Логические функции
Логические функции В случае, если, НЕ, И, ИЛИиспользуют логические выражения для определения истинности заданного условия. К примеру, любая из приведённых формул есть логическим выражением:
=А1A2 =СРЗНАЧ(В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 и выбрать В