Работа со списками в Excel'97.

• Сортировка списка.
• Отбор данных на основе фильтра.
• Подведение итогов.
• Ограничение вводимых данных.

Сортировка списка.

Чтобы отсортировать список, выделите любую ячейку в списке, а затем из меню Data (Данные) выберите команду Sort (Сортировка). На экране появится диалоговое окно Sort (Сортировка диапазона).

1. Щелкнув на кнопке со стрелкой в группе Sort by (Сортировать по), Вы увидите список заглавных строк. Выберите соответствующий пункт из этого списка, и Excel расставит все записи в соответствии с порядком значений, помещенных в ячейки данного столбца.
2. Установки Ascending (по возрастанию) и Descending (по убыванию) определяют порядок сортировки. Вариант Ascending (по возрастанию) означает сортировку по возрастающим числам (начиная с самого малого и кончая самым большим), по буквам от А до Я и по датам и времени - начиная с самых ранних значений и до самых поздних. При сортировке Descending (по убыванию) значения располагаются в обратном порядке.
3. В поле Then by (Затем по) выбирается установка для вторичной сортировки.

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

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

Отбор данных на основе фильтра.

Выберите из меню Data (Данные) последовательно команды Filter (Фильтр), AutoFilter (Автофильтр). После этого в заглавной строке каждого столбца появятся маленькие кнопки-стрелки. Щелчок на стрелке приведет к появлению списка, содержащего перечень всех элементов этого столбца. Кроме этого в списке находятся команды, позволяющие показать все элементы списка, первые десять или задать свой собственный критерий.

Найдите необходимое поле, щелкните на стрелке, расположенной рядом с этим полем, и в появившемся списке выберите какой-нибудь пункт. Все записи списка, за исключением относящихся к этому пункту, станут скрытыми. Для восстановления всего списка необходимо из меню Data (Данные) выбрать команды Filter (Фильтр), Show All (Показать все).

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

• Куда поместить отфильтрованный список: фильтровать на месте или скопировать результат в другое место (в этом случае необходимо указать диапазон, в который следует поместить отфильтрованный список).
• List range (Исходный диапазон) - диапазон Вашего списка.
• Criteria range (Диапазон условий) - диапазон ячеек, содержащий набор условий поиска. Диапазон условий состоит из строки подписей условий и одной или нескольких строк самих условий. Условия, перечисленные в одной строке диапазона условий должны выполняться одновременно, в разных - хотя бы одно из условий должно быть удовлетворено.

Поставив флажок Unique records only (Только уникальные записи), Вы предотвратите вывод повторяющихся записей списка.

В приведенном примере отфильтрованный список будет содержать записи, в которых:
Год меньше, чем 1970, но не меньше, чем 1960
или
поле ФИО содержит значение Собченко Н.Г..

Подведение итогов.

Вы можете подвести промежуточные итоги в базе данных Microsoft Excel, предварительно отсортировав список по столбцу, для которого необходимо подвести промежуточный итог. Для подведения итогов необходимо выбрать команду Subtotals (Итоги) из меню Data (Данные).

В диалоговом окне Subtotals (Промежуточные итоги) нужно указать следующее:

• Столбец, содержащий группы, по которым необходимо подвести итоги, из списка At each change in (При каждом изменении в). Это должен быть столбец, по которому проводилась сортировка списка.
• Функцию, необходимую для подведения итогов, из списка Use function (Операция).
• Столбцы, содержащие значения, по которым необходимо подвести итоги, в спискеAdd subtotal to (Добавить итоги по).

Вы можете также

• Replace current subtotals (Заменить текущие итоги), если они уже подводились раньше.
• Добавить Page break between groups (Конец страницы между группами).
• Подвести окончательные Summary below data (Итоги под данными).
• Remove All (Убрать все) промежуточные итоги.

Ограничение вводимых данных.

В Microsoft Excel существует возможность ограничения диапазона вводимых данных. Для этого Вы должны:

• Выделить ячейки, на которые требуется наложить ограничения.
• Выбрать из меню Data (Данные) команду Validation (Проверка), вкладка Settings (Параметры).
• Выбрать допустимый тип данных из списка Allow (Тип данных).
• Указать диапазон, в котором должно находиться вводимое значение или длина вводимого текста.

Флажок Ignore blank (Игнорировать пустые ячейки) позволяет не выводить сообщение об ошибке в том случае, если ячейка, на которую наложены ограничения, пуста. Для вывода на экран подсказки при переходе к ячейке с ограничениями введите текст сообщений на вкладке Input Message (Сообщение для ввода). Текст сообщения, появляющегося в том случае, если условие не удовлетворено, введите на вкладке Error Alert (Сообщение об ошибке). Основные функции для работы со списками.

• Функции для работы со ссылками и массивами.
• Функции для работы с базой данных.

Функции для работы со ссылками и массивами.

Если Вам необходимо найти какое-либо значение в таблице или определить ссылку на определенную ячейку, воспользуйтесь специальными встроенными функциями Microsoft Excel для работы со ссылками и массивами:

• АДРЕС - создает адрес ячейки в виде текста, используя номер строки и номер столбца;
• ВПР - ищет заданное значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы;
• ВЫБОР используется, чтобы выбрать одно значение из списка, в котором может быть до 29 значений;
• ГИПЕРССЫЛКА - создает ярлык, который открывает документ, расположенный в сети или в Интернет;
• ГПР - ищет заданное значение в верхней строке таблицы и возвращает значение в том же столбце из заданной строки таблицы;
• ДВССЫЛ - возвращает ссылку, заданную текстовой строкой (например, если эта функция ссылается на ячейку A1, содержимое которой представляет собой текст "B1", а в ячейке B1 хранится число 23, то функция вернет число 23);
• ИНДЕКС - возвращает значение или ссылку на значение из указанного интервала (на основании номера строки и номера столбца в интервале);
• ОБЛАСТИ - возвращает количество непрерывных областей в ссылке;
• ПОИСКПОЗ - возвращает относительное положение элемента массива;
• ПРОСМОТР - просматривает диапазон в поисках определенного значения и возвращает значение из другого столбца или строки;
• СМЕЩ - возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов;
• СТОЛБЕЦ - возвращает номер столбца по заданной ссылке;
• СТРОКА - возвращает номер строки по заданной ссылке;
• ТРАНСП - транспонирует массив значений;
• ЧИСЛСТОЛБ - возвращает количество столбцов в ссылке или массиве;
• ЧСТРОК - возвращает количество строк в ссылке или массиве.

Рассмотрим для примера две функции:

ВПР(искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Функция предназначена для поиска заданного значения в крайнем левом столбце таблицы и возврата значения в той же строке из указанного столбца таблицы.

ГПР(искомое_значение; инфо_таблица; номер_строки; интервальный_просмотр)
Функция предназначена для поиска заданного значения в первой строке таблицы и возврата значения в том же столбце из указанной строки таблицы.

Аргументы:

• Искомое_значение - значение, которое функция будет искать в первом столбце или первой строке массива. Искомое значение может быть значением, ссылкой или текстовой строкой.
• Инфо_таблица - таблица, в которой ищутся данные.
• Номер_столбца (строки) - номер столбца (строки) в массиве Инфо_таблица, из которого будет возвращаться соответствующее значение.
• Интервальный_просмотр - логическое значение, которое определяет, нужно ли, чтобы функция искала точное соответствие. Если этот аргумент - ИСТИНА или опущен, то возвращается приблизительное значение (наибольшее из значений первого столбца (строки), которые меньше требуемого), если аргумент - ЛОЖЬ, то функция ищет точное соответствие.

Предположим, что у Вас имеется база данных сотрудников. В колонке B находятся фамилии сотрудников, в колонке C - их имена. Если фамилия какого-либо сотрудника известна, то функция ВПР поможет узнать его имя:

ВПР("Казаков"; B3:C11; 2; ЛОЖЬ) = Антон, т.к. в первой колонке диапазона B3:C11 будет найдено значение в точности соответствующее первому аргументу (Казаков). После этого функция вернет содержимое ячейки, которая находится во 2-ой колонке этого диапазона в той же строке, что и первый аргумент.

ВПР("Казаковы";B3:C11;2;ЛОЖЬ) = #Н/Д, т.к. в первой колонке диапазона B3:C11 не найдено значение, в точности соответствующее первому аргументу (Казаковы).

ВПР("Казаковы";B3:C11;2;ИСТИНА) = Антон, т.к. значение Казаковы в первой колонке диапазона B3:C11 ищется не точно, а приблизительно.

Если известен порядковый номер сотрудника в списке, Вы можете узнать его фамилию с помощью функции ГПР:

ГПР("Фамилия"; B2:C11; 7;ЛОЖЬ) = Казаков.

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

Функции для работы с базой данных.

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

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

Аргументы:

• база_данных - интервал ячеек, задающий базу данных;
• поле - столбец, используемый функцией. Этот аргумент можно задать в виде текста в двойных кавычках (название поля) или как число (номер столбца в списке полей);
• критерий - диапазон условий, задающих условия для поиска. Ссылка на критерий может быть введена как интервал ячеек или как имя диапазона, например "Критерии". Диапазон условий должен быть введен отдельно от списка. Он состоит из строки подписей условий и одной или нескольких строк самих условий. Условия, перечисленные в одной строке диапазона условий должны выполняться одновременно, в разных - хотя бы одно из условий должно быть удовлетворено. Если Вы хотите выполнить операцию над целым столбцом, необходимо ввести пустую ячейку под названием столбца в диапазоне критерия.

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

• БСЧЁТ - подсчитывает количество ячеек, содержащих числа;
• БСЧЁТА - подсчитывает количество непустых ячеек;
• ДМАКС - ищет максимальное значение;
• ДМИН - ищет минимальное значение;
• БДСУММ - вычисляет сумму числовых значений;
• БДПРОИЗВЕД - перемножает числовые значения;
• ДСРЗНАЧ - считает среднее значение;
• ДСТАНДОТКЛ - оценивает стандартное отклонение;
• ДСТАНДОТКЛП - вычисляет стандартное отклонение по генеральной совокупности;
• БДДИСП - оценивает дисперсию;
• БДДИСПП - вычисляет дисперсию по генеральной совокупности;
• БИЗВЛЕЧЬ - ищет одну запись (если критерию удовлетворяют несколько записей, возвращается ошибка #ЧИСЛО!).

Рассмотрим пример:

• В диапазоне A4:C15 находится база данных деталей, поступивших на склад.
• Диапазон критериев, хранящийся в интервале A1:C2, предписывает ограничить область просмотра только болтами, поступившими на склад с 1990 по 1995 год.
• Ячейка B10 отформатирована как текст (обратите внимание, что значение в этой ячейке выровнено по левому краю, в то время как все остальные значения в этом столбце - по правому).

Тогда различные функции для работы с базыми данных будут выглядеть следующим образом:

БСЧЁТ(A14:C15;2;A1:C2) = 2, т.к. условию отбора удовлетворяют три записи, но одна из них содержит текстовое значение во втором столбце. Поскольку функция подсчитывает количество числовых значений именно во втором столбце, то возвращаемая функцией величина - 2.

БСЧЁТА(A14:C15;2;A1:C2) = 3, т.к. функция подсчитывает количество непустых ячеек во втором столбце, независимо от формата данных.

ДСРЗНАЧ(A14:C15;2;A1:C2) = 5750, т.к. функция работает только с числами, содержащимися во втором столбце, игнорируя текстовые значения.

БИЗВЛЕЧЬ(A14:C15;2;A1:C2) = #ЧИСЛО!. Функция возвращает ошибку, т.к. условию отбора удовлетворяют три записи.