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

После ввода даты её формат можно изменить с помощью команды Главная – ячейки – формат – формат ячейки. Следующая таблица показывает, как выглядит дата 24 февраля 2000 года в первых пяти встроенных форматах даты.

Формат	Результат
Д.М	24.2
Д.М.ГГ	24.2.00
ДД.ММ.ГГ	24.02.00
Д МММ	24 фев
Д МММ ГГ	24 фев 00

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

1. Выделите ячейки, которые вы хотите форматировать.

2. В меню Главная выберите команду Ячейки (или нажмите клавиши ctrl+1) и затем в окне диалога Формат ячеек перейдите на вкладку Число, показанную на рис.12.2

3. В списке Числовые форматы выберите категорию Дата.

4. Выберите нужный формат в списке Тип и нажмите кнопку ОК.

Рис.12.2

Подобным образом можно изменить формат введённого значения времени. Следующая таблица показывает результаты форматирования введённого значения 13:52:32,44 с использованием первых восьми встроенных форматов времени.

Формат	Результат
ч:мм	13:52
Ч: мм AP/PM	1:52PM
Ч:мм:сс	13:52:32
Ч:мм:сс AP/PM	1:52:32 PM
Мм:сс,0	52:3,4
[ч]:мм:сс	13:52:32
ДД.ММ.ГГ ч:мм AP/PM	00.01.00 1:52 PM
ДД.ММ.ГГ ч:мм	00.01.00 13:52

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

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

Ввод дат и времени

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

Например, чтобы ввести дату 1 декабря 2000 г., выделите ячейку и введите 1.12.00 (в качестве разделителя компонентов даты можно использовать наклонную черту, точку или дефис). Excel не отобразит десятичное значение даты в строке формул. Вместо этого ячейке будет назначен формат ДД.ММ.ГГ (если эта ячейка еще не отформатирована). а в строке формул появится значение 01.12.2000. Время также позволяет вводить «в формате». выделите ячейку, в которую требуется занести значение, и введите время в одном из следующих форматов: ч:мм АМ/РМ, ч:мм:сс АМ/РМ, ч:мм, ч:мм:сс, мм:сс, мм:сс.0, или в объединенном формате даты и времени — Д. ММ. ГГ ч:мм. Обратите внимание, что часы, минуты и секунды в значении времени должны быть разделены двоеточием.

Например, чтобы ввести время 2:15 РМ, выделите ячейку и введите 2:15 РМ или 14:15. В любом случае Excel распознает это время как эквивалент десятичного значения 0,59375 и выведет его на листе в формате времени, который вы использовали при вводе.

Арифметические операции с датами и временем

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

Например, предположим, что нам нужно вычислить дату на 200 дней позже 4 июля 2000 года. Если ячейка А1 содержит значение 4.07.00, то следующая формула возвратит значение 36911 или 20.01.01:

=А1+200

Или предположим, что вы хотите найти число недель между 31 октября 1999 и 13 мая 2000. Используйте для этого формулу:

=((«13.05.00»)-(«3.10.99»))/7

эта формула возвращает приблизительно 27,9 (недель).

Время можно использовать в функциях и в формулах точно так же, как и даты. Однако результаты вычислений времени не так легко понять, как результаты вычисления дат. Например, промежуток времени между 8:22 AP и 10:45 PM можно определить с помощью формулы

= «22:45» - «8:22»

Результат будет равен 0,599306, и его можно отформатировать как 2:23 PM.

Excel выведет значение по отношению к 12 ночи, и, следовательно, 2:23 PM означает, что интервал времени между этими значениями составляет 14 часов и 23 минуты.

Предположим, что вы хотите определить момент времени, который наступит через 2 часа, 23 минуты и 17 секунд после 12:35:23 PM. Формула

=(«12:35:23 PM»)+(«2:23:17»)

возвратит правильный ответ, 0,624074, который может быть отформатирован как 2:58 PM. Значение 2:23:17 в этой формуле представляет собой не абсолютное время (2:23:17 AM), а интервал времени (2 часа, 23 минуты и 17 секунд). Такой формат для Excel является совершенно приемлемый.

Функция ДНЕЙ360

Функция ДНЕЙ360 вычисляет количество дней между датами на основе 360-дневного года (двенадцать месяцев по 30 дней). Её синтаксис:

=ДНЕЙ360 (нач_дата; кон_дата; метод).

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

Функция ЧАС

Функция ЧАС преобразует дату в числовом формате (0-23) в часы. Её синтаксис:

= ЧАС (дата как число).

Дата как число - код дата-время, используемый в Miсrosoft Excel для вычислений с датами и периодами времени, например 16:48:00 или 4:48:00.

Изменение шкалы оси времени

Если исходные данные для оси категории являются значениями даты и времени EXCEL вместо обычной оси категории использует ось времени. На оси времени размещается деления через равные временные интервалы, что приводит к появлению разрывов или интерполяции значений в случае отсутствия данных для некоторых дат. Шкала оси времени имеет совсем другие параметры по сравнению со шкалой оси категории. Чтобы увидеть эти параметры, выделите ось времени и выберите первую команду в меню Работа с диаграммами - Макет- Оси - Основная горизонтальная(вертикальная) ось – дополнительные параметры горизонтальной (вертикальной) оси – окно формат оси. В открывшемся окне оси перейдите на вкладку Параметры оси. (рис.12.3)

Задание минимального и максимального значений

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

Рис.12.3

Изменение цены основных и промежуточных делений

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

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

Изменение единицы измерения

Единица измерения определяет «гранулированность» диаграммы. Например, вы создали диаграмму для месячных данных. (рис.12.4)

Рис.12.4

То при изменении единицы измерения с месяца на день диаграмма будет содержать пустое пространство величиной почти в месячный интервал между соседними парами точек данных. В то же время при увеличении единицы измерения вы можете получить диаграмму с грубой «консолидацией» данных. (рис.12.5)

Рис.12.5

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

1. В А10, В10, С10 помещены текстовые строки Иванов, Иван,Иванович. В D10 поместите формулу, которая выдаст «Иванов И.И.»

2. Вычислите сумму цифр целого трёхзначного числа с использованием текстовых функций.

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

4. Производственное совещание проходит по вторникам и пятницам. Составьте их расписание на второй квартал 2006 года в виде таблицы Дата-День недели.

5. Посчитайте, сколько в следующем году пятниц приходится на 13 число.

6. Сосчитайте количество рабочих дней в декабре.

7. Постройте диаграмму с осью времени, где в качестве единицы времени указывается день. Измените единицу измерения со дня на месяц.

ЛАБОРАТОРНАЯ РАБОТА №13

Тема: Списки

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

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

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

Рис. 13.1 Список на рабочем листе

Список занимает шесть столбцов. Обратите внимание на несколько разных типов данных, содержащихся в этом списке: текст и числа (возможны, и даты). В столбце F находится формула, полученная на основе данных, введенных в столбцы D и Е.

Столбцы списка часто называют полями,а строки - записями. Исходя из принятой выше терминологии, можно сказать, что данный список содержит шесть полей (Менеджер, Месяц, Товар, Цена, Количество и Всего) и пятнадцать записей.

Размер списка ограничен размерами одного рабочего листа. Другими словами, список может иметь не больше 16384 столбцов и не больше 1048576 строк.

Что можно делать со списком

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

1. Ввод данных в список.

2. Фильтрация списков для выборочного отображения строк (по определенному критерию).

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

4. Вставка формул для подведения промежуточных итогов.

5. Создание формул для вычисления результатов в списке, отфильтрованном по определенным критериям.

6. Создание итоговой сводной таблицы на основе данных списка.

Планирование списка

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

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

2. В каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст.

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

4. Не используйте пустых строк в списке. При проведении операций над списком Excel определяет его границы автоматически, при этом пустая строка означает конец списка.

5. Рекомендуется помещать список на отдельный лист. Если на этот лист надо поместить еще другую информацию, помещайте ее ниже или выше списка. Не используйте для этого ячейки слева или справа от списка.

6. Используйте команду Закрепить области на вкладке Вид в группе Окно, чтобы заголовки были всегда видны при прокручивании листа списка

7. Старайтесь предварительно отформатировать весь столбец, чтобы данные всегда имели один и тот же формат. Например, если столбец содержит даты, выберите необходимый формат для отображения дат в этом столбце.

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

Одной из наиболее привлекательных особенностей электронных таблиц является возможность простого структурирования таблиц. Это, конечно же, относится и к спискам. Например, если к созданному списку необходимо добавить еще один столбец (или, другими словами, поле), то здесь нет ничего сложного: просто вставьте новый столбец, дайте ему заголовок - и ваш список расширен. Если вы когда-нибудь работали с программой управления базами данных, то сможете по достоинству оценить такую возможность.

Ввод данных в список

Данные можно ввести в список тремя способами.

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

1. Импортировать или скопировать данные из другого файла.

2. Использовать форму ввода.

Ввод данных вручную

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

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

1. Автозаполнение. Когда вы начинаете вводить данные, программа просматривает столбец, чтобы выяснить, сможет ли она узнать то, что вы набираете. Если Excel находит закономерность, то заполняет остаток ячеек автоматически. Чтобы завершить ввод данных, нажмите клавишу <Enter>. Можно включать или отключать эту возможность с помощью опции Автозавершение значений ячеек на вкладке Правка диалогового окна Параметры.

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

Рис.13.2 Выбор из списка

Ввод данных с помощью формы ввода

Если вы предпочитаете вводить данные, используя форму, то Excel предоставляет вам и эту возможность. Чтобы вывести на экран форму для ввода данных, поместите табличный курсор в каком-нибудь месте списка и выполните команду Данные/Форма. (Если такой кнопки нет, то нужно включить Настройка панели быстрого доступа/Другие команды…/Выбрать команды из:/Все команды/Формы/Добавить/ОК > кнопка появится на панели быстрого доступа) Excel определит размер вашего списка и выведет диалоговое окно, в котором будет находиться каждое поле списка (рис.13.3). Поля, содержащие формулу, в форме ввода отображаются, но их значения нельзя изменить.

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

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

Рис. 13.3 Поле списка

Теперь можно вводить новую информацию в соответствующие поля. Используйте клавишу <ТаЬ> или <Shift+Tab> для перемещения от одного поля к другому. После щелчка на кнопке Добавить или Закрыть введенные данные появятся в конце списка. Можно также нажать клавишу <Enter>, что эквивалентно щелчку на кнопке Добавить. Если список содержит формулы, то они автоматически появятся в новых записях.

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

Форма ввода содержит несколько кнопок:

1. Удалить. Удаляет текущую запись.

2. Вернуть. Отменяет все внесенные в текущую запись изменения. Эта кнопка работает до тех пор, пока вы не щелкнете на кнопке Добавить.

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

4. Далее. Осуществляется переход к следующей записи списка. Если установлен критерий отбора, то произойдет переход к следующей записи, удовлетворяющей данному критерию.

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

6. Закрыть. Закрывает форму и записывает введенные данные (конечно, если вы их ввели) в рабочий лист.

Фильтрация списков

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

1. Автофильтр используется для фильтрации по простым критериям.

2. Расширенный фильтр применяется для фильтрации по более сложным критериям.

Автоматическая фильтрация

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

Рис. 13.4 Автофильтр

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

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

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

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

Автоматическая фильтрация по значениям в нескольких столбцах

Предположим, вам необходимо просмотреть записи, относящиеся к продажам в феврале, выполненным менеджером Голубковым. Другими словами, вам нужно исключить все записи, кроме тех, которые в поле Месяц содержат Февраль, а в поле Менеджер-Голубков.

Сначала включите режим Фильтр. Затем щелкните на кнопке раскрывающегося списка в поле Месяц и выберите Февраль. Из списка будут отобраны записи, в которых поле Месяц имеет значение Февраль. Затем щелкните на кнопке раскрывающегося списка в поле Менеджер и выберите Голубков. Список будет отфильтрован еще раз — по значениям в двух столбцах.

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

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

Обычно автоматическая фильтрация заключается в выборе одного значения в одном или нескольких столбцах. Если выбрать опцию Условие в раскрывающемся списке, то можно осуществить фильтрацию более гибким способом. При выборе опции Числовые фильтры/Настраиваемый фильтр... появится диалоговое окно Пользовательский автофильтр (рис.13.5). Это окно позволяет фильтровать списки с использованием нескольких критериев.

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

1. Значения больше или меньше установленного. Например, можно выбрать записи, указывающие на объемы продаж, превышающие 1 000.

2. Значения в интервале. Например, отобрать все записи, указывающие на объемы продаж, превышающие 1 000 И не превышающие 2 000.

3. Два отдельных значения. Например, отобрать записи, в которых находится информация об объеме продаж в январе ИЛИ феврале.

Можно использовать символы подстановки “*” и "?", чтобы отфильтровать список более гибким способом. Например, чтобы вывести на экран записи только о тех менеджерах, фамилии которых начинаются с буквы В, используйте шаблон В*.

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

Наложение условия по списку

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

Название Первые 10 - это просто общепринятый термин, действие же опции не ограничивается поиском только 10 "наибольших" элементов. На самом деле ее действие даже не ограничивается поиском только наибольших элементов. При выборе опции Первые 10 появится диалоговое окно Наложение условия по списку (рис.13.6).

Рис. 13.6 Наложение условия по списку

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

Построение диаграммы по данным отфильтрованного списка

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

Расширенная фильтрация

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

1. Определить более сложный критерий фильтрации.

2. Установить вычисляемый критерий фильтрации.

3. Перемещать копии строк, отвечающих определенному критерию, в другое место.

Установка диапазона критериев

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

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

2. Остальные строки должны содержать критерии фильтрации.

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

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

В этом примере диапазон критериев содержит только одну строку критериев. Поля в каждой строке диапазона критериев (исключая заглавную строку) соединены оператором И. Поэтому в отфильтрованном списке показаны только те строки, у которых одновременно поле Менеджер содержит фамилии, начинающиеся с буквы "Б", и в поле Всего содержится значение, превышающее 1000.

Чтобы выполнить фильтрацию, выберите команду Дополнительно, которая находится в группе Фильтр во вкладке Данные. Появится диалоговое окно Расширенный фильтр (рис.13.7).

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

Определите диапазон списка и диапазон критериев и убедитесь, что установлен переключатель Фильтровать список на месте. Щелкните на кнопке ОК, и список будет отфильтрован по заданным критериям.

Множественный критерий отбора

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

1. В поле Месяц содержится значение Январь И в поле Товар — значение Товар В.

2. ИЛИ в поле Месяц содержится значение Февраль И при этом значения в поле Всего превышают 1 000.

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

Текстовые и числовые критерии

При фильтрации можно использовать сравнения с числом или строковой (текстовой) константой с помощью операторов, таких, как равно (=), больше чем (>), не равно (<>) и т.д.

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

Табл.1.Операторы сравнения

Оператор	Тип сравнения
=	Равно
>	Больше чем
>=	Больше либо равно
<	Меньше
<=	Меньше либо равно
<>	Не равно

В табл.2 показаны примеры строковых критериев.

Табл.2.Примеры строковых критериев

Критерий	Действие
>K	Слова, начинающиеся с букв от Л до Я
<>C	Все слова, кроме начинающихся с буквы С
="Январь"	Все слова "Январь"
См*	Слова, начинающиеся с букв "См"
с*с	Слова, начинающиеся с буквы "с" и заканчивающиеся буквой "с"
с?c	Слова из трех букв, начинающиеся с буквы "с" и заканчивающиеся буквой "с"

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

Вычисляемые критерии

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

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

=Дата_окончания-Дата_начала>=30

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

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

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

1. Не используйте заголовки полей списка в диапазоне критериев для вычисляемого значения. Создайте новый заголовок или просто оставьте пустую ячейку.

2. Можно использовать любое количество вычисляемых критериев, а также сочетания вычисляемых критериев с невычисляемыми.

3. Не обращайте внимания на значения, возвращаемые формулами в диапазоне критериев. Они ссылаются на первую строку списка.

4. Если ваша вычисляемая формула ссылается на значения вне списка, используйте абсолютные, а не относительные ссылки. Например, вместо С1 используйте ссылку $С$1.

5. При создании формул вычисляемых критериев используйте первую строку списка " (не строку заголовков!). Используйте относительные, а не абсолютные ссылки. Например, вместо $С$5 используйте ссылку С5.

Другие возможности расширенной фильтрации

В диалоговом окне Расширенный фильтр представлены еще две опции, которые рассматриваются дальше.

1. Скопировать результат в другое место.

2. Только уникальные записи.

Копирование отобранных строк

Если вы выберете переключатель Скопировать результат в другое место в диалоговом окне Расширенный фильтр, то отобранные строки будут скопированы в другое место активного рабочего листа или на другой лист. Место определяется в поле Поместить результат в диапазон. Обратите внимание, что при использовании этой опции сам список не фильтруется.

Отображение только уникальных строк

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

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

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

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

Простая сортировка

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

Сортировка отфильтрованного списка выполняется только для видимых строк. Если фильтрация отменяется, то список окажется не отсортированным.

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

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

Более сложная сортировка

В отдельных случаях необходимо выполнить сортировку по двум или нескольким столбцам. Такая операция проводится, если сортировка по одному полю оставляет не отсортированными записи, соответствующие одинаковым значениям в сортируемом поле. Например, если список, приведенный на рис.8, отсортировать по месяцам, то строки для каждого месяца расположатся вместе. Однако иногда желательно, чтобы в пределах каждого месяца отдельные записи тоже были отсортированы, например, по значениям поля, в котором представлены менеджеры по продажам. В этом случае нужно выполнять сортировки по двум столбцам (Месяц и Менеджер).

Если необходимо выполнить сортировку по нескольким полям, выберите команду Данные/Сортировка - появится диалоговое окно Сортировка (рис.13.8). В раскрывающемся списке Сортировать по выберите поле и укажите порядок сортировки (по возрастанию или по убыванию) и нажмите Добавить уровень Затем сделайте то же самое для второго поля. Если необходимо отсортировать и по третьему полю, определите третье поле в третьем разделе. Щелкните на кнопке ОК, и строки быстро перегруппируются.

Рис. 13.8 Окно сортировки

Если результат сортировки оказался неудовлетворительным, нажмите комбинацию клавиш <Ctrl+Z>, чтобы вернуть список к первоначальному состоянию.

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

Если в диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры, на экране появится диалоговое окно Параметры сортировки, показанное на рис.13.9.

Рис.13.9 Параметры сортировки

Ниже приведено описание опций этого диалогового окна.

1. Учитывать регистр.

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

2. Сортировать. Позволяет выполнить сортировку по столбцам, а не по строкам (задано по умолчанию).

Правила сортировки в Excel

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

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

2. Текст. Сортируется в следующем алфавитном порядке:

3. 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 А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я По умолчанию во время сортировки строчные и прописные буквы не различаются. Однако есть возможность изменить это в диалоговом окне Параметры сортировки.

4. Логические значения. Ложь следует перед Истина.

5. Ошибочные значения. Такие ошибочные значения, как #знач! и #н/д, появляются в их первоначальном порядке и не сортируются по типу ошибок.

6. Пустые ячейки. Всегда появляются последними.

7. При выполнении сортировки по убыванию приведенные последовательности используются в обратном порядке (за исключением пустых ячеек, которые по-прежнему остаются последними).

Особый порядок сортировки

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

1. Сокращенные названия дней недели: Пн, Вт, Ср, Чт, Пт, Сб, Вс.

2. Полные названия дней недели: Понедельник, Вторник, Среда, Четверг, Пятница Суббота, Воскресенье.

3. Сокращенные названия месяцев: Янв, Фев, Map, Апр, Май, Июн, Июл, Авг, Сен, Окт, Ноя, Дек.

4. Полные названия месяцев: Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь.

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

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

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

Рис. 13.10 Список для подведения промежуточных итогов

Чтобы формулы промежуточных итогов вставлялись в список автоматически, поместите табличный курсор где-нибудь на списке и выберите команду на вкладке Данные в группе Структура команда Промежуточные итоги. Появится диалоговое окно Промежуточные итоги.

В этом диалоговом окне представлено несколько опций.

1. При каждом изменении в. В этом раскрывающемся списке показаны все поля вашего списка. Выбранное поле должно быть отсортировано.

2. Операция. Вам предоставлены на выбор 11 функций. Обычно используется функция Сумма. Она задана по умолчанию.

3. Добавить итоги по. В этом окне выводятся названия всех полей вашего списка. Разместите отметку радом с полем или полями, в которых вы хотите подвести промежуточные итоги.

4. Заменить текущие итоги. Любые существующие формулы итогов заменяются новыми.

5. Конец страницы между группами. Excel вставляет символ конца страницы после подведения каждого промежуточного итога.

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

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

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

Рис. 13.11 Рабочий лист после добавления промежуточных итогов

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

Студенты группы сдавали тесты по 7 предметам: физика, история, иностранный язык, теоретическая механика, политология, философия, экология и получили количество баллов от 0 до 100.

1. Введите данные в список, используя форму.

2. Отсортируйте фамилии студентов в алфавитном порядке.

3. Найти средний балл по всем предметам каждого студента.

4. Показать 5 студентов с лучшими и 5 студентов с худшими средними баллами.

5. Найти студентов, которые получили по истории более 80 баллов.

6. Вывести на экран список студентов, фамилия которых начинаются с буквы ”С” и которые имеют по экологии “автомат” (ставится за 57 баллов и выше).

7. Найдите студентов, которые получили от 50 до 70 баллов либо по философии, либо по истории.

8. Найти студентов у которых оценка по физике от 50 до 70 баллов.

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

10. Найти студентов у которых по истории и по философии от 30 до 70 баллов, а по иностранному и экологии от 50 до 85.

11. Отобразить список студентов, фамилии которых начинаются на “А”, набравших средний балл менее 50, но набравших по физике более 20 баллов, по истории - менее 40, по иностранному – более 10, по тер.меху – менее 60, по политологии – более 45, по философии – более 30 и по экологии – менее 40.

Дата добавления: 2015-05-26