ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 18.10.2024
Просмотров: 5
Скачиваний: 0
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
1
ЛАБОРАТОРНАЯ РАБОТА №13
Тема: Списки
По существу, список - это упорядоченный набор данных. Обычно список состоит из строки заголовков (описания данных) и строк данных, которые могут быть числовыми и текстовыми. Список можно считать табличной базой данных, чем он, в сущности, и является.
В Excel используется термин списокдля обозначения базы данных, хранящейся в рабочем листе, а термин база данных- для таблиц с информацией, которая находится во внешнем файле.
На рис.13.1 показан список, находящийся на рабочем листе. В первой строке этого списка расположены заголовки, а в следующих строках - данные.
Рисунок 13. 1. Список на рабочем листе
Список занимает шесть столбцов. Обратите внимание на несколько разных типов данных, содержащихся в этом списке: текст и числа (возможны, и даты). В столбце F находится формула, полученная на основе данных, введенных в столбцы D и Е.
Столбцы списка часто называют полями,а строки – записями. Исходя из принятой выше терминологии, можно сказать, что данный список содержит шесть полей (Сотрудник,
Месяц, Товар, Цена, Количество и Всего) и пятнадцать записей.
Размер списка ограничен размерами одного рабочего листа. Другими словами, список может иметь не больше 16384 столбцов и не больше 1048576 строк.
Что можно делать со списком
В Excel есть несколько средств, предназначенных для работы со списками. Они могут быть использованы для самых разных целей. Для одних пользователей список — это способ простого хранения набора данных (например, список клиентов), другие используют его для хранения таких данных, которые в конечном итоге должны быть включены в отчет.
Ниже приведены операции, которые обычно выполняются над списками.
2
Ввод данных в список.
Фильтрация списков для выборочного отображения строк (по определенному критерию).
Сортировка списка.
Вставка формул для подведения промежуточных итогов.
Создание формул для вычисления результатов в списке, отфильтрованном по определенным критериям.
Создание итоговой сводной таблицы на основе данных списка.
Планирование списка
Excel - довольно гибкая система в отношении хранения информации в списках, однако получить начальное представление о том, как организовать эту информацию, нелегко. Вот основные рекомендации, о которых следует помнить, создавая списки:
Помещайте заголовки (по одному для каждого столбца) в первую строку списка, которая называется строкой заголовков. Если заголовки длинные, используйте текстовый формат с переносом слов, тогда вам не придется расширять столбцы.
В каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст.
Можно применять формулы, использующие значения из других полей этой же записи. Если формула ссылается на ячейку, расположенную вне списка, сделайте ссылку на эту ячейку абсолютной, иначе результаты при сортировке списка могут быть непредсказуемыми.
Не используйте пустых строк в списке. При проведении операций над списком Excel определяет его границы автоматически, при этом пустая строка означает конец списка.
Рекомендуется помещать список на отдельный лист. Если на этот лист надо поместить еще другую информацию, помещайте ее ниже или выше списка. Не используйте для этого ячейки слева или справа от списка.
Используйте команду Закрепить области на вкладке Вид в группе Окно, чтобы заголовки были всегда видны при прокручивании листа списка
Старайтесь предварительно отформатировать весь столбец, чтобы данные всегда имели один и тот же формат. Например, если столбец содержит даты, выберите необходимый формат для отображения дат в этом столбце.
Для того чтобы в столбец списка заносились только определенные данные, с помощью команды Работа с данными/Проверка данных можно задать проверку вводимых данных.
Одной из наиболее привлекательных особенностей электронных таблиц является возможность простого структурирования таблиц. Это, конечно же, относится и к спискам.
Например, если к созданному списку необходимо добавить еще одно поле, то можно просто вставить новый столбец, дать ему заголовок – и ваш список расширен.
Ввод данных в список
Данные можно ввести в список тремя способами:
Вручную, используя стандартные методы ввода данных.
Импортировать или скопировать данные из другого файла.
3
Использовать форму ввода.
Ввод данных вручную
В операции ввода данных в список нет ничего особенного. Перемещаясь по таблице, вы вводите данные в нужные ячейки.
В Excel есть два средства, которые помогают избежать выполнения скучной операции ввода однообразных данных.
1.
Автозаполнение. Когда вы начинаете вводить данные, программа просматривает столбец, чтобы выяснить, сможет ли она узнать то, что вы набираете. Если
Excel находит закономерность, то заполняет остаток ячеек автоматически. Чтобы завершить ввод данных, нажмите клавишу
диалогового окна Параметры.
2.
Выбор из списка. Щелкните правой кнопкой мыши на ячейке и выберите из появившегося контекстного меню команду Выбрать из раскрывающегося списка. Excel выведет список со всеми элементами, находящимися в столбце (рис.13.2). Выберите из списка нужный элемент, и он появится в ячейке (при этом ничего набирать не нужно).
Рисунок 13. 2. Выбор из списка
Ввод данных с помощью формы ввода
В Excel можно вводить данные с помощью формы. Чтобы вывести на экран форму для ввода данных, поместите табличный курсор в каком-нибудь месте списка и выполните команду Данные/Форма. Если такой кнопки нет, то нужно включить Настройка панели
4
быстрого доступа – Другие команды – Выбрать команды из – Все команды – Форма –
Добавить – ОК – кнопка появится на панели быстрого доступа. Excel определит размер вашего списка и выведет диалоговое окно, в котором будет находиться каждое поле списка
(рис.13.3).
Рисунок 13. 3. Поле списка
Поля, содержащие формулу, в форме ввода отображаются, но их значения нельзя изменить.
Когда появится форма, в ней будет показана первая запись списка. Обратите внимание, что индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в списке.
Чтобы ввести новую запись, щелкните на кнопке Добавить, таким образом в форме очистятся все поля.
После этого можно вводить новую информацию в соответствующие поля.
Используйте клавишу <ТаЬ> или
После щелчка на кнопке Добавить или Закрыть введенные данные появятся в конце списка. Можно также нажать клавишу
Добавить. Если список содержит формулы, то они автоматически появятся в новых записях.
Форму ввода можно использовать не только для ввода данных. Она позволяет редактировать существующие записи, просматривать их, удалять и выборочно отображать записи по определенному критерию.
Форма ввода содержит несколько кнопок:
Удалить. Удаляет текущую запись.
Вернуть. Отменяет все внесенные в текущую запись изменения. Эта кнопка работает до тех пор, пока вы не щелкнете на кнопке Добавить.
Назад. Осуществляется переход к предыдущей записи списка. Если установлен критерий отбора, то произойдет переход к предыдущей записи, удовлетворяющей данному критерию.
Далее. Осуществляется переход к следующей записи списка. Если установлен критерий отбора, то произойдет переход к следующей записи, удовлетворяющей данному критерию.
5
Критерии. Очищает поля для ввода критерия, по которому будут отбираться записи. Например, чтобы найти все записи, в которых значение, находящееся в поле
Количество, больше 10, введите >10 в поле Количество формы. После этого можно использовать кнопки Назад и Далее, чтобы найти подходящую запись.
Закрыть. Закрывает форму и записывает введенные данные (конечно, если вы их ввели) в рабочий лист.
Фильтрация списков
Фильтрация списка – это процесс сокрытия всех строк, кроме тех, которые удовлетворяют определенным критериям. Например, если у вас есть список клиентов, его можно отфильтровать так, чтобы видеть фамилии только тех из них, которые живут в определенном городе. Фильтрация - весьма распространенная и очень полезная операция.
В Excel списки можно фильтровать двумя способами.
Автофильтр используется для фильтрации по простым критериям.
Расширенный фильтр применяется для фильтрации по более сложным критериям.
Автоматическая фильтрация
Чтобы автоматически отфильтровать список, сначала установите табличный курсор на одну из его ячеек. Затем выполните команду фильтр, которая находится на вкладке
Данные в группе Сортировка и Фильтр. Excel проанализирует список и добавит в строку заголовков полей кнопки раскрывающихся списков (кнопки автофильтра), как показано на рисунке 13.4.
Рисунок 13. 4. Автофильтр
Когда вы отфильтруете список, в строке состояния появится сообщение о том, сколько строк отобрано. Кроме того, изменится значок кнопки раскрывающегося списка, чтобы напомнить вам, что список отфильтрован по значениям, содержащимся в этом столбце.
6
Автоматическая фильтрация имеет ограничения. В раскрывающемся списке появляются только первых 999 различных значений. Если число элементов в вашем списке превышает указанный предел, можно использовать средства расширенной фильтрации, которые будут описаны ниже.
Для отмены режима Автофильтр и удаления кнопок раскрывающихся списков в именах полей выберите команду Данные – Фильтр повторно. В результате будет удален флажок у пункта меню Автофильтр и список вернется в обычное состояние.
Не забывайте, что если у вас есть формулы, в которых используются ссылки на ячейки отфильтрованного списка, то после выполнения фильтрации значение, вычисляемое по таким формулам, не изменяется. Другими словами, в формулах не могут быть использованы лишь значения из отфильтрованных ячеек. Например, если в ячейку введена формула суммирования значений в столбце С, то после фильтрации будет продолжаться суммирование всех значений в столбце С, а не только элементов в видимых строках.
Автоматическая фильтрация по значениям в нескольких столбцах
Предположим, вам необходимо просмотреть записи, относящиеся к продажам в феврале, выполненным сотрудником Кипеловым. Сначала включите режим Фильтр. Затем щелкните на кнопке раскрывающегося списка в поле Месяц и выберите Февраль. Из списка будут отобраны записи, в которых поле Месяц имеет значение Февраль. Затем щелкните на кнопке раскрывающегося списка в поле Сотрудник и выберите Кипелов. Список будет отфильтрован еще раз — по значениям в двух столбцах.
Можно отфильтровать список по любому количеству столбцов.
Пользовательский автофильтр
Обычно автоматическая фильтрация заключается в выборе одного значения в одном или нескольких столбцах. Если выбрать опцию Условие в раскрывающемся списке, то можно осуществить фильтрацию более гибким способом. При выборе опции Числовые
фильтры – Настраиваемый фильтр появится диалоговое окно Пользовательский
автофильтр (рис.13.5). Это окно позволяет фильтровать списки с использованием нескольких критериев.
Рисунок 13. 5. Пользовательский автофильтр
Значения больше или меньше установленного. Например, можно выбрать записи, указывающие на объемы продаж, превышающие 1 000.
Значения в интервале. Например, отобрать все записи, указывающие на объемы продаж, превышающие 1 000 и не превышающие 2 000.
Два отдельных значения. Например, отобрать записи, в которых находится информация об объеме продаж в январе или феврале.
7
Можно использовать символы подстановки “*” и "?", чтобы отфильтровать список более гибким способом. Например, чтобы вывести на экран записи только о тех сотрудниках, фамилии которых начинаются с буквы К, используйте шаблон К*.
Пользовательский автофильтр может быть весьма полезным средством, но он имеет определенные ограничения. Например, с его помощью нельзя отфильтровать по трем и более значениям (например, вывести записи, в которых представлена информация о продажах только за январь, февраль или март). Для выполнения задач такого типа необходимо использовать средство расширенной фильтрации.
Наложение условия по списку
Иногда необходимо отфильтровать числовые поля так, чтобы показать на экране наибольшие или наименьшие значения. Например, вам нужно определить список пяти сотрудников, продавших наибольшее количество товара. В этом случае следует использовать опцию Первые 10, которая расположена в группе Числовые фильтры.
При выборе опции Первые 10 появится диалоговое окно Наложение условия по
списку (рис.13.6).
Рисунок 13. 6. Наложение условия по списку
С его помощью вы можете выбрать наибольшие или наименьшие элементы из списка, а также указать их количество. Например, если вам необходимо получить список пяти сотрудников, сделавших наибольшее количество продаж, выберите опцию
Наибольших в списке поля Количество и задайте число 5. Таким образом, вы отфильтруете список, и в нем будут показаны пять строк с наибольшими значениями этого поля. В этом диалоговом окне можно также выбрать опцию % от количества элементов. Например, можно отобрать 5% наибольших элементов поля.
Построение диаграммы по данным отфильтрованного списка
Для создания некоторых интересных многоцелевых диаграмм используются данные отфильтрованного списка. Этот способ весьма эффективен, так как только диаграмма позволяет наглядно увидеть картину, представленную теми или иными данными. При изменении критерия автофильтра диаграмма автоматически изменяется и показывает значения, взятые только из "видимых" ячеек.
Расширенная фильтрация
Часто для обработки списка бывает вполне достаточно автофильтра. Но если необходимо выполнить операцию, выходящую за рамки возможностей автофильтра, то придется прибегнуть к помощи средств расширенной фильтрации. Расширенный фильтр гораздо более гибкий, чем автофильтр, однако при его использовании требуется выполнять
8 больше подготовительных действий. Расширенный фильтр позволяет выполнить следующее:
Определить более сложный критерий фильтрации.
Установить вычисляемый критерий фильтрации.
Перемещать копии строк, отвечающих определенному критерию, в другое место.
Установка диапазона критериев
Прежде чем использовать средство расширенной фильтрации, необходимо задать
диапазон критериев. Это специально отведенная область рабочего листа, отвечающая определенным требованиям. Диапазон критериев используется Excel для фильтрации списка и должен отвечать следующим требованиям:
Должен состоять по крайней мере из двух строк, первая из которых должна содержать все или некоторые названия полей списка.
Остальные строки должны содержать критерии фильтрации.
Хотя вы можете отвести для диапазона критериев любое место на рабочем листе, желательно не задействовать при этом строки, используемые списком.
Поскольку некоторые из строк списка будут скрыты при фильтрации, может оказаться, что ваш диапазон критериев стал невидимым после фильтрации. Поэтому размещайте диапазон критериев над или под списком. Поля, не используемые при фильтрации, можно не помещать в диапазон критериев (рис. 13.7).
Рисунок 13. 7. Расширенный фильтр
В примере диапазон критериев содержит только одну строку критериев. Поля в каждой строке диапазона критериев (исключая заглавную строку) соединены оператором
9
И. Поэтому в отфильтрованном списке показаны только те строки, у которых одновременно поле Сотрудник содержит фамилии, начинающиеся с буквы "К", и в поле Товар содержится значение «товар А».
Чтобы выполнить фильтрацию, выберите команду Дополнительно, которая находится в группе Фильтр во вкладке Данные. Появится диалоговое окно Расширенный
фильтр (рис.13.7).
Определите диапазон списка и диапазон критериев и убедитесь, что установлен переключатель Фильтровать список на месте. Щелкните на кнопке ОК, и список будет отфильтрован по заданным критериям.
Множественный критерий отбора
Если в диапазоне критериев используется несколько строк, критерии в каждой строке соединены оператором ИЛИ. Например, можно отфильтровать строки списка так, чтобы они отвечали следующим требованиям:
В поле Цена содержатся значения <200 ИЛИ в поле Цена содержатся значения >300
(рис.13.8).
Диапазон критериев может иметь любое количество строк, соединенных одна с другой оператором ИЛИ.
Рисунок 13. 8. Множественный критерий отбора
Текстовые и числовые критерии
При фильтрации можно использовать сравнения с числом или строковой (текстовой) константой с помощью операторов, таких, как равно (=), больше чем (>), не равно (<>) и т.д.
В таблице 13.1 представлен список операторов сравнения, которые можно использовать в текстовых или числовых критериях.
10
Таблица 13. 1. Операторы сравнения
Оператор
Тип сравнения
=
Равно
>
Больше чем
>=
Больше либо равно
<
Меньше
<=
Меньше либо равно
<>
Не равно
В таблице 13.2 показаны примеры строковых критериев.
Таблица 13. 2. Примеры строковых критериев
Критерий
Действие
>K
Слова, начинающиеся с букв от Л до Я
<>C
Все слова, кроме начинающихся с буквы С
="Январь"
Все слова "Январь"
См*
Слова, начинающиеся с букв "См" с*с
Слова, начинающиеся с буквы "с" и заканчивающиеся буквой "с" с?c
Слова из трех букв, начинающиеся с буквы "с" и заканчивающиеся буквой "с"
В текстовых сравнениях не различаются прописные и строчные буквы. Например, критерий си* соответствует как фамилии Сидоров, так и слову сильный.
Вычисляемые критерии
Использование вычисляемых критериев может сделать операцию фильтрации более гибкой. Вычисляемый критерий основывается на одном или нескольких вычислениях и, по существу, вычисляет для списка новое поле. Поэтому следует помещать новое название поля в первую строку диапазона критериев.
Предположим, что список содержит информацию о проектах: номер проекта, дата начала проекта, дата окончания и т.п. Если вы хотите увидеть информацию только о проектах, длительность которых не превышает двух дней, необходимо использовать формулу
=Дата окончания-Дата начала<=2 (рис. 13.9).
Эта формула возвращает логическое значение истина или ложь формула ссылается на ячейки с данными, расположенными в первой строке списка, следующей послестроки заголовков. Когда список будет отфильтрован, в нем будут отображены только те проекты, продолжительность которых меньше либо равна 2 дням.
11
Рисунок 13. 9. Вычисляемый критерий
Обратите внимание, что можно получить тот же результат без использования вычисляемого критерия. Нужно просто прибавить к списку новый столбец с формулой, по которой вычисляется продолжительность проекта. Однако использование вычисляемого критерия избавляет вас от необходимости добавлять лишний столбец в список.
При использовании вычисляемых критериев рекомендуется помнить некоторые правила.
Не используйте заголовки полей списка в диапазоне критериев для вычисляемого значения. Создайте новый заголовок или просто оставьте пустую ячейку.
Можно использовать любое количество вычисляемых критериев, а также сочетания вычисляемых критериев с невычисляемыми.
Не обращайте внимания на значения, возвращаемые формулами в диапазоне критериев. Они ссылаются на первую строку списка.
Если ваша вычисляемая формула ссылается на значения вне списка, используйте абсолютные, а не относительные ссылки. Например, вместо С1 используйте ссылку $С$1.
При создании формул вычисляемых критериев используйте первую строку списка " (не строку заголовков!). Используйте относительные, а не абсолютные ссылки.
Например, вместо $С$5 используйте ссылку С5.
Другие возможности расширенной фильтрации
В диалоговом окне Расширенный фильтр представлены еще две опции, которые рассматриваются дальше.
Скопировать результат в другое место.
Только уникальные записи.
Копирование отобранных строк
Если вы выберете переключатель Скопировать результат в другое место в диалоговом окне Расширенный фильтр, то отобранные строки будут скопированы в другое место активного рабочего листа или на другой лист. Место определяется в поле
Поместить результат в диапазон. Обратите внимание, что при использовании этой опции сам список не фильтруется.
Отображение только уникальных строк
12
При выборе опции Только уникальные записи все одинаковые строки, отвечающие определенному критерию, будут скрыты. Если вы не установили диапазон критериев, то в списке будут скрыты все одинаковые строки.
Сортировка списка
Обычно порядок строк в списке не имеет значения. Но иногда необходимо, чтобы строки имели определенную последовательность. Так, например, чтобы какое-либо наименование товара легче было найти в списке, нужно, чтобы строки были расположены в алфавитном порядке по наименованиям товара. Кроме того, иногда необходимо изменить порядок строк в готовом отчете, для того чтобы значения сумм располагались в списке в последовательности от большей к меньшей.
Изменение порядка строк в списке называется сортировкой. Excel – очень гибкая система в отношении методов сортировки данных, поэтому часто для выполнения описываемой операции бывает достаточно сделать всего один щелчок мышью.
Простая сортировка
Чтобы быстро отсортировать список в порядке возрастания, переместите табличный курсор в начало столбца, по которому нужно выполнить сортировку. Затем щелкните на кнопке Сортировка по возрастанию, расположенной на панели инструментов Главная
или Данные. Кнопка Сортировка по убыванию работает таким же образом, но список сортируется в порядке убывания. В обоих случаях Excel самостоятельно определяет размер списка и сортирует все строки в нем.
Сортировка отфильтрованного списка выполняется только для видимых строк. Если фильтрация отменяется, то список окажется не отсортированным.
Будьте внимательны во время сортировки списка с формулами. Если в формулах используются значения ячеек, находящихся в тех же строках, то никаких проблем не возникнет. Однако если в формулах используются значения ячеек, находящихся в других строках списка, то после сортировки эти формулы не будут верными. Если формулы в списке связаны с ячейками вне списка, убедитесь, что были указаны абсолютные адреса этих ячеек.
Многоуровневая сортировка
В отдельных случаях необходимо выполнить сортировку по двум или нескольким столбцам. Такая операция проводится, если сортировка по одному полю оставляет не отсортированными записи, соответствующие одинаковым значениям в сортируемом поле.
Например, если список, приведенный на рисунке 13.10, отсортировать по месяцам, то строки для каждого месяца расположатся вместе. Однако иногда желательно, чтобы в пределах каждого месяца отдельные записи тоже были отсортированы, например, по значениям поля, в котором представлены сотрудники. В этом случае нужно выполнять сортировки по двум столбцам (Месяц и Сотрудник).
Если необходимо выполнить сортировку по нескольким полям, выберите команду
Данные – Сортировка. Появится диалоговое окно Сортировка (рис.13.10). В раскрывающемся списке Сортировать по выберите поле и укажите порядок сортировки
(по возрастанию/по убыванию или настраиваемый список) и нажмите Добавить уровень
Затем сделайте то же самое для второго поля. Если необходимо отсортировать и по
13 третьему полю, определите третье поле в третьем разделе. Щелкните на кнопке ОК, и строки быстро перегруппируются.
Рисунок 13. 10. Многоуровневая сортировка
Если результат сортировки оказался неудовлетворительным, нажмите комбинацию клавиш
Очень часто бывает необходимо, чтобы записи в файле хранились в своем первоначальном порядке, а сортировка выполнялась лишь временно, для просмотра.
Выход один - добавить к списку дополнительный столбец и хранить в нем порядковые номера строк (для создания номеров строк не используйте формулы). Затем после сортировки можно вернуться к первоначальному порядку, еще раз отсортировав список по полю, содержащему порядковые номера. Для возврата к первоначальному порядку также можно использовать возможность отмены последнего действия. Однако использование дополнительного столбца имеет то преимущество, что позволяет выполнить дополнительные операции над временно отсортированным списком (и результат действия этих операций не будет отменен после возврата к первоначальному порядку в списке).
Если в диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры, на экране появится диалоговое окно Параметры сортировки (рис.13.11).
Рисунок 13. 11. Параметры сортировки
Ниже приведено описание опций этого диалогового окна.
Учитывать регистр. При сортировке будет учитываться различие между прописными и строчными буквами. В результате при сортировке по возрастанию прописные буквы будут располагаться перед строчными. По умолчанию различие между прописными и строчными буквами не учитывается.
14
Сортировать. Позволяет выполнить сортировку по столбцам, а не по строкам
(задано по умолчанию).
Правила сортировки в Excel
Поскольку в ячейках может содержаться информация разного типа, полезно знать, как эта информация сортируется. Ниже приведен список элементов в порядке убывания их значимости при выполнении сортировки по возрастанию.
Числа. Числа сортируются от наименьшего отрицательного к наибольшему положительному. Даты и время обрабатываются как числа. Во всех случаях при сортировке обрабатывается реальнаявеличина (а не ее внешний вид после форматирования).
Текст. Сортируется в следующем алфавитном порядке: 0 1 2 3 4 5 6 7 8 9
(пробел) ! " # $ % & ' ( ) * + . - . / : ; < = > ? @ [ \ ] ^ _ ' { | } A B C D E F G H I J K L M N O
P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь
Э Ю Я. По умолчанию во время сортировки строчные и прописные буквы не различаются.
Однако есть возможность изменить это в диалоговом окне Параметры сортировки.
Логические значения. Ложь следует перед Истина.
Ошибочные значения. Такие ошибочные значения, как #знач! и #н/д, появляются в их первоначальном порядке и не сортируются по типу ошибок.
Пустые ячейки. Всегда появляются последними.
При выполнении сортировки по убыванию приведенные последовательности используются в обратном порядке (за исключением пустых ячеек, которые по-прежнему остаются последними).
Особый порядок сортировки
Сортировка чисел или упорядочение текстовых строк чаще всего выполняется по алфавиту. Однако в некоторых случаях бывает необходимо отсортировать данные особым образом. Например, если данные представляют собой названия месяцев, то желательно, чтобы они располагались не в алфавитном порядке, а в порядке возрастания номеров месяцев. Для выполнения такой команды нужно в поле Порядок выбрать Настраиваемый
список (рис.13.12). По умолчанию в Excel определены четыре стандартных списка. Кроме того, пользовать может также определить собственные списки. Вот перечень стандартных списков Excel.
Сокращенные названия дней недели: Пн, Вт, Ср, Чт, Пт, Сб, Вс.
Полные названия дней недели: Понедельник, Вторник, Среда, Четверг,
Пятница Суббота, Воскресенье.
Сокращенные названия месяцев: Янв, Фев, Map, Апр, Май, Июн, Июл, Авг,
Сен, Окт, Ноя, Дек.
Полные названия месяцев: Январь, Февраль, Март, Апрель, Май, Июнь,
Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь.
15
Рисунок 13. 12. Настраиваемый список
Иногда необходимо создать свой список. Например, у вашей компании есть несколько магазинов и вам необходимо, чтобы они были размещены в определенном порядке (не алфавитном). Если вы создадите пользовательский список, операция сортировки будет помещать элементы в том порядке, в котором они указаны в списке.
Создание промежуточных итогов
Чтобы использовать эту возможность, список должен быть предварительно отсортирован, поскольку промежуточные итоги будут создаваться каждый раз при изменении значения определенного поля. Например, отсортируем список для подведения промежуточных итогов по значениям поля Проект.
Чтобы формулы промежуточных итогов вставлялись в список автоматически, поместите табличный курсор где-нибудь на списке и выберите команду на вкладке Данные в группе Структура команда Промежуточные итоги. Появится диалоговое окно
Промежуточные итоги (рис. 13.13).
Рисунок 13. 13. Промежуточные итоги
16
В этом диалоговом окне представлено несколько опций.
При каждом изменении в. В этом раскрывающемся списке показаны все поля вашего списка. Выбранное поле должно быть отсортировано.
Операция. Вам предоставлены на выбор 11 функций. Обычно используется функция Сумма. Она задана по умолчанию.
Добавить итоги по. В этом окне выводятся названия всех полей вашего списка. Разместите отметку радом с полем или полями, в которых вы хотите подвести промежуточные итоги.
Заменить текущие итоги. Любые существующие формулы итогов заменяются новыми.
Конец страницы между группами. Excel вставляет символ конца страницы после подведения каждого промежуточного итога.
Итоги под данными. Итоги будут расположены под текущими данными, если эта опция выбрана (она установлена по умолчанию). В противном случае вначале будет расположен итог, а затем данные.
Убрать все. После щелчка на этой кнопке из списка удаляются все формулы итогов.
Если щелкнуть на кнопке OK, Excel проанализирует список и вставит формулы, которые были определены, а также структурирует таблицу. Все формулы используют функцию Промежуточные итоги. На рисунке 13.14 показан рабочий лист после добавления промежуточных итогов.
Рисунок 13. 14. Добавление промежуточных итогов
200>
1
ЛАБОРАТОРНАЯ РАБОТА №13
Тема: Списки
По существу, список - это упорядоченный набор данных. Обычно список состоит из строки заголовков (описания данных) и строк данных, которые могут быть числовыми и текстовыми. Список можно считать табличной базой данных, чем он, в сущности, и является.
В Excel используется термин списокдля обозначения базы данных, хранящейся в рабочем листе, а термин база данных- для таблиц с информацией, которая находится во внешнем файле.
На рис.13.1 показан список, находящийся на рабочем листе. В первой строке этого списка расположены заголовки, а в следующих строках - данные.
Рисунок 13. 1. Список на рабочем листе
Список занимает шесть столбцов. Обратите внимание на несколько разных типов данных, содержащихся в этом списке: текст и числа (возможны, и даты). В столбце F находится формула, полученная на основе данных, введенных в столбцы D и Е.
Столбцы списка часто называют полями,а строки – записями. Исходя из принятой выше терминологии, можно сказать, что данный список содержит шесть полей (Сотрудник,
Месяц, Товар, Цена, Количество и Всего) и пятнадцать записей.
Размер списка ограничен размерами одного рабочего листа. Другими словами, список может иметь не больше 16384 столбцов и не больше 1048576 строк.
Что можно делать со списком
В Excel есть несколько средств, предназначенных для работы со списками. Они могут быть использованы для самых разных целей. Для одних пользователей список — это способ простого хранения набора данных (например, список клиентов), другие используют его для хранения таких данных, которые в конечном итоге должны быть включены в отчет.
Ниже приведены операции, которые обычно выполняются над списками.
2
Ввод данных в список.
Фильтрация списков для выборочного отображения строк (по определенному критерию).
Сортировка списка.
Вставка формул для подведения промежуточных итогов.
Создание формул для вычисления результатов в списке, отфильтрованном по определенным критериям.
Создание итоговой сводной таблицы на основе данных списка.
Планирование списка
Excel - довольно гибкая система в отношении хранения информации в списках, однако получить начальное представление о том, как организовать эту информацию, нелегко. Вот основные рекомендации, о которых следует помнить, создавая списки:
Помещайте заголовки (по одному для каждого столбца) в первую строку списка, которая называется строкой заголовков. Если заголовки длинные, используйте текстовый формат с переносом слов, тогда вам не придется расширять столбцы.
В каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст.
Можно применять формулы, использующие значения из других полей этой же записи. Если формула ссылается на ячейку, расположенную вне списка, сделайте ссылку на эту ячейку абсолютной, иначе результаты при сортировке списка могут быть непредсказуемыми.
Не используйте пустых строк в списке. При проведении операций над списком Excel определяет его границы автоматически, при этом пустая строка означает конец списка.
Рекомендуется помещать список на отдельный лист. Если на этот лист надо поместить еще другую информацию, помещайте ее ниже или выше списка. Не используйте для этого ячейки слева или справа от списка.
Используйте команду Закрепить области на вкладке Вид в группе Окно, чтобы заголовки были всегда видны при прокручивании листа списка
Старайтесь предварительно отформатировать весь столбец, чтобы данные всегда имели один и тот же формат. Например, если столбец содержит даты, выберите необходимый формат для отображения дат в этом столбце.
Для того чтобы в столбец списка заносились только определенные данные, с помощью команды Работа с данными/Проверка данных можно задать проверку вводимых данных.
Одной из наиболее привлекательных особенностей электронных таблиц является возможность простого структурирования таблиц. Это, конечно же, относится и к спискам.
Например, если к созданному списку необходимо добавить еще одно поле, то можно просто вставить новый столбец, дать ему заголовок – и ваш список расширен.
Ввод данных в список
Данные можно ввести в список тремя способами:
Вручную, используя стандартные методы ввода данных.
Импортировать или скопировать данные из другого файла.
3
Использовать форму ввода.
Ввод данных вручную
В операции ввода данных в список нет ничего особенного. Перемещаясь по таблице, вы вводите данные в нужные ячейки.
В Excel есть два средства, которые помогают избежать выполнения скучной операции ввода однообразных данных.
1.
Автозаполнение. Когда вы начинаете вводить данные, программа просматривает столбец, чтобы выяснить, сможет ли она узнать то, что вы набираете. Если
Excel находит закономерность, то заполняет остаток ячеек автоматически. Чтобы завершить ввод данных, нажмите клавишу
диалогового окна Параметры.
2.
Выбор из списка. Щелкните правой кнопкой мыши на ячейке и выберите из появившегося контекстного меню команду Выбрать из раскрывающегося списка. Excel выведет список со всеми элементами, находящимися в столбце (рис.13.2). Выберите из списка нужный элемент, и он появится в ячейке (при этом ничего набирать не нужно).
Рисунок 13. 2. Выбор из списка
Ввод данных с помощью формы ввода
В Excel можно вводить данные с помощью формы. Чтобы вывести на экран форму для ввода данных, поместите табличный курсор в каком-нибудь месте списка и выполните команду Данные/Форма. Если такой кнопки нет, то нужно включить Настройка панели
4
быстрого доступа – Другие команды – Выбрать команды из – Все команды – Форма –
Добавить – ОК – кнопка появится на панели быстрого доступа. Excel определит размер вашего списка и выведет диалоговое окно, в котором будет находиться каждое поле списка
(рис.13.3).
Рисунок 13. 3. Поле списка
Поля, содержащие формулу, в форме ввода отображаются, но их значения нельзя изменить.
Когда появится форма, в ней будет показана первая запись списка. Обратите внимание, что индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в списке.
Чтобы ввести новую запись, щелкните на кнопке Добавить, таким образом в форме очистятся все поля.
После этого можно вводить новую информацию в соответствующие поля.
Используйте клавишу <ТаЬ> или
После щелчка на кнопке Добавить или Закрыть введенные данные появятся в конце списка. Можно также нажать клавишу
Добавить. Если список содержит формулы, то они автоматически появятся в новых записях.
Форму ввода можно использовать не только для ввода данных. Она позволяет редактировать существующие записи, просматривать их, удалять и выборочно отображать записи по определенному критерию.
Форма ввода содержит несколько кнопок:
Удалить. Удаляет текущую запись.
Вернуть. Отменяет все внесенные в текущую запись изменения. Эта кнопка работает до тех пор, пока вы не щелкнете на кнопке Добавить.
Назад. Осуществляется переход к предыдущей записи списка. Если установлен критерий отбора, то произойдет переход к предыдущей записи, удовлетворяющей данному критерию.
Далее. Осуществляется переход к следующей записи списка. Если установлен критерий отбора, то произойдет переход к следующей записи, удовлетворяющей данному критерию.
5
Критерии. Очищает поля для ввода критерия, по которому будут отбираться записи. Например, чтобы найти все записи, в которых значение, находящееся в поле
Количество, больше 10, введите >10 в поле Количество формы. После этого можно использовать кнопки Назад и Далее, чтобы найти подходящую запись.
Закрыть. Закрывает форму и записывает введенные данные (конечно, если вы их ввели) в рабочий лист.
Фильтрация списков
Фильтрация списка – это процесс сокрытия всех строк, кроме тех, которые удовлетворяют определенным критериям. Например, если у вас есть список клиентов, его можно отфильтровать так, чтобы видеть фамилии только тех из них, которые живут в определенном городе. Фильтрация - весьма распространенная и очень полезная операция.
В Excel списки можно фильтровать двумя способами.
Автофильтр используется для фильтрации по простым критериям.
Расширенный фильтр применяется для фильтрации по более сложным критериям.
Автоматическая фильтрация
Чтобы автоматически отфильтровать список, сначала установите табличный курсор на одну из его ячеек. Затем выполните команду фильтр, которая находится на вкладке
Данные в группе Сортировка и Фильтр. Excel проанализирует список и добавит в строку заголовков полей кнопки раскрывающихся списков (кнопки автофильтра), как показано на рисунке 13.4.
Рисунок 13. 4. Автофильтр
Когда вы отфильтруете список, в строке состояния появится сообщение о том, сколько строк отобрано. Кроме того, изменится значок кнопки раскрывающегося списка, чтобы напомнить вам, что список отфильтрован по значениям, содержащимся в этом столбце.
6
Автоматическая фильтрация имеет ограничения. В раскрывающемся списке появляются только первых 999 различных значений. Если число элементов в вашем списке превышает указанный предел, можно использовать средства расширенной фильтрации, которые будут описаны ниже.
Для отмены режима Автофильтр и удаления кнопок раскрывающихся списков в именах полей выберите команду Данные – Фильтр повторно. В результате будет удален флажок у пункта меню Автофильтр и список вернется в обычное состояние.
Не забывайте, что если у вас есть формулы, в которых используются ссылки на ячейки отфильтрованного списка, то после выполнения фильтрации значение, вычисляемое по таким формулам, не изменяется. Другими словами, в формулах не могут быть использованы лишь значения из отфильтрованных ячеек. Например, если в ячейку введена формула суммирования значений в столбце С, то после фильтрации будет продолжаться суммирование всех значений в столбце С, а не только элементов в видимых строках.
Автоматическая фильтрация по значениям в нескольких столбцах
Предположим, вам необходимо просмотреть записи, относящиеся к продажам в феврале, выполненным сотрудником Кипеловым. Сначала включите режим Фильтр. Затем щелкните на кнопке раскрывающегося списка в поле Месяц и выберите Февраль. Из списка будут отобраны записи, в которых поле Месяц имеет значение Февраль. Затем щелкните на кнопке раскрывающегося списка в поле Сотрудник и выберите Кипелов. Список будет отфильтрован еще раз — по значениям в двух столбцах.
Можно отфильтровать список по любому количеству столбцов.
Пользовательский автофильтр
Обычно автоматическая фильтрация заключается в выборе одного значения в одном или нескольких столбцах. Если выбрать опцию Условие в раскрывающемся списке, то можно осуществить фильтрацию более гибким способом. При выборе опции Числовые
фильтры – Настраиваемый фильтр появится диалоговое окно Пользовательский
автофильтр (рис.13.5). Это окно позволяет фильтровать списки с использованием нескольких критериев.
Рисунок 13. 5. Пользовательский автофильтр
Значения больше или меньше установленного. Например, можно выбрать записи, указывающие на объемы продаж, превышающие 1 000.
Значения в интервале. Например, отобрать все записи, указывающие на объемы продаж, превышающие 1 000 и не превышающие 2 000.
Два отдельных значения. Например, отобрать записи, в которых находится информация об объеме продаж в январе или феврале.
7
Можно использовать символы подстановки “*” и "?", чтобы отфильтровать список более гибким способом. Например, чтобы вывести на экран записи только о тех сотрудниках, фамилии которых начинаются с буквы К, используйте шаблон К*.
Пользовательский автофильтр может быть весьма полезным средством, но он имеет определенные ограничения. Например, с его помощью нельзя отфильтровать по трем и более значениям (например, вывести записи, в которых представлена информация о продажах только за январь, февраль или март). Для выполнения задач такого типа необходимо использовать средство расширенной фильтрации.
Наложение условия по списку
Иногда необходимо отфильтровать числовые поля так, чтобы показать на экране наибольшие или наименьшие значения. Например, вам нужно определить список пяти сотрудников, продавших наибольшее количество товара. В этом случае следует использовать опцию Первые 10, которая расположена в группе Числовые фильтры.
При выборе опции Первые 10 появится диалоговое окно Наложение условия по
списку (рис.13.6).
Рисунок 13. 6. Наложение условия по списку
С его помощью вы можете выбрать наибольшие или наименьшие элементы из списка, а также указать их количество. Например, если вам необходимо получить список пяти сотрудников, сделавших наибольшее количество продаж, выберите опцию
Наибольших в списке поля Количество и задайте число 5. Таким образом, вы отфильтруете список, и в нем будут показаны пять строк с наибольшими значениями этого поля. В этом диалоговом окне можно также выбрать опцию % от количества элементов. Например, можно отобрать 5% наибольших элементов поля.
Построение диаграммы по данным отфильтрованного списка
Для создания некоторых интересных многоцелевых диаграмм используются данные отфильтрованного списка. Этот способ весьма эффективен, так как только диаграмма позволяет наглядно увидеть картину, представленную теми или иными данными. При изменении критерия автофильтра диаграмма автоматически изменяется и показывает значения, взятые только из "видимых" ячеек.
Расширенная фильтрация
Часто для обработки списка бывает вполне достаточно автофильтра. Но если необходимо выполнить операцию, выходящую за рамки возможностей автофильтра, то придется прибегнуть к помощи средств расширенной фильтрации. Расширенный фильтр гораздо более гибкий, чем автофильтр, однако при его использовании требуется выполнять
8 больше подготовительных действий. Расширенный фильтр позволяет выполнить следующее:
Определить более сложный критерий фильтрации.
Установить вычисляемый критерий фильтрации.
Перемещать копии строк, отвечающих определенному критерию, в другое место.
Установка диапазона критериев
Прежде чем использовать средство расширенной фильтрации, необходимо задать
диапазон критериев. Это специально отведенная область рабочего листа, отвечающая определенным требованиям. Диапазон критериев используется Excel для фильтрации списка и должен отвечать следующим требованиям:
Должен состоять по крайней мере из двух строк, первая из которых должна содержать все или некоторые названия полей списка.
Остальные строки должны содержать критерии фильтрации.
Хотя вы можете отвести для диапазона критериев любое место на рабочем листе, желательно не задействовать при этом строки, используемые списком.
Поскольку некоторые из строк списка будут скрыты при фильтрации, может оказаться, что ваш диапазон критериев стал невидимым после фильтрации. Поэтому размещайте диапазон критериев над или под списком. Поля, не используемые при фильтрации, можно не помещать в диапазон критериев (рис. 13.7).
Рисунок 13. 7. Расширенный фильтр
В примере диапазон критериев содержит только одну строку критериев. Поля в каждой строке диапазона критериев (исключая заглавную строку) соединены оператором
9
И. Поэтому в отфильтрованном списке показаны только те строки, у которых одновременно поле Сотрудник содержит фамилии, начинающиеся с буквы "К", и в поле Товар содержится значение «товар А».
Чтобы выполнить фильтрацию, выберите команду Дополнительно, которая находится в группе Фильтр во вкладке Данные. Появится диалоговое окно Расширенный
фильтр (рис.13.7).
Определите диапазон списка и диапазон критериев и убедитесь, что установлен переключатель Фильтровать список на месте. Щелкните на кнопке ОК, и список будет отфильтрован по заданным критериям.
Множественный критерий отбора
Если в диапазоне критериев используется несколько строк, критерии в каждой строке соединены оператором ИЛИ. Например, можно отфильтровать строки списка так, чтобы они отвечали следующим требованиям:
В поле Цена содержатся значения <200 ИЛИ в поле Цена содержатся значения >300
(рис.13.8).
Диапазон критериев может иметь любое количество строк, соединенных одна с другой оператором ИЛИ.
Рисунок 13. 8. Множественный критерий отбора
Текстовые и числовые критерии
При фильтрации можно использовать сравнения с числом или строковой (текстовой) константой с помощью операторов, таких, как равно (=), больше чем (>), не равно (<>) и т.д.
В таблице 13.1 представлен список операторов сравнения, которые можно использовать в текстовых или числовых критериях.
10
Таблица 13. 1. Операторы сравнения
Оператор
Тип сравнения
=
Равно
>
Больше чем
>=
Больше либо равно
<
Меньше
<=
Меньше либо равно
<>
Не равно
В таблице 13.2 показаны примеры строковых критериев.
Таблица 13. 2. Примеры строковых критериев
Критерий
Действие
>K
Слова, начинающиеся с букв от Л до Я
<>C
Все слова, кроме начинающихся с буквы С
="Январь"
Все слова "Январь"
См*
Слова, начинающиеся с букв "См" с*с
Слова, начинающиеся с буквы "с" и заканчивающиеся буквой "с" с?c
Слова из трех букв, начинающиеся с буквы "с" и заканчивающиеся буквой "с"
В текстовых сравнениях не различаются прописные и строчные буквы. Например, критерий си* соответствует как фамилии Сидоров, так и слову сильный.
Вычисляемые критерии
Использование вычисляемых критериев может сделать операцию фильтрации более гибкой. Вычисляемый критерий основывается на одном или нескольких вычислениях и, по существу, вычисляет для списка новое поле. Поэтому следует помещать новое название поля в первую строку диапазона критериев.
Предположим, что список содержит информацию о проектах: номер проекта, дата начала проекта, дата окончания и т.п. Если вы хотите увидеть информацию только о проектах, длительность которых не превышает двух дней, необходимо использовать формулу
=Дата окончания-Дата начала<=2 (рис. 13.9).
Эта формула возвращает логическое значение истина или ложь формула ссылается на ячейки с данными, расположенными в первой строке списка, следующей послестроки заголовков. Когда список будет отфильтрован, в нем будут отображены только те проекты, продолжительность которых меньше либо равна 2 дням.
11
Рисунок 13. 9. Вычисляемый критерий
Обратите внимание, что можно получить тот же результат без использования вычисляемого критерия. Нужно просто прибавить к списку новый столбец с формулой, по которой вычисляется продолжительность проекта. Однако использование вычисляемого критерия избавляет вас от необходимости добавлять лишний столбец в список.
При использовании вычисляемых критериев рекомендуется помнить некоторые правила.
Не используйте заголовки полей списка в диапазоне критериев для вычисляемого значения. Создайте новый заголовок или просто оставьте пустую ячейку.
Можно использовать любое количество вычисляемых критериев, а также сочетания вычисляемых критериев с невычисляемыми.
Не обращайте внимания на значения, возвращаемые формулами в диапазоне критериев. Они ссылаются на первую строку списка.
Если ваша вычисляемая формула ссылается на значения вне списка, используйте абсолютные, а не относительные ссылки. Например, вместо С1 используйте ссылку $С$1.
При создании формул вычисляемых критериев используйте первую строку списка " (не строку заголовков!). Используйте относительные, а не абсолютные ссылки.
Например, вместо $С$5 используйте ссылку С5.
Другие возможности расширенной фильтрации
В диалоговом окне Расширенный фильтр представлены еще две опции, которые рассматриваются дальше.
Скопировать результат в другое место.
Только уникальные записи.
Копирование отобранных строк
Если вы выберете переключатель Скопировать результат в другое место в диалоговом окне Расширенный фильтр, то отобранные строки будут скопированы в другое место активного рабочего листа или на другой лист. Место определяется в поле
Поместить результат в диапазон. Обратите внимание, что при использовании этой опции сам список не фильтруется.
Отображение только уникальных строк
12
При выборе опции Только уникальные записи все одинаковые строки, отвечающие определенному критерию, будут скрыты. Если вы не установили диапазон критериев, то в списке будут скрыты все одинаковые строки.
Сортировка списка
Обычно порядок строк в списке не имеет значения. Но иногда необходимо, чтобы строки имели определенную последовательность. Так, например, чтобы какое-либо наименование товара легче было найти в списке, нужно, чтобы строки были расположены в алфавитном порядке по наименованиям товара. Кроме того, иногда необходимо изменить порядок строк в готовом отчете, для того чтобы значения сумм располагались в списке в последовательности от большей к меньшей.
Изменение порядка строк в списке называется сортировкой. Excel – очень гибкая система в отношении методов сортировки данных, поэтому часто для выполнения описываемой операции бывает достаточно сделать всего один щелчок мышью.
Простая сортировка
Чтобы быстро отсортировать список в порядке возрастания, переместите табличный курсор в начало столбца, по которому нужно выполнить сортировку. Затем щелкните на кнопке Сортировка по возрастанию, расположенной на панели инструментов Главная
или Данные. Кнопка Сортировка по убыванию работает таким же образом, но список сортируется в порядке убывания. В обоих случаях Excel самостоятельно определяет размер списка и сортирует все строки в нем.
Сортировка отфильтрованного списка выполняется только для видимых строк. Если фильтрация отменяется, то список окажется не отсортированным.
Будьте внимательны во время сортировки списка с формулами. Если в формулах используются значения ячеек, находящихся в тех же строках, то никаких проблем не возникнет. Однако если в формулах используются значения ячеек, находящихся в других строках списка, то после сортировки эти формулы не будут верными. Если формулы в списке связаны с ячейками вне списка, убедитесь, что были указаны абсолютные адреса этих ячеек.
Многоуровневая сортировка
В отдельных случаях необходимо выполнить сортировку по двум или нескольким столбцам. Такая операция проводится, если сортировка по одному полю оставляет не отсортированными записи, соответствующие одинаковым значениям в сортируемом поле.
Например, если список, приведенный на рисунке 13.10, отсортировать по месяцам, то строки для каждого месяца расположатся вместе. Однако иногда желательно, чтобы в пределах каждого месяца отдельные записи тоже были отсортированы, например, по значениям поля, в котором представлены сотрудники. В этом случае нужно выполнять сортировки по двум столбцам (Месяц и Сотрудник).
Если необходимо выполнить сортировку по нескольким полям, выберите команду
Данные – Сортировка. Появится диалоговое окно Сортировка (рис.13.10). В раскрывающемся списке Сортировать по выберите поле и укажите порядок сортировки
(по возрастанию/по убыванию или настраиваемый список) и нажмите Добавить уровень
Затем сделайте то же самое для второго поля. Если необходимо отсортировать и по
13 третьему полю, определите третье поле в третьем разделе. Щелкните на кнопке ОК, и строки быстро перегруппируются.
Рисунок 13. 10. Многоуровневая сортировка
Если результат сортировки оказался неудовлетворительным, нажмите комбинацию клавиш
Очень часто бывает необходимо, чтобы записи в файле хранились в своем первоначальном порядке, а сортировка выполнялась лишь временно, для просмотра.
Выход один - добавить к списку дополнительный столбец и хранить в нем порядковые номера строк (для создания номеров строк не используйте формулы). Затем после сортировки можно вернуться к первоначальному порядку, еще раз отсортировав список по полю, содержащему порядковые номера. Для возврата к первоначальному порядку также можно использовать возможность отмены последнего действия. Однако использование дополнительного столбца имеет то преимущество, что позволяет выполнить дополнительные операции над временно отсортированным списком (и результат действия этих операций не будет отменен после возврата к первоначальному порядку в списке).
Если в диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры, на экране появится диалоговое окно Параметры сортировки (рис.13.11).
Рисунок 13. 11. Параметры сортировки
Ниже приведено описание опций этого диалогового окна.
Учитывать регистр. При сортировке будет учитываться различие между прописными и строчными буквами. В результате при сортировке по возрастанию прописные буквы будут располагаться перед строчными. По умолчанию различие между прописными и строчными буквами не учитывается.
14
Сортировать. Позволяет выполнить сортировку по столбцам, а не по строкам
(задано по умолчанию).
Правила сортировки в Excel
Поскольку в ячейках может содержаться информация разного типа, полезно знать, как эта информация сортируется. Ниже приведен список элементов в порядке убывания их значимости при выполнении сортировки по возрастанию.
Числа. Числа сортируются от наименьшего отрицательного к наибольшему положительному. Даты и время обрабатываются как числа. Во всех случаях при сортировке обрабатывается реальнаявеличина (а не ее внешний вид после форматирования).
Текст. Сортируется в следующем алфавитном порядке: 0 1 2 3 4 5 6 7 8 9
(пробел) ! " # $ % & ' ( ) * + . - . / : ; < = > ? @ [ \ ] ^ _ ' { | } A B C D E F G H I J K L M N O
P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь
Э Ю Я. По умолчанию во время сортировки строчные и прописные буквы не различаются.
Однако есть возможность изменить это в диалоговом окне Параметры сортировки.
Логические значения. Ложь следует перед Истина.
Ошибочные значения. Такие ошибочные значения, как #знач! и #н/д, появляются в их первоначальном порядке и не сортируются по типу ошибок.
Пустые ячейки. Всегда появляются последними.
При выполнении сортировки по убыванию приведенные последовательности используются в обратном порядке (за исключением пустых ячеек, которые по-прежнему остаются последними).
Особый порядок сортировки
Сортировка чисел или упорядочение текстовых строк чаще всего выполняется по алфавиту. Однако в некоторых случаях бывает необходимо отсортировать данные особым образом. Например, если данные представляют собой названия месяцев, то желательно, чтобы они располагались не в алфавитном порядке, а в порядке возрастания номеров месяцев. Для выполнения такой команды нужно в поле Порядок выбрать Настраиваемый
список (рис.13.12). По умолчанию в Excel определены четыре стандартных списка. Кроме того, пользовать может также определить собственные списки. Вот перечень стандартных списков Excel.
Сокращенные названия дней недели: Пн, Вт, Ср, Чт, Пт, Сб, Вс.
Полные названия дней недели: Понедельник, Вторник, Среда, Четверг,
Пятница Суббота, Воскресенье.
Сокращенные названия месяцев: Янв, Фев, Map, Апр, Май, Июн, Июл, Авг,
Сен, Окт, Ноя, Дек.
Полные названия месяцев: Январь, Февраль, Март, Апрель, Май, Июнь,
Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь.
15
Рисунок 13. 12. Настраиваемый список
Иногда необходимо создать свой список. Например, у вашей компании есть несколько магазинов и вам необходимо, чтобы они были размещены в определенном порядке (не алфавитном). Если вы создадите пользовательский список, операция сортировки будет помещать элементы в том порядке, в котором они указаны в списке.
Создание промежуточных итогов
Чтобы использовать эту возможность, список должен быть предварительно отсортирован, поскольку промежуточные итоги будут создаваться каждый раз при изменении значения определенного поля. Например, отсортируем список для подведения промежуточных итогов по значениям поля Проект.
Чтобы формулы промежуточных итогов вставлялись в список автоматически, поместите табличный курсор где-нибудь на списке и выберите команду на вкладке Данные в группе Структура команда Промежуточные итоги. Появится диалоговое окно
Промежуточные итоги (рис. 13.13).
Рисунок 13. 13. Промежуточные итоги
16
В этом диалоговом окне представлено несколько опций.
При каждом изменении в. В этом раскрывающемся списке показаны все поля вашего списка. Выбранное поле должно быть отсортировано.
Операция. Вам предоставлены на выбор 11 функций. Обычно используется функция Сумма. Она задана по умолчанию.
Добавить итоги по. В этом окне выводятся названия всех полей вашего списка. Разместите отметку радом с полем или полями, в которых вы хотите подвести промежуточные итоги.
Заменить текущие итоги. Любые существующие формулы итогов заменяются новыми.
Конец страницы между группами. Excel вставляет символ конца страницы после подведения каждого промежуточного итога.
Итоги под данными. Итоги будут расположены под текущими данными, если эта опция выбрана (она установлена по умолчанию). В противном случае вначале будет расположен итог, а затем данные.
Убрать все. После щелчка на этой кнопке из списка удаляются все формулы итогов.
Если щелкнуть на кнопке OK, Excel проанализирует список и вставит формулы, которые были определены, а также структурирует таблицу. Все формулы используют функцию Промежуточные итоги. На рисунке 13.14 показан рабочий лист после добавления промежуточных итогов.
Рисунок 13. 14. Добавление промежуточных итогов