66681

РАБОТА СО СПИСКАМИ ИЛИ БД

Доклад

Информатика, кибернетика и программирование

Excel может работать как с простыми и небольшими по размерам, так и с более сложными и занимающими большой объём дискового пространства базами данных (БД). В Excel БД это просто список, состоящий из одного или более столбцов.

Русский

2014-08-26

3.74 MB

2 чел.

РАБОТА СО СПИСКАМИ ИЛИ БД

Список. Составляющие списка

Excel может работать как с простыми и небольшими по размерам, так и с более сложными и занимающими большой объём дискового пространства базами данных (БД). В Excel БД это просто список, состоящий из одного или более столбцов.

Выделяют следующие элементы списка (рис. 1):

запись (отдельная строка);

поле (отделенный столбец);

имена полей (в первой строке списка);

строка заголовков (первая строка списка).

Полнофункциональный список или БД в Excel состоит из диапазона базы данных, диапазона критериев, диапазона для извлечения.

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

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

Диапазон для извлечения – область, в которую копируют выбранные из списка данные.

Диапазон базы данных

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

Если какому-либо диапазону ячеек на текущем листе присвоено имя «База_данных», Excel будет считать этот диапазон списком.

Если перед выбором команды в меню Данные выделен диапазон ячеек, то он будет воспринят как список (имя «База_данных» не определено).

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

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

Диапазон критериев

В качестве диапазона критериев может использоваться любой диапазон ячеек. Если на листе определить имя «Критерии», то соответствующие этому имени ячейки будут использоваться в качестве диапазонов критериев по умолчанию. В диапазоне критериев можно перечислить все имена полей списка, но достаточно указать только те, которые определяют условия отбора. Ниже должна располагаться хотя бы одна строка, где задаются условия. Этот диапазон является необязательным и используется при применении расширенного фильтра и функций БД.

Диапазон для извлечения

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

Ограниченный диапазон для извлечения – диапазон, для которого указано максимально допустимое количество строк. Чтобы задать такой диапазон, следует в какой-либо строке перечислить необходимые имена полей, а затем выделить эту строку и нужное количество пустых строк под ней.

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

Ввод данных в список или базу данных

Ввод имён полей

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

Имена полей должны удовлетворять следующим требованиям:

не должны превышать 255 символов;

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

имена должны быть уникальными.

При просмотре достаточно большого списка удобно, чтобы строка заголовков всегда оставалась видимой. Зафиксировать строку с именами полей можно следующим образом. Нужно опустить маркер разделения окна по вертикали (он находится непосредственно над вертикальной полосой прокрутки) под строку заголовков, затем воспользоваться командой Window|Freeze PanesОкно|Закрепить области. Если необходимо отменить закрепление областей, то сначала нужно выполнить команду Окно|Снять закрепление областей, а затем перетащить маркер разделения окна вверх, чтобы он исчез с рабочего листа.

Ввод данных

Использование формы данных

Сразу после ввода имен полей можно воспользоваться наиболее простым способом ввода информации в список – автоматически создаваемой формой данных:

а) указать диапазон ячеек, где располагается список. Если определенно имя «Базы_данных», Excel автоматически определяет диапазон БД;

б) выбрать команду Data|Form Данные|Форма (рис. 2);

в) нажать кнопку NewДобавить и ввести данные в соответствующие поля (повторять нужное количество раз);

г) нажать кнопку CloseЗакрыть, чтобы вернуться к рабочему листу.

Непосредственный ввод данных.

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

Сортировка данных

Сортировка по возрастанию/по убыванию

Чтобы выполнить сортировку списка, нужно выделить соответствующий диапазон ячеек, выбрать команду Data|Sort Данные|Cортировка. В появившемся окне (рис. 3) задаются ключи сортировки, порядок сортировки. Всего можно задать 3 ключа сортировки. Порядок сортировки может быть «По возрастанию» и «По убыванию».

С помощью кнопок  панели инструментов Стандартная можно упорядочить данные по одному полю.

Сортировка в особом порядке

MS Excel позволяет сортировать данные в особом порядке. Например, по дням недели, по месяцам или указать собственный порядок сортировки. Для этого в диалоговом окне Сортировка диапазона нужно выбрать кнопку Options Параметры. В диалоговом окне Параметры сортировки в раскрывающемся списке Порядок сортировки по первому ключу  выбрать подходящий вариант (рис. 4).

Чтобы воспользоваться собственным порядком сортировки, его нужно предварительно создать:

1) с помощью команды Tools|Options Сервис|Параметры вызвать окно Параметры;

2) перейти на вкладку Custom ListsСписки;

3) в поле Элементы списка ввести элементы, разделяя их нажатием клавиши Enter;

4) после ввода всех элементов нажать кнопку AddДобавить и закрыть окно Параметры.

Сортировка по четырем и более полям

С помощью команды Data|Sort Данные|Сортировка можно упорядочить список по любому количеству полей. Это осуществляется путём последовательных сортировок. Для того чтобы предыдущих сортировок не терялись, следует начинать с ключей самого нижнего уровня.

Пример. Пусть необходимо отсортировать список по пяти полям: в первую очередь по Отделу, затем по Фамилии, Имени, Отчеству и в последнюю очередь по Дате рождения. Сортировку нужно производить в два этапа. Сначала отсортировать по Имени, Отчеству и Дате рождения (1й этап), а затем по Отделу и Фамилии (2й этап)
(рис. 5).


Поиск, фильтрация и редактирование в списках и БД

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

Критерии поиска

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

Для ввода простейшего критерия достаточно ввести в соответствующем поле искомое значение. В критериях на основе сравнения можно использовать различные операции сравнения: =, >, <=, <, >=, <>.

Поиск близкого соответствия с использованием образца. Для задания таких критериев используются символы шаблона * и ?: «*» заменяет любое количество любых символов, «?» один любой символ. Например, нужно выбрать все фамилии, начинающиеся на «Ив» (Иванов, Ивлев, Иванович и т.п.). Для решения этой задачи можно использовать шаблон «Ив*».

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

Использование формы данных

Форма данных в Excel – великолепное средство для поиска и редактирования отдельных записей.

Чтобы осуществить поиск записей, нужно воспользоваться командой Data|Form Данные|Форма. В появившемся диалоговом окне нажать кнопку CriteriaКритерии и в нужных полях задать критерии поиска. Для перехода к записям, удовлетворяющим критерию, нажать кнопку Далее или Назад. Найденные записи можно легко изменять в полях формы.

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

Кнопки формы данных Вернуть и Удалить можно использовать для отмены внесенных в запись изменений и удаления записи соответственно.

Автофильтр

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

Чтобы включить автофильтр, нужно воспользоваться командой Data|Filter|AutoFilter  Данные|Фильтр|Автофильтр. Excel выведет кнопки со стрелками (кнопки автофильтра) рядом с каждым заголовков столбца.

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

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

Пользовательский автофильтр: в списке, который появляется при нажатии на кнопку автофильтра, выбрать вариант (Условие). На экране появится диалоговое окно (рис. 7), позволяющее задать условия отбора данных. Можно задать одно условие или два. В последнем случае условия могут связываться логическими операциями И или ИЛИ (рис. 8 9).

Первые 10. Этот метод имеет смысл только для полей с числовыми данными, в том числе и с датами. Чтобы воспользоваться этим методом, нужно в списке выбрать вариант (Первые 10…). В появившемся диалоговом окне следует указать, сколько наибольших или наименьших элементов отображать. Например, в БД имеется поле Оклад для хранения размера оклада сотрудника. Организация имеет возможность оказать материальную помощь пяти сотрудникам с наименьшим окладом. Требуется найти таких сотрудников. Для решения такой задачи можно воспользоваться методом отбора (Первые 10…) для поля Оклад: задать количество отображаемых записей 5 с наименьшим значением.


Чтобы снять фильтрацию с поля, нужно нажать на кнопку автофильтра и выбрать вариант (Все).

Можно одновременно накладывать условия на несколько полей. В этом случае условия отбора связаны операцией И, т.е. отображаться будут только те записи, для которых условия одновременно выполняются во всех полях, в соответствии со значениями в которых происходит фильтрация. Например, если на поле Количество наложить условие, как показано на рис. 8, а на поле Номенклатура  в соответствии с рис. 9, то будут отображены записи о продуктах Молоко и Кефир, закупленных в количестве от 50 до 100 пакетов.

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

Чтобы выключить автофильтр, нужно снова воспользоваться командой Data|Filter|AutoFilter  Данные|Фильтр|Автофильтр.


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

Расширенный фильтр является более гибким средством отбора записей из БД, чем автофильтр и позволяет задавать:

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

три и более условий для конкретного столбца,

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

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

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

Чтобы воспользоваться расширенным фильтром, нужно выбрать команду меню Data|Filter|Advanced Filter  Данные|Фильтр|Расширенный фильтр. На экране появится диалоговое окно (рис. 10).

В элементе управления Исходный диапазон нужно указать диапазон, в котором размещается список, в элементе управления Диапазон условий  диапазон критериев. По умолчанию в группе Обработка всегда установлен переключатель фильтровать список на месте. Если выбрать вариант скопировать результат в другое место, тогда будет задействован диапазон для извлечения. В этом случае становится доступным элемент управления Поместить результат в диапазон, в котором нужно задать диапазон, в который будут помещены отфильтрованные данные.

Флажок Только уникальные записи позволяет исключить повторяющиеся.

В том случае, когда список фильтруется на месте, не удовлетворяющие критерию строки скрываются. Чтобы вновь их отобразить (то есть снять действие фильтра), необходимо воспользоваться командой Data|Filter|Show All  Данные|Фильтр|Отобразить все.

Задание условий с использованием логической операции ИЛИ

Чтобы связать условия в диапазоне критериев логической операцией ИЛИ, нужно эти условия расположить в разных строках (см. рис. 11 12).

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

Пусть необходимо создать критерий отбора записей с использованием оператора И. Для этого условия в диапазоне критериев нужно расположить в одной строке (см. рис.13 15).



Задание условий с одновременным использованием логических операций И и ИЛИ

Расширенный фильтр позволяет задавать условия отбора записей с одновременным использованием логических операций И и ИЛИ. На рис. 16 диапазон критериев задает следующее условие: выбрать из списка записи о сотрудниках бухгалтерии с фамилиями на А и на В и о сотрудниках ПФО с фамилией на Б. В этом критерии операцией И связаны условия: фамилия на А И отдел бухгалтерия (условие 1), фамилия на Б И отдел ПФО, (условие 2), фамилия на В И отдел бухгалтерия (условие 3); операцией ИЛИ  условия: (условие 1) ИЛИ (условие 2) ИЛИ (условие 3).


Задание текстовых условий

Существует несколько правил задания текстовых условий.

Если необходимо найти все значения, которые начинаются с конкретной буквы, нужно в диапазоне критериев указать эту букву
(рис. 12, 16).

Символ «>» («<») означает: «Найти все значения, которые находятся по алфавиту после (до) введенного текстового значения. Например, при задании условия «>М» ниже заголовка Фамилия в диапазоне критериев отбираются строки, в которых фамилии начинаются с М, Н и т.д. до Я.

Формула =”=текст” означает: «Найти значения, которые точно совпадают со строкой символов текст». Например, если просто задать Иванов без формулы, то Excel отфильтрует строки с фамилиями Иванов, Иванова, Иванович, Ивановский и т.д. Условие =”=Иванов” позволит найти строки, в которых в поле Фамилия будет указана фамилия Иванов.

Кроме того, при задании текстовых условий можно использовать символы шаблона «*» и «?». Например, для того чтобы найти записи о сотрудниках, чьи имена состоят из шести букв и заканчиваются на «ей», нужно задать такой шаблон в диапазоне критериев под заголовком Имя: «????ей».

Использование вычисляемых условий

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

Правила применения вычисляемых условий:

1) заголовок над вычисляемым условием должен отличаться от заголовка любого из столбцов списка;

2) ссылки на ячейки, находящиеся вне списка, должны быть абсолютными;

3) ссылки на ячейки в списке должны быть относительными (это правило имеет исключение).

На рис. 17 приведен пример использования в расширенном фильтре вычисляемого условия. Необходимо получить записи о людях, родившихся в период с 01.01.1956 г. по 01.01.1960 г. Начальная и конечная даты периода заданы в ячейках В1 и В2. Диапазон критериев состоит только из вычисляемого условия с заголовком Вычисляемое условие (такого заголовка в исходном списке нет). В ячейке Е2 находится формула; на рис. 16 она отображена в примечании. Ссылки на ячейки В1 и В2 являются абсолютными, поскольку они расположены вне диапазона база данных. Ячейка Е5 является первой ячейкой в поле Дата рождения неотфильтрованного списка. Эта ячейка находится в диапазоне базы данных, поэтому ссылка на нее является относительной. В процессе фильтрации Excel по порядку подставляет каждый элемент вместо этого первого значения, то есть Excel сравнивает Е5, затем Е6, потом Е6 и т.д., пока не дойдет до конца списка. Если сравнение дает ложный результат, то соответствующая строка скрывается, как это получилось на рис. 16. Таким образом, отображенными останутся только те строки, для которых формула дает результат ИСТИНА.

На рис. 18 приведен еще один пример использования вычисляемого условия, демонстрирующий исключение из третьего правила применения вычисляемых условий. Здесь вычисляемое условие позволяет найти людей с окладом выше среднего оклада на предприятии. Ранее было оговорено, что ссылки на ячейки в диапазоне базы данных должны быть относительными, однако здесь ссылки на диапазон ячеек от Е5 до Е9 являются абсолютными. Это сделано для того, чтобы Excel использовал один и тот же диапазон ячеек на каждом шаге процесса фильтрации, то есть, чтобы Excel сравнивал Е5 со средним для Е5:Е9, затем Е6 со средним для Е5:Е9 и т.д.

Подведение промежуточных итогов

Команда Data|Subtotals… – Данные|Итоги… может быть использована для получения различной итоговой информации. Но прежде чем подводить итоги, нужно обязательно отсортировать список соответствующим образом (см. пример ниже). Для подведения итогов можно использовать различные функции: Сумма, Количество значений, Среднее, Максимум, Минимум, Произведение и др. Команда Data|Subtotals… – Данные|Итоги… создает промежуточные и общие итоги. При выводе промежуточных итогов Excel всегда создает структуру списка; с помощью символов структуры можно отобразить список с нужным уровнем детализации данных.


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

Шаг 1: исходный список нужно отсортировать по полю Отдел
(рис. 19).

Шаг 2: воспользоваться командой Data|Subtotals… – Данные|Итоги….
На экране появится диалоговое окно (рис. 20).

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

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

В списке Добавить итоги по: нужно указать, по какому (каким) полю (полям) подводить итоги.

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

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

Если установлен флажок Итоги под данными, то промежуточные и общие итоги будут расположены под данными, а если этот флажок сброшен то над данными.

На рис. 21 продемонстрирован результат подведения итогов.

Чтобы убрать все итоги, нужно вызвать окно Промежуточные итоги командой Data|Subtotals… – Данные|Итоги… и воспользоваться кнопкой Убрать все (рис. 20).

Функции для анализа списка

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

Функции СЧЕТЕСЛИ и СУММЕСЛИ

Функции COUNTIF  СЧЕТЕСЛИ и SUMIF  СУММЕСЛИ предназначены для работы со списками. Они проще в использовании, чем другие функции, используемые для анализа списка, поскольку позволяют задавать условия непосредственно в формуле. Но в этих функциях можно использовать только простые условия сравнения.

Функция COUNTIF  СЧЕТЕСЛИ имеет синтаксис

=COUNTIF(range; criteria)

=СЧЕТЕСЛИ(диапазон; условие).

Аргумент диапазон задает диапазон, в котором подсчитывается количество значений, удовлетворяющих критерию. Критерий задается вторым аргументом условие, представляющим собой текстовое значение. Эта функция относится к категории статистических функций.

Пример. 

1. Необходимо подсчитать количество сотрудников с именем Иван (рис. 22). Для решения задачи понадобится формула: =СЧЕТЕСЛИ(В2:В7;”Иван”). Ответ: 3.

2. Требуется определить число сотрудников, у которых оклад не превышает 2000р. (рис.22). Задачу можно решить с помощью формулы: =СЧЕТЕСЛИ(Е2:Е7;”<=4500”). Ответ: 1.


Функция
 SUMIF  СУММЕСЛИ имеет синтаксис

=SUMIF(range; criteria; sum_ range)

=СУММЕСЛИ(диапазон; условие; диапазон_суммирования).

Первые два аргумента диапазон и условие используются так же, как и в функции СЧЕТЕСЛИ. Аргумент диапазон_суммирования задает диапазон суммируемых значений. Функция относится к категории математических функций.

Пример.

Пусть необходимо подсчитать сумму окладов всех сотрудников с именем Иван. Первый аргумент функции (рис. 22) задает диапазон В2:В7, в котором осуществляется проверка условия (второй аргумент) «имя должно быть Иван» (на рисунке выполнение условия выделено жирным шрифтом). Если условие выполняется, то значение из диапазона Е2:Е7 (третий аргумент) добавляется к итоговой сумме (суммируемые значения обведены рамкой).

Функции баз данных

Функции баз данных имеют обобщенное название D-function (Д-функции) и оперируют только с элементами диапазона, которые удовлетворяют заданным условиям.

У всех функций баз данных один и тот же синтаксис:

=Dfunction(data_base; field; criteria)

=Дфункция(база_данных; поле; критерий).

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

В табл. 1 приведено краткое описание функций баз данных.


Таблица 1. Функции баз данных

Функция

Описание

DAVERAGE

ДСРЗНАЧ

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

DCOUNT

БСЧЕТ

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

DCOUNTA

БСЧЕТА

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

DGET

БИЗВЛЕЧЬ

Извлекает отдельное значение из столбца списка или базы данных, которое удовлетворяет заданным условиям. Если такой ячейки не обнаружено, возвращается значение #ЗНАЧ!. Если заданным условиям удовлетворяют несколько ячеек, то возвращается ошибочное значение #ЧИСЛО!

DMAX

ДМАКС

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

DMIN

ДМИН

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

DPRODUCT

БДПРОИЗВЕД

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

DSUM

БДСУММ

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

DSTDEV

ДСТАНДОТКЛ

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

DSTDEVP

ДСТАНДОТКЛП

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

DVAR

БДДИСП

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

DVARP

БДДИСПП

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


Пример.

Необходимо вычислить средний оклад у работников бухгалтерии с именем Иван. На рис. 23 продемонстрировано решение этой задачи.

Проверка вводимых значений

MS Excel предлагает специальное средство, позволяющее проверять, удовлетворяют ли заданным условиям вводимые в список значения. Можно задать допустимый тип данных (например, целые числа, даты, время или текст), диапазон разрешенных значений (например, целые числа от 1 до 31), указать перечень разрешенных значений (например, названия отделов предприятия). Следует отметить, что проверке подвергаются только значения, вводимые пользователем непосредственно в ячейки. Поэтому список может содержать некорректные данные, если они оказались там в результате операций копирования и вставки.

Чтобы задать условия проверки данных, нужно выделить диапазон ячеек, к которому должны применяться эти условия, затем воспользоваться командой Data|Validation… – Данные|Проверка…. На экране появится окно диалога Проверка вводимых значений, содержащее три вкладки: SettingsПараметры, Input MessageСообщение для ввода, Error Allert Сообщение об ошибке.

Задание типа данных и допустимых значений

Вкладка SettingsПараметры позволяет задать тип и интервал значений, которые разрешается вводить. Тип значений выбирается из списка Тип данных:. Интервал значений задается с помощью списка Значение: и поля/полей ввода. Если выбрать в списке Значение операцию между или вне, то полей будет два, для остальных операций, как и на
рис. 24,
одно. Надписи, сопровождающие поля, будут меняться в зависимости от выбранной операции.

Чтобы задать список допустимых значений, его нужно сначала сформировать на рабочем листе, а потом в раскрывающемся списке Тип данных выбрать вариант Список (рис. 25) и в поле Источник указать диапазон, в котором хранится список допустимых значений. Можно не создавать список на рабочем листе. Тогда значения для раскрывающего списка нужно указать в поле Источник, отделяя их друг от друга точкой с запятой:

Бухгалтерия; ПФО; ОК; Цех №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 выводит стандартное сообщение об ошибке и предлагает повторить или отменить ввод (в случае отмены восстанавливается прежнее содержимое ячейки). Вместо стандартного сообщения можно задать пользовательское. Для этого на вкладке Сообщение об ошибке (рис. 26) диалогового окна Проверка вводимых значений нужно ввести заголовок и текст сообщения.

Кроме того, в раскрывающемся списке Вид можно выбрать тип сообщения об ошибке: Останов, Предупреждение, Сообщение. Эти варианты отличаются значками, которые выводятся рядом с текстом сообщения, а также набором кнопок. Сообщение типа Останов позволяет повторить либо отменить ввод. Сообщение типа Предупреждение дает возможность наряду с этим сохранить введенное некорректное значение. Если выбран вариант Сообщение, то пользователь сможет сохранить введенное значение или отменить ввод, но ему не будет позволено повторить ввод.


Рис.
1. Элементы списка или БД в MS Excel

Строка заголовков

Имя поля

Поле

Запись

Фамилия

Имя

Название

Издательство

Год выпуска

Иванов

Иван

Excel 5.0

BHV

1994

Сидоров

Сергей

Excel 5.0

BHV

1995

Поместить результат в диапазон:

Рис. 3. Задание параметров сортировки

Рис. 5. Пример сортировки по пяти полям

Рис. 4. Сортировка в особом порядке

Рис. 14. Пример использования операции И в расширенном фильтре: найти информацию о сотрудниках ПФО, родившихся не раньше 02.01.1960

Отфильтрованный список

Диапазон критериев

Отфильтрованный список

Диапазон критериев

Рис. 7. Пользовательский автофильтр

Выбор значения

Выбор оператора

Рис. 9. Условие для отображения информации о молоке и кефире

EMBED PBrush  

Рис. 8. Условия для отображения записей со значением в поле Количество из диапазона от 50 до 100

Отфильтрованный список

Диапазон критериев

EMBED PBrush  

Рис. 11. Пример использования операции ИЛИ в расширенном фильтре: отобрать записи о людях с именем Иван или с отчеством Иванович

Рис. 13. Пример использования операции И в расширенном фильтре: отобразить информацию о сотрудниках с именем Иван, работающих в бухгалтерии

Отфильтрованный список

Диапазон критериев

EMBED PBrush  

Отфильтрованный список

Отфильтрованный список

Рис. 12. Пример использования операции ИЛИ в расширенном фильтре: получить информацию о людях, чьи фамилии начинаются либо на букву А, либо на Б, либо на В

Рис.10. Использование расширенного фильтра

Отфильтрованный список

Диапазон критериев

EMBED PBrush  

Рис. 16. Одновременное использование операций И и ИЛИ в расширенном фильтре

Диапазон критериев

EMBED PBrush  

Рис. 17. Пример использования вычисляемого условия в расширенном фильтре

Диапазон критериев

EMBED PBrush  

Рис. 18. Пример использования вычисляемого условия в расширенном фильтре

EMBED PBrush  

Рис. 19. Сортировка списка по полю Отдел

Рис. 20. Подсчет сумм окладов сотрудников для каждого отдела

Общий итог

Промежуточный итог

Структура списка

EMBED PBrush  

Рис. 21. Пример подведения итогов

EMBED PBrush  

Рис. 22. Использование функции СУММЕСЛИ

Диапазон критериев

Рис. 23. Пример использования функции баз данных

EMBED PBrush  

EMBED PBrush  

Рис. 24. Пример определения типа и интервала вводимых значений

Рис. 25. Пример задания списка допустимых значений

EMBED PBrush  

EMBED PBrush  

Рис. 2. Автоматическая форма для ввода данных
в список
Excel

Рис. 26. Задание сообщения об ошибке

в) поиск на основе множественного критерия

EMBED PBrush  

б) поиск близкого соответствия

EMBED PBrush  

а) поиск точного соответствия

Рис. 6. Задание критериев в форме данных

Рис. 15. Пример использования операции И в расширенном фильтре: найти сотрудников, дата рождения которых находится в промежутке с 01.01.1956 г. по 31.12.1961 г. включительно

Отфильтрованный список

Диапазон критериев

EMBED PBrush  


 

А также другие работы, которые могут Вас заинтересовать

76907. Глазодвигательный, блоковый, отводящий нервы 181.24 KB
  Глазодвигательный блоковый и отводящий нервы связаны в одну группу по причинам: единого происхождения и развития обусловленного передними головными миотомами из которых возникают мышцы глазного яблока; схожим строением волокон которые имеют нисходящее направление и по функции являются двигательными; общностью иннервации глазных мышц. Корешки нерва выходят на основании головного мозга в межножковой ямке по медиальной поверхности ножек мозга где для них имеется борозда. Ресничный узел лежит на латеральной полуокружности влагалища...
76908. Тройничный нерв. V пара черепных нервов, ее ветви, топография и области иннервации 185.93 KB
  V пара тройничные нервы правый и левый смешанные: чувствительные двигательные вегетативные. Нервы развиваются вместе с производными первой висцеральной дуги челюстями и жевательными мышцами кожей лица слизистой полости носа и рта. Ствол тройничного нерва возникает при объединении чувствительного и двигательного корешков на выходе из мозга.
76909. Лицевой нерв, его топография, ветви и области иннервации 181.44 KB
  VII пара включает два нерва лицевой и промежуточный смешанные нервы двигательные парасимпатические и чувствительные. Промежуточный нерв нередко обозначают как XIII пару и тогда в VII паре остается только лицевой – двигательный нерв. Нерв выходит из мозга в поперечной борозде между мостом и продолговатым мозгом латерально от оливы направляясь по задней черепной яме к внутреннему слуховому проходу куда вступает вместе с VIII парой.
76910. Преддверно-улитковый нерв. VIII пара черепных нервов и топография ее ядер. Проводящие пути органов слуха и равновесия 183.89 KB
  Преддверная и улитковая части VIII пары объединяются во внутреннем слуховом проходе и направляются через заднюю черепную яму к мозговому стволу к его поперечной борозде между мостом и продолговатым мозгом где латеральнее лицевого и промежуточного нервов входят во внутрь моста и заканчиваются синапсами на ядрах вестибулярного поля моста. Вестибулярное поле находится в латеральных углах ромбовидной ямки на него проецируются два улитковых и четыре вестибулярных ядра залегающих в вентролатеральных отделах моста. Улитковые ядра: переднее и...
76911. Языкоглоточный нерв. IX пара черепных нервов, их ядра, топография и области иннервации 180.17 KB
  IX пара языкоглоточные нервы смешанные развиваются из заднего мозгового пузыря. Корешки нерва 45 выходят позади оливы продолговатого мозга и сливаются в короткий ствол. Чувствительные узлы нерва: верхний в яремном отверстии нижний в области каменистой ямки височной кости содержат псевдоуниполярные нейроны центральные отростки которых формируют чувствительный корешок нерва.
76912. Блуждающий нерв, его ядра, их топография; ветви и области иннервации 181.14 KB
  Краниальная часть нерва отдает ветви: менингиальную веточку для твердой мозговой оболочки в задней черепной яме; ушную ветвь которая через сосцевидный каналец и барабаннососцевидную щель подходит к коже наружного слухового прохода и ушной раковине. Ветви шейной части: глоточные к глоточному сплетению и через него к слизистой мышцамконстрикторам глотки мышцам мягкого неба кроме напряжителя из тройничного нерва шейные верхние сердечные ветви к сердечным сплетениям гортанные верхние нервы к перстнещитовидной мышце наружная...
76913. Прибавочный и подъязычный нервы 181.56 KB
  Обе пары XI XII по выходе из черепа идут между внутренней яремной веной и внутренней сонной артерией и ложатся под заднее брюшко двубрюшной мышцы. Из черепа ствол нерва выходит через яремное отверстие вместе с IX X парами и внутренней яремной веной занимая при этом латеральное положение. Внутренняя веточка для соединения с блуждающим нервом; наружная ветвь – для трапециевидной и грудиноключичнососцевидной мышц; Наружная ветвь проходит между внутренней яремной веной и внутренней сонной артерией а затем уходит под заднее брюшко...
76914. Вегетативная, автономная нервная система. Вегетативная часть нервной системы, ее деление и характеристика отделов 185.72 KB
  В надсегментарных вегетативных центрах которые располагаются в коре полушарий базальных ядрах мозжечке различают: центры чувствительные по восприятию внутренней рецепции; центры двигательные по координации гладкомышечных и сердечных сокращений в органах и сосудах. Подкорковые вегетативные центры Полосатое тело центры терморегуляции слюно и слезоотделения образования слизи. Ретикулярная формация ствола мозга – зрачковый рефлекс центры дыхания сердечный сосудистый глотания и рвоты и другие регуляции обмена веществ и...
76915. Парасимпатическая часть ВНС 187.66 KB
  Краниальная часть парасимпатических ядер включает мезэнцефалические добавочное и срединное ядра глазодвигательного нерва которые лежат в сером веществе дна водопровода на уровне верхних холмиков. Центральные нейроны ядер направляют свои преганглионарные отростки в составе глазодвигательного нерва к ресничному узлу где они переключаются на периферические 2ые нейроны. Дорсальное ядро блуждающего нерва направляет преганглионарные волокна в интрамуральные органные парасимпатические узлы органов иннервируемых Х парой где они прерываются....