Создание и удаление пользовательского числового формата

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

Для создания пользовательского числового формата необходимо:

1) выделить ячейки, формат которых требуется изменить;

2) в меню Формат выбрать команду Ячейки, вкладку Число;

3) в списке Числовые форматы выбрать пункт (все форматы);

4) ввести в поле Тип коды числовых форматов.

 

Примеры кодов форматов (шаблоны)

Таблица 1

Отображается в ячейки Применили шаблон Будет отображаться в ячейки 1234,59 ####,# 1234,6 8,9 #,000 8,900 0,631 0,# 0,6   #,0# 12,0 Янв — Дек мммм Январь — Декабрь Дни от 1 до 9 дд Дни от 01 до 09   ###0,00" тонн" 100,00 тонн   ###" стран" 15 стран

 

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

 

 

Для удаления пользовательского числового формата необходимо:

1) в меню Формат выбрать команду Ячейки, вкладку Число;

2) в списке Числовые форматы выбрать пункт (все форматы);

3) из списка Тип выбрать пользовательский формат, который требуется удалить;

4) нажать кнопку Удалить.

 

Контрольные вопросы

1. Что понимается под списком в Excel?

2. Перечислите основные правила создания списков.

3. Как выполнить проверку вводимых значений?

4. Как открыть Форму данных?

5. Перечислите назначение кнопок диалогового окна Форма.

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

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

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

1) числа от наименьшего отрицательного до наибольшего положительного числа;

2) текст – по алфавиту;

3) логическое значение ложь;

4) логическое значение истина;

5) значения ошибок;

6) пустые значения.

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

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

На стандартной панели инструментов Excel расположены две кнопки для сортировки списка в возрастающем или убывающем порядке. Для сортировки данных выполнить следующее:

1) выделить ячейку в столбце, который следует использовать в качестве ключа сортировки;

2) нажать кнопку Сортировка по возрастанию, либо Сортировка по убыванию.

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

2.2. Сортировка по нескольким столбцам

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

Для сортировки данных выполнить следующее:

1) выделить диапазон ячеек, который необходимо отсортировать. Если необходимо отсортировать список целиком, достаточно выделить любую ячейку списка;

2) выполнить команду Данные, Сортировка. Откроется диалоговое окно Сортировка диапазона;

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

4) в раскрывающемся списке Сортировать по выбрать первое поле, по которому следует упорядочить данные. Это поле называется первым ключом сортировки;

5) выбрать положение переключателя по возрастанию или по убыванию;

6) выбрать имена полей, если требуется, в раскрывающихся списках Затем и В последнюю очередь, по. Второе поле сортировки используется, если возникает повторение значения первого поля, а третье поле – если повторяются значения и первого, и второго полей;

7) нажать кнопку ОК.

Использование особого порядка сортировки (по первому ключу)

Некоторые последовательности элементов должны выстраиваться не в алфавитно-цифровом порядке, а в некотором специальном порядке. К таким последовательностям, например, относятся дни недели или месяцы.

По умолчанию в Excel определены четыре стандартных списка:

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

2. Полные названия дней недели. Понедельник, Вторник, Среда,…

3. Сокращенные названия месяцев. Янв, Фев, Мар, Апр, Май,…

4. Полные названия месяцев. Январь, Февраль, Март, Апрель,…

Если необходимо упорядочить данные в особом порядке, надо выполнить следующее:

1) выполнить команду Данные, Сортировка;

2) в диалоговом окне Сортировка диапазона нажать кнопу Параметры;

3) откроется диалоговое окно Параметры сортировки. В раскрывающемся списке Сортировка по первому ключу выбрать подходящий вариант;

4) нажать кнопку ОК.

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

Примечание. В поле списка Сортировка по первому ключу по умолчанию выделен элемент Нет. Начиная с версии Microsoft Excel 2002, используется «Нет» вместо употреблявшегося в предыдущих версиях Excel слова «Обычный».

Контрольные вопросы

1. Что такое сортировка списка?

2. Обязательно ли выделение списка при выполнении Сортировки?

3. Как сортировать список по трем полям одновременно?

4. Возможна ли сортировка списка по четырем и более полям?

5. Что такое сортировка по первому ключу?

6. Как создаётся пользовательский порядок сортировки списка?

7. В каком порядке сортируются текстовые данные?

8. Как восстановить первоначальный вид списка после всех выполненных сортировок?

9. В каком порядке сортируются числовые данные?

10. В каком случае для сортировки списка используются кнопки на панели инструментов, а в каком – команда меню?

 

Вычисление итогов

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

1) активизировать одну из ячеек таблицы;

2) выполнить команду Данные, Итоги. Появится диалоговое окно Промежуточные итоги;

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

4) в поле списка Операция установитьфункцию (табл. 2);

5) в поле списка Добавить итоги выводятся названия всех полей списка. Активизировать поле или поля, для которых надо вычислить промежуточные итоги;

6) щёлкнуть кнопку ОК.

В диалоговом окне Промежуточные итоги имеются следующие опции:

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

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

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

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

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

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

Таблица 2

Функция	Назначение
Сумма	Суммирует все значения и выдает общий итог
Количество значений	Определяет количество элементов ряда
Среднее	Вычисляет среднее арифметическое для ряда значений
Максимум	Определяет наибольшее значение в ряде значений
Минимум	Определяет наименьшее значение в ряде значений
Произведение	Вычисляет произведение всех значений ряда
Количество чисел	Устанавливает количество ячеек, содержащих числовые значения, в выделенной области

 

Контрольные вопросы

1. Для чего используется команда Итоги?

2. Какие предварительные операции надо выполнить со списком, чтобы получить промежуточные итоги с помощью команды Данные, Итоги?

3. Какие встроенные функции могут использоваться при подведении промежуточных итогов?

4. Что появляется слева от таблицы после выполнения команды Итоги?

5. Как скрыть и отобразить вложенные уровни списка?

 

Фильтрация данных

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

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