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

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

 

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

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

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

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

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

Основные действия с листами книги:

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

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

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

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

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

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

Ячейки и блоки ячеек

Электронные таблицы состоят из строк и столбцов. Столбцы обозначены латинскими буквами, а строки цифрами. Место пересечения столбца и строки называется ячейкой (для хранения данных). Каждая ячейка имеет уникальный адрес, состоящий из имени столбца и номера строки, на пересечении которых она находится. Например, ячейки А1, В2, D6. Многие команды Excel позволяют работать с блоками ячеек. Блок ячеек – это прямоугольник, задаваемый верхним левым адресом и нижним правым адресом ячеек. Адреса ячеек в блоках разделяются двоеточием. Например, блок А1:В4 включает в себя ячейки А1, А2, А3, А4, В1, В2, В3, В4.

Обычно над блоками выполняют операции: копирования, удаления, перемещения, вставки, присваивания имен и т.д.

С учетом имени листа адрес ячейки состоитиз именилиста, буквы столбца и номера строки. Например, Лист1!А3. Если вся работа производится на одном листе, то название листа в адрес не включается.

Ячейка будет активной, еслищелкнуть по ней мышью.

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

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

 

Типы данных

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

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

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

Под формулой понимают выражение, состоящее из числовых величин, адресов ячеек и функций. Формула всегда начинается со знака = (знак равенства) или + (знак плюс). После нажатия на клавишу Enter в ячейке виден результат, а сама формула отражается в строке формул.

Например, =В7*20.

 

Ввод и форматирование данных в таблице

Текст можно расположить в одной ячейке в несколько строк с помощью: Главная/ Выравнивание, выбрать: Перенос текста, По верхнему краю, По центру.

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

Если необходимо объединить несколько ячеек в одну большую (например,длязаголовка таблицы), надо выделить объединяемые ячейки и щёлкнуть (в Главная/ Выравнивание) команду Объединить и поместить в центре.

Если результат вычислений не помещается в ячейке таблицы, то на экран выводится последовательность символов «#######». В этом случае, необходимо увеличить ширину столбца.

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

Использование функций при создании формул

Формулы могут содержать функции. Например, =СУММ(Е1:F11). Эта функция суммирует числа столбца, с ячейки F1 по F11 включительно. Функция =СРЗНАЧ(Е1:Е11) вычисляет среднее значение всех чисел в диапазоне F1:F11.

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

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

Для ввода ссылки на ячейки в качестве аргументов, нажать кнопку красного цвета - свертывание диалогового окна, чтобы временно скрыть его. Выделить ячейки, представляющие аргумент. Нажать кнопку развертывание (свертывание) диалогового окна. По завершении ввода формулы нажать клавишу ENTER.

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

Копирование формул в электронных таблицах

Если в таблице (в столбце или строке) выполняются расчеты по одной формуле, то формулу расчета следует ввести один раз. Затем перекопировать ее из этой ячейки в другие. Например, применить, один из способов: установить курсор в ячейку, содержащую формулу. Затем протащить маркер (в правом нижнем углу ячейки) по всем ячейкам, в которых надо получить результат. Это наиболее простой способ копирования. Например, если в ячейке G3 формула= D3-C3, то после копирования формулы в ячейки G4, G5 там будут соответственно формулы = D4-C4, =D5-C5. Адресав формуле относительные.

Формула может содержать относительные ссылки на ячейки, как в нашем примере. Относительная ссылка (относительный адрес) в формуле, основана на относительной позиции ячейки (местонахождении ячейки). При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. Это происходит при копировании формулы вдоль строк и вдоль столбцов.

Могут быть абсолютные ссылки на ячейки. Например, в формуле =$A$5*B3. Абсолютная ссылка на ячейку в формуле, $A$5, всегда ссылается на ячейку А5. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не изменяется. Для создания абсолютной ссылки можно использовать клавишу F4 после ввода адреса.

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

Если ссылки на ячейки содержат абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец, то такие ссылки называют смешанными. Абсолютная ссылка столбца, например, приобретает вид $A1, $B1. Абсолютная ссылка строки, например, A$1, B$1.

 

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

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

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

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

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

 

Вставка и удаление строк и столбцов

Если требуется вставить или удалить строки или столбцы в таблицу, надо выделить строку (строки) столбец (столбцы), щёлкнуть правой кнопкой мыши (ПКМ) и выбрать нужную команду: Вставить, Удалить, Вырезать, Копировать, Очистить содержимое и другие.

 

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

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

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

Порядок сортировки по возрастанию:

· Числа  от наименьшего отрицательного до наибольшего положительного;

· Даты и время  от наиболее ранней до самой поздней.

· Текстовые значения  сначала числа, введенные в виде текста, затем обычный текст (по алфавиту).

· Логические значения  сначала Ложь, затем Истина.

Порядок сортировки по убыванию:

В этом случае EXCEL сортирует значения в порядке, обратном порядку по возрастанию, кроме пустых ячеек, которые в этом случае помещаются последними.

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

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

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

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

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

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

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

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

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

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

 

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

Часть I

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

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

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

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

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

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

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

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

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

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

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

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

Проверка:

1.над таблицей (слева от окна формул) в списке Имя по мере создания появляются имена Код_зак или Наим_зак, Адрес, Заказчики.

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

Для этого выполнить:

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

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

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

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

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

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

· Скопировать полученную формулу в ячейки В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 оператор больше (>). В качестве операторов сравнения могут быть: =, >, <, >=, <=, <> (не равно). Результатом логического выражения является логическое значение ИСТИНА или логическое значение ЛОЖЬ.

 

Защита листа

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

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

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

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

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

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

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

Создать три листа с копиями Рабочая ведомость, разместив эти листы за листом Рабочая ведомость.

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

Новые листы назвать Сортировка,Итоги и Автофильтр,

 

Часть II

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

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

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

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

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

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

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

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

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

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

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

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

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

Получим:

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

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

Получим:

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

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

 

Получим:

 

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

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

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

 

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

 

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

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

 

· Для линейного графика можно создать дополнительную ось Y-ов справа на графике. Это тем более необходимо, если значения для линейного графика несоизмеримы со значениями столбцов гистограммы. Щелкнуть по линейному графику и выполнить команду Диаграмма/Параметры диаграммы. В открывшемся окне открыть закладку Оси и установить флажок ось Y(значений) в группе по вспомогательной оси. После этого на графике появится дополнительная ось Y-ов (справа). Нажать кнопку ОК.

 

 

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

Можно изменить фон диаграммы. Для этого щелкнуть правой кнопкой мыши по полю диаграммы. Появится контекстное меню. Выбрать из списка Формат области построения … В следующем окне выбрать заливка – обычная. Нажать ОК. Получим фон диаграммы - белый:

Автофильтр

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

Долг+Пеня

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

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

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

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

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

Установить флажок на «скопировать результат в другое место» и провести курсором на листе Фильтр диапазон А5:С5. В «Поместить результат в диапазон» получим: Фильтр!$A$5:$C$5

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

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

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

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

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

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

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

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

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

 

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

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

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

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

· В строке условия вводится формула, которая вычисляет логическую константу по отношению к первой записи списка.

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

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

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

 

· Для удобства создания вычисляемого условия можно расположить на экране два окна: одно – лист Рабочая ведомость, другое – лист Фильтр. Для этого выбрать в меню Вид в разделе Масштаб/ Новое окно .

· Кликнуть правой кнопкой мыши по панели задач и выбрать Окна слева направо. На экране появятся два окна, в первом из которых расположится лист Рабочая ведомость, а во втором – лист Фильтр. Так удобнее создавать формулу для критерия отбора на листе Фильтр.

 

· Сделать активной ячейку E22 листа Фильтр и ввести знак
= (равно)

· Кликнуть по ячейке F2 на листе Рабочая ведомость (F2 - первая ячейка столбца Оплачено ) и на листе Рабочая ведомость в строке формул после =’Рабочая ведомость’!F2 ввести знак >(больше)

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

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

Проверить в строке формул:

 

· В окне функции СРЗНАЧ нажать ОК.

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

 

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

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

Получим:

 

Нажать ОК. Получим результат:

После выполнения четырёх задач на листе Фильтр получим:

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

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

В диалоговом окне «Создание сводной таблицы»:

1.подтвердить выбор таблицы Рабочая ведомость, как источник исходных данных;

2.указать, что отчёт сводной таблицы поместить на новый лист.

Нажать ОК.

Открывается новый лист:

 

Справа на листе создаётся структура отчёта сводной таблиц путём размещения полей в следующих областях:

 

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

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

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

∑ Значения -для размещения полей, по которым выполня-ются операции: сумма, среднее значение, количество значений, максимум,проводятся вычис-ления и т.д.