Работа с данными

ЛК №4-5
1) Форматирование чисел и текста.
Правильное и грамотное использование форматов позволяет улучшить восприятие выводимой информации и повысить результативность ее анализа.
Данные (числовые, текстовые, даты и время) в Excel выводятся в определенном формате. По умолчанию информация в ячейках отображается в формате Общий.
Необходимо помнить о различиях между хранимыми и отображаемыми значениями ( на хранимые значения в ячейках форматы не распространяются).
Можно выделить следующие категории форматов.
1. Общий
2. Числовой
3. Денежный

ЛК №4-5
4. Финансовый
5. Дата
6. Время
7. Процентный
8. Дробный
9. Экспоненциальный
10. Текстовый
11. Дополнительный
12. Все форматы
Для использования этих категорий необходимо выполнить команду Формат - Ячейки -вкладка Число.
Каждая категория может содержать различные форматы (простыми дробями, половинчатыми долями, Янв 99, 3.01.02 и т. д.), для каждого из которых приводится список его кодов (0,00; 0%; #” ”?/?? и т.д.). Эти коды называются форматными и состоят из символов (0, #, ?, Д, ч и т.д. ).
Рассмотрим каждую из названных категорий.

ЛК №4-5

ЛК №4-5
1.1 Общий
По умолчанию Excel отображает любое введенное значение в формате Общий. Этот формат отображает точно то, что вы ввели в ячейку, кроме трех случаев:
длинные числовые зн-я отображаются в экспоненциальном формате или округляются
формат Общий не отображает незначащие нули (вводим 12,00 , получим 12)
десятичная дробь, введенная без числа слева от десятичной запятой, выводится с нулем (вводим ,12 получим 0,12)
1.2 Числовой
Эта категория содержит четыре формата и позволяет выводить значения в виде целых чисел или чисел с фиксированной запятой и выделять отрицательные значения с помощью цвета.

ЛК №4-5
1.3. Денежный
Содержит четыре формата и позволяет управлять выводом денежной единицы. Все денежные форматы содержат пробел между разделителями разрядов.
1.4. Финансовый
Финансовый формат в основном соответствует денежному формату. Основные различия финансового и денежного форматов:
в Ф.Ф. перед кодом ставится символ *. При его отсутствии Ф.Ф. переходит в другую категорию
Ф.Ф. выводит денежную единицу с выравниванием по левому краю ячейки, а число выравнивается по правому краю ячейки
Ф.Ф. выводит тире вместо нулевых значений.
Поясним это на примере.

ЛК №4-5
Число десятичных знаков выбрано два.
1.5. Дата
Эта категория используется для отображения даты или даты и времени одновременно.
1.6. Время
Эта категория используется для отображения времени или даты и времени одновременно.
1.7. Процентный формат

ЛК №4-5
Форматы категории процентный выводят числа в виде процентов. В этом формате значения ячеек умножаются на 100 и выводятся с символом % в конце числа
1.8. Дробный
В дробном формате дробные значения выводятся как обычные, а не десятичные дроби. Пример. Число 123,5 отобразится как 123 1/2 или 123 8/16 в зависимости от применяемого формата
1.9. Экспоненциальный
Экс. формат используется для отображения очень малых или очень больших чисел.
В этом формате число отображается в виде аЕв, где
а - мантисса
в- порядок
Е - символ заменяющий число 10.

ЛК №4-5
Пример. Число 98765432198 эквивалентно в э.ф. 9,88Е+10, или 0,000000009 - 9,00Е-09.
1.10. Текстовый
Применение к ячейке текстового формата означает, что данные в этой ячейке должны трактоваться как текст и выравниваться по левому краю.
На практике числовая константа, форматированная как текст, рассматривается как число.
1.11. Дополнительные форматы
В эту категорию входят четыре формата:
два формата почтовых индексов
формат номера телефона
формат табельного номера
Эти форматы позволяют быстро вводить числа без ввода специальных символов (круглых скобок, тире), что упрощает работу с клавиатурой.

ЛК №4-5
Сохраняются и начальные нули, встречаемые в почтовых индексах.
Пример. Ввели число 3154432525 - формат номера телефона--- [phone]
1.12. Все форматы
Эта категория позволяет создавать пользовательские форматы
2) Создание ПЧФ
Рассмотрим особенности создания ПЧФ
При создании ПЧФ используется категория Все форматы и поле Тип окна Формат ячеек, куда вводятся специальные символы форматирования.
В качестве основы для создания собственного формата можно использовать один из существующих форматов Excel из категории Все форматы.

ЛК №4-5

ЛК №4-5
При создании пчф можно использовать следующие символы: 0; #; ?; , (десятичная запятая); %; / ; пробел; E- E+ e- e+ ; $ - + /(); \ ; _ (подчеркивание); " Текст" ; * (символ повторения); @ (метка текста). Кратко охарактеризуем некоторые из этих символов.

ЛК №4-5
3) Создание ПФ дат и времени
Существует около 15 встроенных форматов даты и вре -мени, однако по умолчанию Excel позволяет использо- вать только три(четыре) формата. Форматные коды для дат используемые по умолчанию: ДД. ММ. ГГГГ , ДД. МММ. ГГ, ДД. МММ или МММ ГГ. Для их расшифровки воспользуемся следующей таблицей.
Таблица символов для кодов дат
Технология создания пользовательских форматов дат такая же, как и при создании пользовательских числовых форматов. Такие форматы создаются с использованием букв-символов: Д, М, Г для дат и ч, м, с для времени.

ЛК №4-5
Пример. Применение различных форматных кодов к дате 24 февраля 2000г. Результаты приведены в таблице.
Для форматирования ячеек, содержащих время, по умолчанию, существует семь форматных кодов, приведенных в таблице (сл.15)
Пример. Форматирование введенного в ячейку значения 13:52:32,44

ЛК №4-5
Для расшифровки форматных кодов времени воспользуемся таблицей.

ЛК №4-5
Для объединенного формата даты и времени характерна( по умолчанию) запись кода ДД. ММ. ГГГГ ч:мм или ДД. ММ. ГГГГ ч:мм АМ/РМ. Объединенный формат отображает дату и время в одной ячейке. Т.е. запись 24 февраля 2000г. в 13ч 30 мин. будет представлена в ячейке как 24.02.2000 13: 30 или 24.02.2000 1:30 РМ.
3.1. Арифметические операции с датами и временем
А.о. Можно выполнять двумя способами:
1-й. Когда дата и время представлены в привычном виде, например, 10.01.01 и 13:02.

ЛК №4-5
Вычисления начинаются со знака равно, а дата и время заключаются в двойные кавычки (две даты вводятся в одну ячейку)
Пример.Для дат. =“13.05.01” - ”05.03.00” , что равно 434.
Или так А1=13.05.01 А2=05.03.00, а А3=А1-А2 и ячейке А3 определен числовой формат, тогда А3=434.
Для времени =“12:35” + “2:13”, что равно 14:48 или 0,61668)
Для дат и времени. =“20.01.2000 13:05” - “24.02.2000 13:30”, что равно -35,0174
2-ой. Ячейкам, в которых находятся значения даты и времени определен один из числовых форматов.

ЛК №4-5
После выполнения вычислений, ячейке в которой хранится результат может быть присвоен как числовой формат так и формат даты и времени.
Пример. 12:35 эквивалентно числу 0,52431 , а 2:13 - 0,09236, тогда 12:35+2:13=0,52431+0,09236=0,61667=14:48
4) Форматирование положительных, отрицательных, нулевых и текстовых значений
Пользовательские форматы могут содержать до четырех секций кодов, разделенных точкой с запятой. Каждая из этих секций «отвечает» за определенные константы.
(1-ая)Положительные константы;(2-ая) Отрица- тельные константы; (3-ая)Нулевая константа; (4-ая) Текстовые константы

ЛК №4-5
0,?0;#,00??;#,0;@
Если формат содержит одну секцию, то этот формат применяется к положительным, отрицательным и нулевым значениям. Если ф. Содержит две секции, то 1-ая секция применяется к положительным и нулевым значениям , а 2-ая только к отрицательным.
При форматировании текстовых значений возможны четыре случая (в ячейку вводим текст West) :
1. 1-ая секция;2-ая секция;3-ая секция;
2. 1-ая секция;2-ая секция;3-ая секция; ”Текст”
3. 1-ая секция;2-ая секция;3-ая секция
4. 1-ая секция;2-ая секция;3-ая секция;@
В ячейке получим: 1. Пусто 2. Текст 3. West 4.West

ЛК №4-5
Пример. Необходимо отформатировать ячейки так, чтобы значения в них выводились различно в зависимости от значения в каждой ячейке. Создадим ф.к. :
“К получению:”_-$#,0;”Кредит:”-$#,00;”Пустой счет ” 0,00;”Внимание:”@
В таблице показано действие созданного форм. кода:

ЛК №4-5
5) Изменение цвета значений
Для изменения цвета значений необходимо в поле Тип окна Формат ячеек в начале соответствующей секции ф.к. ввести в квадратных скобках имя нового цвета.
Пример. Создадим ф.к.
[Синий]0;[Красный]0;[Зеленый];[Фиолетовый]
Цвета можно задавать как ЦВЕТn, где n – число от 1..16
6) Условия
6.1. Условия в пользовательских форматах
В Excel можно управлять отображением чисел в зависимости от выполнения различных условий. Для этого надо добавить условия в первые две секции пользовательского формата.

ЛК №4-5
Третья секция – формат для констант не удовлетворя-ющих первым двум условиям. При этом условия проверяются последовательно, т.е. если выполняется первое условие, то анализ второго не производится. Можно использовать операторы сравнения: <,>,=,<=,>= , <> с любыми числами. Пример.
Вывести сумму синим цветом, если она превышает $500, а отрицательные значения красным.
[Синий][>500]_$##;[Красный]_$0;0
Условные форматы могут применяться при масштабировании числовых значений.
Пример. Для отчета необходимо переводить (мл) в (л) и (кл). Создадим ф.к.
[>999999]0пр.+пр._м”кл”;[<999]0_к”мл”;0пр._к_м”л”

ЛК №4-5
Действие созданного ф.к.
Т.о. используя условия можно повысить эффективность отображения данных.
6.2. Условное форматирование
Оно позволяет применять форматы к конкретным ячейкам, когда значения в них примут некоторые контрольные значения.

ЛК №4-5
Для его использования – выбрать команду Условное форматирование в меню Формат.
С его помощью можно задавать: цвет фона ячейки, размер и цвет шрифта, границы и т.д.
Можно задавать до трех условий.