Использование Microsoft Excel для работы с базами данных

Лекция к лабораторной работе № 8 Использование Microsoft Excel для работы с базами данных План. 1. 2. 3. 4. 5. 6. 7. Список и его составляющие Способы ввода данных в список или базу данных Проверка вводимых значений Сортировка данных Поиск и фильтрация данных. Критерии поиска Анализ списка с помощью подведения промежуточных итогов Функции для анализа списка Список и его составляющие Одной из типичных задач, выполняемых с помощью электронных таблиц, является ведение как простых (небольших по размерам), так и более сложных (занимающих большой объем дискового пространства) баз данных (БД): списков номеров клиентов, торговых операций, материальных ценностей. MS Excel позволяет легко систематизировать и анализировать такого рода информацию. С позиции Excel БД - это просто список, состоящий из одного или более столбцов. Выделяют следующие элементы списка:  запись (отдельная строка);  поле (отделенный столбец);  имена полей (в первой строке списка); Строка  строка заголовков (первая строка списка). Поле заголовков Имя поля ФАМИЛИЯ ИМЯ ОТЧЕСТВО ФАКУЛЬТЕТ КУРС Иванов Иван Иванович ИЭФ 1 Петров Петр Петрович РТФ 2 Запись Сидоров Антон Егорович ВТ 4 Для создания м ведения полнофункциональных списков следует различать следующие понятия: Диапазон базы данных – область рабочего листа, где хранятся данные списка; связанные друг с другом данные записываются в отдельные строки, каждому столбцу соответствует свое поле списка с уникальным именем поля. Диапазон критериев - область на рабочем листе, где задаются критерии поиска информации; здесь указываются имена полей и отводится область для записи условий отбора. Диапазон для извлечения - область, в которую копируют выбранные из списка данные. При работе с БД оказывается удобным именовать диапазоны. Если каким-то диапазонам на листе присвоены имена (например, База_данных, Критерии), то по умолчанию Excel считает их задающими некоторый диапазон. Для более удобной работы со списком и для использования средств фильтрации данных необходимо в первой строке списка (строке заголовка) определить имена полей. Они служат для обозначения соответствующих столбцов, содержащих информацию. Список должен иметь хотя бы одну строку данных. Имена полей должны удовлетворять следующим требованиям:  не должны превышать 255 символов; 1 Лекция к лабораторной работе № 8  только названия непосредственно над данными используются в качестве имён полей, т.е. нельзя вставлять пустую строку между строкой заголовков или ячейками данных;  имена должны быть уникальными. Для достижения максимального эффекта в работе со списками надо, чтобы: - каждый столбец содержал информацию одного типа; - одна или две верхних строки содержали заголовки, описывающие содержимое расположенного ниже столбца; - не включать в список пустые строки и столбцы; - список находился на отдельном листе или чтобы он был отделен от других данных рабочего листа. Данные, представленные в виде списка могут хранится как внутри книги Excel, так и во внешнем хранилище. При просмотре достаточно большого списка удобно, чтобы строка заголовков всегда оставалась видимой. Для того, чтобы зафиксировать шапку списка (заголовки столбцов или строк) используют команду Окно/Закрепить области. Если выделить ячейку и выполнить эту команду, то окно разделится, и области выше и слева от активной ячейки фиксируются. После выбора этой команды нельзя прокручивать верхнюю левую область в любом направлении. В правой верхней области можно будет прокручивать только столбцы, а в левой нижней области - только строки. Нижнюю правую область можно прокручивать в любом направлении. Команда Окно/Снять закрепление областей снимает не только закрепление, но и разделение областей. Способы ввода данных в список или базу данных После того, как список создан (определены заголовки столбцов), его необходимо заполнить данными. Это один из наиболее трудоемких этапов работы, отнимающий много времени и сил. Excel предоставляет как разнообразные возможности для непосредственного ввода с приемами ускорения, так и средства автоматизации ввода, облегчающие ввод данных. Непосредственный ввод данных. Вводить данные в список можно непосредственно с клавиатуры в строки рабочего листа. При этом следует учесть, что если диапазону ячеек списка присвоено имя, лучше всего вставлять новые строки или ячейки между существующими записями (строками) списка. Если вставлять ячейки после последней записи списка, то они окажутся вне именованного диапазона и его необходимо переопределить. Использование автозавершения и команды Выбрать из раскрывающегося списка Многие таблицы содержат в ячейках одну и ту же информацию. Excel имеет средство автоматизации ввода повторяющихся данных. Автозавершение отслеживает вводимую текстовую информацию, и если первые набранные символы совпадают с началом какой-либо записи в одной из ячеек текущего столбца, Excel автоматически вставляет ранние используемые данные. При наборе первых символов элемента списка автозавершение предлагает один из вариантов завершения слова или словосочетания, которые находятся выше в том же столбце. Можно сделать иначе: щелкнуть правой кнопкой мыши в пустой ячейке, из контекстного меню взять пункт Выбрать 2 Лекция к лабораторной работе № 8 из раскрывающегося списка и затем выделить нужное значение из списка имеющихся значений столбца. Этот список строится на основании введенных в текущем столбце данных, при появлении он упорядочивается по алфавиту. или Использование автозавершения и команды выбора из раскрывающегося списка позволяют сэкономить много времени, а также уберечься от ошибок при вводе повторяющейся информации. Использование форм данных При заполнении списка новую информацию можно вносить вводом данных в пустой строке. Но удобнее при вводе использовать автоматически создаваемую форму данных, которая вызывается с помощью команды Данные/Форма. Перед вызовом команды надо выделить любую ячейку в списке. После вызова команды на экране появляется окно формы: имя листа заголовки столбцов списка В верхней части формы Excel выводит имя листа, содержащего список, для которого предназначена эта форма. Ниже этой строки заголовка находятся все заголовки столбцов списка. В правом верхнем углу формы выводится информация об общем количестве строк в списке и номере строки, отображаемой в форме в данный момент времени. Строка заголовков столбцов исключается из общего количества. Если столбец не содержит значений, вычисляемых с помощью формул, то рядом с его заголовком располагается поле ввода. Если же столбец содержит вычисляемое поле, то оно будет присутствовать в форме, однако в него нельзя будет ввести данное. Ускорение ввода данных Ввод данных может быть замедлен в связи с тем, что Excel одновременно производит вычисления по имеющимся на рабочем листе формулам, пересчитывая содержимое рабочего листа при появлении новых данных. Если замедление работы окажется существенным, то можно отключить режим фоновых вычислений. Для этого используется команда Сервис/Параметры далее надо активизировать вкладку Вычисления и установить переключатель вручную в группе Вычисления. Если Вы планируете обращаться к данным сохраненной на диске рабочей книги, следует также установить флажок пересчет перед сохранением. Это гарантирует запись на диск актуальных данных. 3 Лекция к лабораторной работе № 8 Если отключен режим фоновых вычислений, а Вы вводите или меняете данные, от которых зависят формулы, в строке состояния в нижней части окна Excel появляется слово «Вычислить». Это означает, что показываемые на экране результаты не соответствуют действительности. Ручной запуск вычислений во всех открытых рабочих книгах осуществляется с помощью клавиши . Для пересчета результатов в формулах только активного рабочего листа надо выполнить команду Сервис/Параметры далее надо активизировать вкладку Вычисления и нажать кнопку Пересчет листа. Проверка вводимых значений MS Excel предлагает специальное средство, позволяющее проверять, удовлетворяют ли заданным условиям вводимые в список значения. Можно задать допустимый тип данных (например, целые числа, даты, время или текст), диапазон разрешенных значений (например, целые числа от 1 до 31), указать перечень разрешенных значений (например, названия отделов предприятия). Следует отметить, что проверке подвергаются только значения, вводимые пользователем непосредственно в ячейки. Поэтому список может содержать некорректные данные, если они оказались там в результате операций копирования и вставки. Чтобы задать условия проверки данных, нужно выделить диапазон ячеек, к которому должны применяться эти условия, затем воспользоваться командой Данные/Проверка…. На экране появится окно диалога Проверка вводимых значений, содержащее три вкладки: Параметры , Сообщение для ввода, Сообщение об ошибке. Задание типа данных и допустимых значений Вкладка Параметры позволяет задать тип и интервал значений, которые разрешается вводить. Тип значений выбирается из списка Тип данных. Интервал значений задается с помощью списка Значение: и поля/полей ввода. Если выбрать в списке Значение операцию между или вне, то полей будет два, для остальных операций  одно. Надписи, сопровождающие поля, будут меняться в зависимости от выбранной операции. Чтобы задать список допустимых значений, его нужно сначала сформировать на рабочем листе, а 4 Лекция к лабораторной работе № 8 потом в раскрывающемся списке Тип данных выбрать вариант Список и в поле Источник указать диапазон, в котором хранится список допустимых значений. Можно не создавать список на рабочем листе. Тогда значения для раскрывающего списка нужно указать в поле Источник, отделяя их друг от друга точкой с запятой: Бухгалтерия; ПФО; ОК; Цех №1; Цех №2; Цех №3. Чтобы для проверки данных использовать формулу, в раскрывающемся списке Тип данных нужно выбрать вариант Другой и затем ввести нужное выражение в поле Формула. Например, чтобы значение в ячейке А1 было больше значения в В1, нужно выделить ячейку А1, выбрать команду Данные/Проверка…, в раскрывающемся списке Тип данных указать Другой и затем ввести = А1>В1 в поле Формула. Если перед выбором команды Данные/Проверка… выделен диапазон ячеек, то в формуле должны соответствующим образом использоваться относительные и абсолютные ссылки. Например, нужно обеспечить правильный ввод данных в ячейки из столбца А, и для проверки значений используется формула =А1>В1. В этом случае Excel будет проверять условия А1>В1, А2>В2 и т. д. Если задать формулу =А1>$В$1, то Excel будет проверять выполнение условий А1>В1, А2>В1, А3>В1 и т. д. Сообщение для ввода Чтобы задать подсказку, которую Excel будет выводить при вводе значений в заданный диапазон, в окне диалога Проверка вводимых значений нужно воспользоваться вкладкой Сообщение для ввода. Здесь можно ввести заголовок и текст сообщения. Когда проверяемая ячейка будет выделена, это сообщение появится рядом с ней как примечание. Задание сообщения об ошибке Если в проверяемую ячейку введено неправильное значение, Excel выводит стандартное сообщение об ошибке и предлагает повторить или отменить ввод (в случае отмены 5 Лекция к лабораторной работе № 8 восстанавливается прежнее содержимое ячейки). Вместо стандартного сообщения можно задать пользовательское. Для этого на вкладке Сообщение об ошибке диалогового окна Проверка вводимых значений нужно ввести заголовок и текст сообщения. Кроме того, в раскрывающемся списке Вид можно выбрать тип сообщения об ошибке: Останов, Предупреждение, Сообщение. Эти варианты отличаются значками, которые выводятся рядом с текстом сообщения, а также набором кнопок. Сообщение типа Останов позволяет повторить либо отменить ввод. Сообщение типа Предупреждение дает возможность наряду с этим сохранить введенное некорректное значение. Если выбран вариант Сообщение, то пользователь сможет сохранить введенное значение или отменить ввод, но ему не будет позволено повторить ввод. Сортировка данных Excel предоставляет различные способы сортировки данных по возрастанию или по убыванию, с учетом или без учета регистра букв. Можно задать собственный порядок сортировки. При выполнении сортировки необходимо различать понятия ключа сортировки, критерии (условия) сортировки и порядка сортировки. Ключи сортировки задаются столбцами или строками сортируемого диапазона, определяющими новый порядок данных. В Excel 2003 количество ключей сортировки не превышает трех (в Excel 2007, 2010 можно задавать до 64 ключей сортировки). Критерии сортировки задаются характеристиками сортируемых ячеек, по которым определяется их порядок. В качестве критериев сортировки применяются следующие варианты:  значения в ячейках;  форматирование ячеек (начиная с Excel 2007): цвет текста, цвет фона, специальные значки в ячейках. Сортировка по формату может предусматривать, например, упорядочивание согласно заданному порядку цветов. Порядок сортировки указывает правило организации сортируемых ячеек. В Excel используется следующий порядок сортировки по возрастанию данных (по умолчанию): 1. числа от наименьшего отрицательного до наибольшего положительного; 2. текст – по алфавиту; 3. логическое значение ложь; 4. логическое значение истина; 5. значения ошибок; 6. пустые значения. При сортировке по убыванию это порядок заменяется на обратный (за исключением пустых ячеек, которые всегда помещаются в конец списка). Основными средствами сортировки в Excel являются быстрая и настраиваемая сортировка. 6 Лекция к лабораторной работе № 8 Быстрая сортировка данных С помощью кнопок Сортировка по возрастанию/Сортировка по убыванию панели инструментов Стандартная можно упорядочить данные по одному полю списка (столбец которого является текущим). Перед сортировкой списка надо сначала выделить в нем какуюнибудь ячейку (или выделить весь список), затем выбрать команду Данные/ Сортировка. Excel просмотрит данные, для определения размера списка, выяснит, содержит ли список строку заголовка, которую нужно исключить из сортировки и затем выведет на экран окно диалога Сортировка диапазона, которое содержит поля: Сортировка в особом - Сортировать по порядке предусматривает - Затем по ввод параметров сортировки - В последнюю очередь, по Порядок сортировки может быть «по возрастанию» и «по убыванию». Настраиваемая сортировка Для настройки дополнительных условий сортировки необходимо воспользоваться кнопкой Параметры диалогового окна Сортировка диапазона и в диалоге Параметры сортировки указать требуемый параметр. В этом окне можно указать, что в сортировке участвуют данные, расположенные в столбцах диапазона (т.е. сортируем данные слева направо), а не в строках (т.е. расположенные сверху вниз). Использование флажка Учитывать регистр позволяет настраивать учет больших и маленьких букв при сортировке текстовых данных. Сортировка по четырем и более полям Excel позволяет отсортировать список по трем столбцам сразу. Если надо отсортировать более чем по трем столбцам, то надо выполнить несколько последовательных сортировок по меньшему количеству столбцов. Сначала надо тогда отсортировать по наименее важному столбцу, затем повторить сортировку по столбцу следующей степени важности и т.д. (т.е. следует начинать с ключей самого нижнего уровня). Пример. Пусть необходимо отсортировать список по пяти полям: в первую очередь по Издательство, затем по Году выпуска, Названию, Имени и в последнюю очередь по Фамилии. Сортировку нужно производить в два этапа. Сначала отсортировать по Названию, Имени и Фамилии (1й этап), а затем по Издательству и Году выпуска (2й этап). 7 Лекция к лабораторной работе № 8 Результаты 1-го этапа Результаты 2-го этапа с учетом сортировки в особом порядке по параметру Издательство Пример. Пусть необходимо отсортировать список по пяти полям: в первую очередь по Отделу, затем по Фамилии, Имени, Отчеству и в последнюю очередь по Дате рождения. Сортировку нужно производить в два этапа. Сначала отсортировать по Имени, Отчеству и Дате рождения (1-й этап), а затем - по Отделу и Фамилии (2-й этап). Сортировка в особом порядке MS Excel позволяет сортировать данные в особом порядке. Например, по дням недели, по месяцам или указать собственный порядок сортировки. Для этого в диалоговом окне Сортировка диапазона нужно выбрать кнопку Параметры. В диалоговом окне Параметры сортировки в раскрывающемся списке Порядок сортировки по первому ключу выбрать подходящий вариант. Чтобы воспользоваться собственным порядком сортировки, его нужно предварительно создать: 1) с помощью команды Сервис/Параметры вызвать окно Параметры; 2) перейти на вкладку Списки; 3) в поле Элементы списка ввести элементы, разделяя их нажатием клавиши ; 4) после ввода всех элементов нажать кнопку Добавить и закрыть окно Параметры. 8 Лекция к лабораторной работе № 8 Если вы выделяете одну ячейку, то в сортировке участвует весь блок ячеек, имеющий данные, который находится рядом с ней. Если надо отсортировать часть ячеек, то надо выделить соответствующий диапазон. Возврат к первоначальному порядку сортировки Можно восстановить исходное состояние списка после выполнения нескольких операций сортировки, если перед выполнением первой из них создать в списке столбец, заполненный числовой последовательностью. Тогда для восстановления исходного состояния списка после его сортировки надо еще раз отсортировать его по этому же столбцу. Поиск и фильтрация данных. Критерии поиска Одними из самых частых действий над списками в Excel являются поиск и фильтрация. Поиск производится на основе задаваемых пользователем критериев - требований, налагаемых на информацию. Отфильтровать список – значит скрыть все строки за исключением тех, которые удовлетворяют заданным условиям отбора. Поиск и фильтрацию данных можно осуществить с помощью формы данных, автофильтра и расширенного фильтра. Критерии поиска Чтобы найти данные любым из способов поиска или фильтрации, необходимо описать то, что надо найти, т.е. задать критерии поиска. Excel поддерживает несколько типов критериев: – – – – критерии на основе сравнения; критерии в виде образца-шаблона; множественные критерии на основе логических операций; вычисляемые критерии на основе логических формул. Критерии на основе сравнения позволяют находить точные соответствия или отбирать данные из заданного интервала. При этом математические вычисления и логические операции (такие как И, ИЛИ) не используются. Критерии на основе сравнения можно использовать в Excel при любом способе поиска или фильтрации. Для ввода простейшего критерия достаточно ввести в соответствующем поле искомое значение. В критериях на основе сравнения можно использовать различные операции сравнения: =, >, <=, <, >=, <>. Поиск близкого соответствия с использованием образца. Если нет уверенности в правильности написания слова или необходимо найти записи, содержащие похожий, но не совпадающий полностью текст, то надо применить образцы (шаблоны) искомого текста, содержащие специальные символы, называемые символами шаблона. К текстовым символам шаблона относятся звездочка (*) и знак вопроса (?), они обозначают следующее:  «*» заменяет любое количество любых символов,  «?» заменяет один любой символ в той же позиции. Например, нужно выбрать все фамилии, начинающиеся на «Ив» (Иванов, Ивлев, Иванович и т.п.). Для решения этой задачи можно использовать шаблон «Ив*». Если же необходимо найти поля, содержащие сам символ ? или *, следует в образце перед ? или * поставить знак «тильда» (~). Это значит, что ? или * не используются как символы шаблона. Поиск соответствия с использованием множественного критерия с операциями И и ИЛИ. Для поиска записей, удовлетворяющих нескольким условиям, можно задавать множественные критерии. Для наложения нескольких условий поиска в Excel используются логические операции и, или, которые вычисляются по следующим правилам: 9 Лекция к лабораторной работе № 8   Чтобы запись была найдена или отобрана с помощью фильтра, она должна отвечать всем критериям, соединенным операцией И; Чтобы запись была найдена или отобрана с помощью фильтра, она должна отвечать хотя бы одному из критериев, соединенных операцией ИЛИ. Поиск и фильтрация с помощью форм данных Форма данных в Excel – великолепное средство для поиска и редактирования отдельных записей. Чтобы осуществить поиск записей, нужно воспользоваться командой Данные/Форма. В появившемся диалоговом окне нажать кнопку Критерии и в нужных полях задать критерии поиска. Для перехода к записям, удовлетворяющим критерию, нажать кнопку Далее или Назад. Найденные записи можно легко изменять в полях формы. Для перехода к записям, удовлетворяющим критерию, нажать кнопку Далее или Назад. В форме данных можно использовать критерии поиска точного соответствия, соответствия с использованием образца. Если в критерии задать условия для нескольких полей, то будет осуществлен поиск соответствия с использованием множественного критерия с операцией И. Множественный критерий с операцией ИЛИ, а также критерии, требующие вычислений, с помощью формы данных реализовать нельзя. При работе с формой данных критерии могут быть соединены только операцией И. Кнопки формы данных Вернуть и Удалить можно использовать для отмены внесенных в запись изменений и удаления записи соответственно. 10 Лекция к лабораторной работе № 8 Использование фильтра Автофильтр предоставляет простой доступ к мощным средствам рабочего листа. Автофильтр выводит информацию на рабочем листе, при этом записи, не удовлетворяющие критерию, скрыты. Перед использованием команды Автофильтр выделите любую ячейку в списке и выполните команду Данные/ Фильтр/ Автофильтр. Excel выведет кнопки со стрелками рядом с каждым заголовком столбца. Щелчок на кнопке со стрелкой рядом с заголовком раскрывает список значений, которые можно использовать для заданий условий отбора строк. При работе с автофильтром имеется три метода фильтрации данных. 1. Выбор значения поля для поиска точного соответствия: нажать в нужном столбце кнопку автофильтра и в появившемся списке значений для отбора выбрать требуемый вариант. Например, необходимо получить информацию обо всех сотрудниках бухгалтерии. Для этого следует воспользоваться кнопкой автофильтра возле заголовка соответствующего поля и в списке выбрать вариант «Бухгалтерия». На экране останутся записи только о сотрудниках бухгалтерии, а остальные записи будет скрыты. Результат фильтрации 2. Пользовательский автофильтр: в списке, который появляется при нажатии на кнопку автофильтра, выбрать вариант (Условие). На экране появится диалоговое окно, позволяющее задать условия отбора данных. Раскрывающиеся списки в левой части этого окна содержат операторы сравнения (=, <>, < , >, >=, <= ), а раскрывающиеся списки в правой части позволяют выбрать значения, которые должны использоваться в условиях отбора. При задании условий отбора можно использовать два символа шаблона: * и ? (подстановочные символы). Можно задать одно условие или два. В последнем случае условия могут связываться логическими операциями И или ИЛИ. Результат фильтрации 3. Первые 10. Этот метод имеет смысл только для полей с числовыми данными, в том числе и с датами. Чтобы воспользоваться этим методом, нужно в списке выбрать вариант (Первые 10…). В появившемся диалоговом окне следует указать, сколько наибольших или наименьших элементов отображать. Например, в БД имеется поле Оклад для хранения размера оклада сотрудника. Организация имеет возможность оказать материальную помощь пяти сотрудникам с наименьшим окладом. Требуется найти таких сотрудников. Для решения такой задачи можно воспользоваться методом отбора (Первые 10…) для поля Оклад: задать количество отображаемых записей 5 с наименьшим значением. 11 Лекция к лабораторной работе № 8 Если столбец содержит пустые ячейки, то в нижней части раскрывающегося списка автофильтра будут присутствовать элементы Пустые и Непустые. Если надо скрыть строки с пустыми ячейками, то в качестве условия отбора надо задать Непустые. Критерии фильтрации находятся в списке в алфавитном порядке. Стандартные ключи фильтрации:  Все – позволяет отобразить весь список.  Первые 10 – позволяет найти в списке несколько первых записей, по выбранному показателю.  Условие – позволяет найти в списке записи, удовлетворяющие заданному пользователем условию.  Пустые – позволяет найти записи, не содержащие ни какой информации.  Непустые – находит записи, ячейки выделенного поля которых не пусты. Когда выбран критерий фильтрации записи, не удовлетворяющие этому критерию, становятся невидимыми, а номера строк рабочего листа будут подсвечиваться синим цветом. Кроме того, если какое-либо поле участвует в задание фильтра, то значок на кнопке автофильтра также подсвечивается синим цветом. Чтобы снять фильтрацию с поля, нужно нажать на кнопку автофильтра и выбрать вариант (Все). Можно одновременно накладывать условия на несколько полей. В этом случае условия отбора связаны операцией И, т.е. отображаться будут только те записи, для которых условия одновременно выполняются во всех полях, в соответствии со значениями в которых происходит фильтрация. Чтобы отменить все примененные автофильтры, надо выполнить команду Данные/ Фильтр/ Автофильтр. Команда расширенный фильтр. В отличие от Автофильтра команда Расширенный фильтр имеет некоторые дополнительные возможности:  допускается задавать условия, соединенные логическим оператором и, или для нескольких столбцов;  допускается задавать три и более условий для конкретного столбца с использованием хотя бы одного логического оператора. Например, можно вывести информацию о рабочих, чья фамилия начинается с букв А, К, Л;  допускается задавать вычисляемые условия, например, можно вывести на экран тех сотрудников, у кого оклад хотя бы на 25% выше среднего. Команда расширенный фильтр требует задания условий отбора строк в отдельном диапазоне рабочего листа, лучше выше или ниже списка, так как при фильтрации скрываются целые строки. Диапазон условий должен содержать, по крайней мере, две строки. В верхней строке вводятся названия заголовков столбцов, а условия отбора заносятся во вторую и последующие строки. За исключением вычисляемых условий, заголовки в диапазоне критериев должны точно совпадать с заголовками столбцов в списке. В диапазон условий достаточно включать заголовки только тех столбцов, которые используются в условиях отбора. 12 Лекция к лабораторной работе № 8 Чтобы воспользоваться расширенным фильтром, надо выбрать последовательно пункты Данные/ Фильтр/ Расширенный фильтр. На экране появится диалоговое окно Расширенный фильтр. В элементе управления Исходный диапазон нужно указать диапазон, в котором размещается список, в элементе управления Диапазон условий - диапазон критериев. По умолчанию в группе Обработка всегда установлен переключатель фильтровать список на месте. Если выбрать вариант скопировать результат в другое место, тогда будет задействован диапазон для извлечения. В этом случае становится доступным элемент управления Поместить результат в диапазон, в котором нужно задать диапазон, в который будут помешены отфильтрованные данные. Флажок Только уникальные записи позволяет исключить повторяющиеся. Команда Расширенный фильтр скрывает все строки, которые не прошли через фильтр. Она также отображает номера отобранных строк синим цветом, а в строке состояния показывает число найденных записей. Число условий в диапазоне условий может быть любым. Excel интерпретирует условия в соответствии со следующими правилами: 1. условия, записанные на одной строке, считаются соединенными логически оператором И; 2. условия, записанные на разных строках, считаются соединенными логическим оператором ИЛИ. Существует несколько правил задания текстовых условий. 1. Если необходимо найти все значения, которые начинаются с конкретной буквы, нужно в диапазоне критериев указать эту букву. 2. Символ «>» («<») означает: «Найти все значения, которые находятся по алфавиту после (до) введенного текстового значения. Например, при задании условия «>М» ниже заголовка Фамилия в диапазоне критериев отбираются строки, в которых фамилии начинаются с М, Н и т.д. до Я. 3. Формула =”=текст” означает: «Найти значения, которые точно совпадают со строкой символов текст». Например, если просто задать Иванов без формулы, то Excel отфильтрует строки с фамилиями Иванов, Иванова, Иванович, Ивановский и т.д. Условие =”=Иванов” позволит найти строки, в которых в поле Фамилия будет указана фамилия Иванов. 13 Лекция к лабораторной работе № 8 4. Кроме того, при задании текстовых условий можно использовать символы шаблона «*» и «?». Например, для того чтобы найти записи о сотрудниках, чьи имена состоят из шести букв и заканчиваются на «ей», нужно задать такой шаблон в диапазоне критериев под заголовком Имя: «????ей». Чтобы вновь отобразить список (то есть снять действие фильтра), необходимо воспользоваться командой Данные/Фильтр/Отобразить все. Пример: 1. Для диапазона условий 2. Для диапазона условий Фамилия Фамилия Возраст А* А* >40 Г* Г* >40 Н* H* >40 выведутся данные о тех сотрудниках, фамилии Выведется список сотрудников, фамилии которых начинаются с букв А, Г и Н которых начинаются с букв А, Г, Н и возраст которых больше 40. 3. Для диапазона условий Фамилия Отдел А* Бухгалтерия В* Бухгалтерия Г* Конструкторский H* Экономический диапазон критериев задает следующее условие: выбрать их списка записи о сотрудниках бухгалтерии с фамилиями на А и на В, о сотрудниках конструкторского отдела с фамилией на Г и о сотрудниках экономического отдела с фамилией на Н. Использование вычисляемых условий Формулы, результатом которых является логическая величина ИСТИНА или ЛОЖЬ, могут использоваться в качестве критерия при фильтрации данных. Если формула для некоторой записи выдает значение ИСТИНА, то запись удовлетворяет критерию. Вычисляемые критерии используются, например, при поиске записей по датам, попадающим в определенный интервал, по почтовому индексу, который входит в состав поля адреса, по некоторой величине, получаемой на основе значений одного или нескольких полей записи. Правила применения вычисляемых условий: 1. заголовок над вычисляемым условием должен отличаться от заголовка любого из столбцов списка; 2. ссылки на ячейки, находящиеся вне списка, должны быть абсолютными; 3. ссылки на ячейки в списке должны быть относительными (это правило имеет исключение). На рисунке приведен пример использования в расширенном фильтре вычисляемого условия. Необходимо получить записи о людях, принятых на работу в 2005 году. Начальная и конечная даты периода заданы в ячейках В2 и С2. Диапазон критериев состоит только из вычисляемого условия с заголовком Условие (такого заголовка в исходном списке нет). 14 Лекция к лабораторной работе № 8 В ячейке D2 находится формула; она отображена в примечании. Ссылки на ячейки В2 и C2 являются абсолютными, поскольку они расположены вне диапазона база данных. Ячейка J6 является первой ячейкой в поле Дата приема на работу неотфильтрованного списка. Эта ячейка находится в диапазоне базы данных, поэтому ссылка на нее является относительной. В процессе фильтрации Excel по порядку подставляет каждый элемент вместо этого первого значения, то есть Excel сравнивает J6, затем J7, потом J8 и т.д., пока не дойдет до конца списка. Если сравнение дает ложный результат, то соответствующая строка скрывается. Таким образом, отображенными останутся только те строки, для которых формула дает результат ИСТИНА. На рисунке ниже приведен еще один пример использования вычисляемого условия. Здесь вычисляемое условие позволяет найти людей с окладом выше среднего оклада на предприятии. Ранее было оговорено, что ссылки на ячейки в диапазоне базы данных должны быть относительными, однако здесь ссылки на диапазон ячеек от F6 до F33 являются абсолютными. Это сделано для того, чтобы Excel использовал один и тот же диапазон ячеек на каждом шаге процесса фильтрации, то есть, чтобы Excel сравнивал F6 со средним для F6:F33, затем F7 со средним для F6:F33 и т.д. Анализ списка с помощью подведения промежуточных итогов Составление отчетов В процессе подготовки отчетов часто требуется подводить итоги. В Excel можно подводить итоги автоматически, не задавая вручную пространства или формулы для них. В зависимости от цели отчета в него может быть включена полная информация рабочего листа, а может только промежуточная, а может и та и другая. Подведение промежуточных итогов можно осуществить по 15 Лекция к лабораторной работе № 8 нескольким показателям: определению количества элементов списка, суммированию величин, нахождению максимального и минимального значений, среднего значения, можно также использовать статистические функции. Для представления различной итоговой информации используется команда Данные/ Итоги. Эта команда добавляет строки промежуточных итогов для каждой группы элементов списка. Перед использованием команды следует провести сортировку данных, только потом ее выполнить. В появившемся диалоговом окне Промежуточные итоги в поле При каждом изменении в указать имя столбца, содержащего элементы для группы, для которой следует подвести промежуточные итоги; в поле Операция надо выбрать функцию, которую следует использовать при вычислении промежуточных итогов, а в поле Добавить итоги по надо указать один или несколько столбцов, содержащие обрабатываемые значения. Здесь указывается, к каким столбцам применяется функция. Если ранее итоги уже были подведены и их надо заменить новыми, то надо пометить галочкой поле Заменить текущие итоги. Если после каждой группы промежуточных итогов надо вставить разрыв страниц, то надо пометить галочкой поле Конец страницы между группами. Установка галочки в поле Итоги под данными позволяет вставить промежуточные итоги ниже каждой группы, а общие итоги – в самом низу списка. 16 Лекция к лабораторной работе № 8 Для удаления итогов надо: Убедится, что одна из ячеек подытоженных данных выделена; Выполнить команду Данные / Итоги. Щелкнуть на кнопке Убрать все. К столбцам можно применить несколько итоговых формул. Для этого надо сначала подвести итог по одному параметру, а затем по другому при снятом флажке Заменить текущие итоги. Если же флажок Заменить текущие итоги установлен, то итоги полученные ранее будут заменены новыми. Пример. Необходимо подсчитать количество и средний оклад сотрудников в каждом отделе предприятия. Шаг 1: исходный список нужно отсортировать по полю Отдел. Шаг 2: воспользоваться командой Данные/Итоги…. На экране появится диалоговое окно Промежуточные итоги, в котором устанавливаем параметры в соответствии с рисунком (шаг 2). Шаг 3: воспользоваться командой Данные/Итоги…. На экране появится диалоговое окно Промежуточные итоги, в котором устанавливаем параметры в соответствии с рисунком (шаг 3). Шаг 3 Шаг 2 В результате получим: 17 Лекция к лабораторной работе № 8 Структурирование рабочих листов. В процессе подведения промежуточных итогов Excel автоматически структурирует рабочий лист. При этом взаимосвязанные данные группируются вместе при структурировании рабочих листов. Вертикальные линии слева на полях заканчиваются кнопками уровней , они позволяют контролировать количество уровней деталей, попадающих в отчет. Детали структурированных листов можно скрывать, щелкая на кнопках уровней структуры, соответствующих линиям областей структуры и отвечающих за характер детализации рабочего листа. Чтобы увидеть только общий итог по всем данным, следует щелкнуть на кнопку с номером один, а для получения детальной картины всех элементов нужно воспользоваться последней кнопкой (с максимальным номером). Функции для анализа списка Функции для анализа списка  это функции, возвращающие информацию об элементах списка, которые удовлетворяют некоторым условиям. Функции СЧЕТЕСЛИ и СУММЕСЛИ Функции СЧЕТЕСЛИ и СУММЕСЛИ предназначены для работы со списками. Они проще в использовании, чем другие функции, используемые для анализа списка, поскольку позволяют задавать условия непосредственно в формуле. Но в этих функциях можно использовать только простые условия сравнения. Функция СЧЕТЕСЛИ имеет синтаксис =СЧЕТЕСЛИ(диапазон; условие). Аргумент диапазон задает диапазон, в котором подсчитывается количество значений, удовлетворяющих критерию. Критерий задается вторым аргументом условие, представляющим собой текстовое значение. Эта функция относится к категории статистических функций. Пример. 1. Необходимо подсчитать количество сотрудников с именем Иван (рис. 1). Для решения задачи понадобится формула: =СЧЕТЕСЛИ(В2:В7;”Иван”). Ответ: 3. 2. Требуется определить число сотрудников, у которых оклад не превышает 4500р. (рис. 1). Задачу можно решить с помощью формулы: =СЧЕТЕСЛИ(Е2:Е7;”<=4500”). Ответ: 1. Функция СУММЕСЛИ имеет синтаксис =СУММЕСЛИ(диапазон; условие; диапазон_суммирования). Первые два аргумента диапазон и условие используются так же, как и в функции СЧЕТЕСЛИ. Аргумент диапазон_суммирования задает диапазон суммируемых значений. Функция относится к категории математических функций. Пример. Пусть необходимо подсчитать сумму окладов всех сотрудников с именем Иван. Первый аргумент функции (рис. 1) задает диапазон В2:В7, в котором осуществляется проверка условия (второй аргумент) «имя должно быть Иван» (на рисунке выполнение условия выделено жирным шрифтом). 18 Лекция к лабораторной работе № 8 Если условие выполняется, то значение из диапазона Е2:Е7 (третий аргумент) добавляется к итоговой сумме (суммируемые значения обведены рамкой). Рис.1. Использование функции СУММЕСЛИ Функции баз данных Функции баз данных имеют обобщенное название D-function (Д-функции) и оперируют только с элементами диапазона, которые удовлетворяют заданным условиям. У всех функций баз данных один и тот же синтаксис: =Дфункция(база_данных; поле; критерий) Аргумент база_данных задает весь список, а не отдельный столбец. Второй аргумент поле определяет столбец, в котором производятся вычисления (суммирование, усреднение и т. п.). В качестве второго аргумента можно использовать имя поля, задаваемое в виде текстового значения,  название, заключенное в кавычки, или порядковый номер столбца в списке. Если формула формируется с помощью мастера функций, при заполнении второго аргумента достаточно указать ячейку рабочего листа, в которой хранится имя соответствующего поля. Аргумент критерий задает диапазон критериев. Диапазон критериев формируется так же, как при использовании расширенного фильтра. В таблице приведено краткое описание функций баз данных. Таблица. Функции баз данных Функция ДСРЗНАЧ БСЧЕТ БСЧЕТА БИЗВЛЕЧЬ ДМАКС ДМИН БДПРОИЗВЕД БДСУММ Описание Вычисляет среднее значение в столбце списка или базы данных среди значений, удовлетворяющих заданным условиям Подсчитывает количество ячеек, содержащих числа, в столбце списка или базы данных среди всех записей, удовлетворяющих заданным условиям Подсчитывает все непустые ячейки в столбце списка или базы данных, которые удовлетворяют заданным условиям Извлекает отдельное значение из столбца списка или базы данных, которое удовлетворяет заданным условиям. Если такой ячейки не обнаружено, возвращается значение #ЗНАЧ!. Если заданным условиям удовлетворяют несколько ячеек, то возвращается ошибочное значение #ЧИСЛО! Возвращает наибольшее число в столбце списка или базы данных, которое удовлетворяет заданным условиям Возвращает наименьшее число в столбце списка или базы данных, которое удовлетворяет заданным условиям Перемножает значения в столбце списка или базы данных, которые удовлетворяют заданным условиям Суммирует числа в столбце списка или базы данных, которые удовлетворяют 19 Лекция к лабораторной работе № 8 заданным условиям ДСТАНДОТКЛ Оценивает стандартное отклонение на основе выборки из генеральной совокупности, используя числа в столбце списка или базы данных, которые удовлетворяют заданным условиям ДСТАНДОТКЛП Вычисляет стандартное отклонение генеральной совокупности, используя числа в столбце списка или базы данных, которые удовлетворяют заданным условиям БДДИСП Оценивает дисперсию генеральной совокупности по выборке, используя числа в столбце списка или базы данных, которые удовлетворяют заданным условиям БДДИСПП Вычисляет дисперсию генеральной совокупности, используя числа в столбце списка или базы данных, которые удовлетворяют заданным условиям Автоматизировать процесс вставки функции для анализа данных позволяет мастер функций. Перечисленные в таблице функции находятся в категории Работа с базой данных. Пример. Необходимо вычислить средний оклад мужчин младше 30 лет. На рисунке продемонстрировано решение этой задачи. Пример. Необходимо вычислить количество женщин, работающих в конструкторском отделе, стаж работы которых превышает 5 лет. На рисунке продемонстрировано решение этой задачи. 20