ü Проверьте действие всех представленных выше примеров шаблонов форматов.
ü Найдите точное представление десятичной дроби 0,678 в виде простой.
6.5. Форматы “Дата” и “Время”
Excel запоминает даты с помощью натуральных чисел в диапазоне от 1 до 2 958 465. Этому диапазону чисел соответствует диапазон дат от 1 января 1900 года до 31 декабря 9999 года. Для формирования шаблонов дат используются коды:
· М Отображает месяц в виде числа без ведущих нулей (1-12).
· ММ Отображает месяц в виде двузначного числа с ведущим нулем (01-12).
· МММ Отображает месяц в сокращенном виде (Янв-Дек).
· ММММ Отображает месяц в виде полного названия (Январь-Декабрь).
· Д Отображает день в виде числа без ведущих нулей (1-31).
· ДД Отображает день в виде двузначного числа с ведущим нулем (01-31).
· ДДД Отображает день недели в сокращенном виде (Пон-Вос).
· ДДДД Отображает день недели в виде полного названия (Понедельник-Воскресенье).
· ГГ или ГГГГ Отображает год как двузначный (00-99) или четырехзначный () номер года.
Рассмотрим примеры шаблонов форматов дат (в примерах сначала указано число, затем шаблон формата и результат применения формата к числу). Формат записи даты в Windows, как вы знаете, задается в программе Язык и Стандарты из Панели управления Windows. В данном случае использован формат, принятый в России, согласно которому через точку записываются день, месяц, затем год.
1.1.96 | ДД. ММ. ГГ | 01.01.96 |
1.1.96 | Д. МММ. ГГ | 1.янв.96 |
1.1.96 | ДДДД Д ММММ ГГГГ | Понедельник 1 январь 1996 |
Время в Excel задается несколько сложнее, чем дата. Один час представляется дробью 1/24, одна минута - 1/(12*60), а одна секунда - 1/(12*3600). Для записи форматов времени используются коды:
· ч или чч Отображает час как число без ведущих нулей (0-23), или как число с ведущими нулями (00-23). Если формат содержит символы AM или PM, то часы отображаются в двенадцатичасовом формате. В противном случае часы отображаются в 24-часовом формате.
· м или мм Отображает минуты как число без ведущих нулей (0-59), или как число с ведущими нулями (00-59).
· с или сс Отображает секунды как число без ведущих нулей (0-59), или как число с ведущими нулями (00-59).
· ] [ Отображает часы большие 24, или минуты и секунды большие 60. Ставьте квадратные скобки в самой левой части кода времени, например, код времени [ч]:мм:сс будет отображать число часов, большее чем 24.
· AM/am/A/a PM/pm/P/p Отображает часы в 12-часовом формате. Microsoft Excel отображает AM, am, A, или a для времени дня от полуночи до полудня, и PM, pm, P, или p для времени дня от полудня до полуночи. Если не используется индикатор AM/PM, значит, часы отображаются в 24-часовом формате. Символы АМ/PM следует набирать латинским шрифтом.
Ниже приведены примеры шаблонов форматов для отображения времени ( в примерах сначала указано число, затем шаблон формата и результат применения формата к числу). Здесь принят формат времени Windows, когда время записывается через двоеточие в порядке часы, минуты, секунды.
1:25:34 | чч:мм:сс | 01:25:34 |
15:25:34 | чч:мм:сс AM/PM | 03:25:34 РМ |
Можно создать шаблон для записи даты вместе со временем.
1.1.96 15:25:34 | Д. МММ. ГГ ч:мм | 1янв.96 15:25 |
Если вам требуется в таблице отобразить количество часов, большее чем 24 то поступите следующим образом.
80:25 | [ч]:мм | 80:25 |
Иначе Excel поправит вас, добавив слева дату, отсчитанную от начала своего календаря на вмещающееся в ваши часы количество суток.
80:25 | ч:мм:сс | 3.01.1900 8:25:00 |
Если требуется, можно выводить время в ячейку с точностью до сотых долей секунды. Для этого следует применить, например, такой шаблон чч:мм:сс,00 .
¿ Практикум
ü Проверьте действие всех представленных выше примеров шаблонов форматов.
ü Составьте шаблон формата, который при вводе в ячейку числа "1.1.96 15:23:45" выводил бы в ячейке "Понедельник 1 января 1996 года 15 часов 23 минуты 45 секунд".
ü Составьте шаблон формата, который бы выдавал в ячейку день недели в ответ на введенную дату. Узнайте, в какой день недели вы родились?
ü Для ячеек столбца “Год рождения” таблицы 2 задайте один из форматов категории “Дата”.
6.6. Форматирование данных как текста
Иногда требуется, чтобы вводимые в ячейку цифры были интерпретированы Excel как текст. Например, будет очень неудобно, если Excel воспримет номер телефона 22‑10‑25 как дату 22.10.2025. Чтобы вводимые цифры были записаны в память Excel как строка символов, можно начать набор цифр при их вводе с апострофа ' или кавычек ". Если вы хотите сделать данную операцию незаметной для пользователя таблицы, установите для соответствующей ячейки формат @, или просто выберите Текстовый в списке Числовые форматы на панели Число диалога Формат ячеек.
6.7. Форматы с заданным условием
Часто при форматировании возникает ситуация, когда в зависимости от какого-то условия требуется применить к вводимому в ячейку числу различные форматы. Частный случай этой ситуации мы уже рассмотрели, когда разные форматы применялись к различным по отношению к нулю числам.
Возможны и более сложные случаи. Например, нам требуется в таблице, содержащей оценки по экзамену, сделать так, чтобы в зависимости от введенной оценки в ячейку выводилась бы оценка и в скобках комментирующее сообщение (если оценка выше 2 - “зачет”, если оценка 2 или 1 - “незачет”, если будет введен 0 - “не явился”). В подобных случаях помогает код шаблона формата:
· [условие значение] Условие может быть <, >, =, >=, <=, <>, а значение может быть любым числом.
Допускается запись двух условий. Проверка условий осуществляется слева направо. Третий шаблон будет применен при невыполнении первых двух условий. Для рассмотренного примера шаблон формата будет выглядеть следующим образом:
[>2]#” (зачет)”;[>0]#” (незачет)”;#” (не явился)”
Более широкие возможности по условному изменению формата ячеек предоставляет диалог Условное форматирование (вызывается по команде Условное форматирование меню Формат), подробнее возможности этого диалога будут рассмотрены в разделе "Условное форматирование ячеек".
К сожалению шаблон формата не допускает вставки функций в условие, поэтому в более сложных случаях (например, когда надо изменить окончание слова в зависимости от количества: 1 день, но 5 дней; 31 день, но 30 дней и т. д.) лучше изменяющийся текст вывести в отдельную ячейку, в которую вставить соответствующую формулу.
¿ Практикум
ü Проверьте действие шаблона формата с условием.
6.8. Контрольные вопросы
46. Что подразумевается в Excel под термином "формат данных"? Какие группы форматов представлены в Excel? Что такое коды форматов? Назовите и охарактеризуйте коды, общие для всех шаблонов. Как принудительно отформатировать данные как текст?
47. Назовите базовые коды групп форматов "Числовой", "Денежный", "Финансовый". Приведите примеры шаблонов. Как действует формат, состоящий из трех шаблонов, перечисленных через точку с запятой? Приведите пример шаблона, позволяющего выделять отрицательные числа другим цветом.
48. Назовите базовые коды групп форматов "Экспоненциальный", "Процентный", "Дробный". Приведите примеры шаблонов. Как, используя формат "Дробный", перевести десятичную дробь в простую?
49. Каким образом в Excel организовано запоминание дат? Назовите базовые коды шаблонов "Дата". Приведите примеры шаблонов. Как, если потребуется, установить, например, американский стандарт даты?
50. Каково внутреннее представление данных типа "Время" в Excel? Назовите базовые коды шаблонов "Время". Приведите примеры шаблонов. Как отобразить в ячейке таблицы количество часов, большее чем 24?
51. Что такое формат с условием? Приведите свой пример использования формата с условием?
7. ФУНКЦИИ EXCEL
7.1. Включение функций в формулу, мастер функций
Excel имеет большой набор встроенных функций, способный удовлетворить запросы даже самого искушенного пользователя. Самый простой способ включить функцию в формулу - набрать ее вручную с клавиатуры при вставке формулы в ячейку.
Более удобный способ - воспользоваться Мастером функций. Мастер функций позволяет за два шага вставить функцию в пустую активную ячейку ( при этом автоматически ячейка станет вычисляемой, т. е. в ячейку будет вставлена формула, содержащая данную функцию) или добавить функцию в уже существующую в активной ячейке формулу (при этом функция будет вставлена в то место формулы, где находится курсор) . Для включения функции в формулу:
· Сделайте активной ячейку, предназначенную для вставки.
· Если ячейка уже содержит формулу, дважды щелкните по ней мышкой и установите курсор в место включения функции.
· Выполните команду Функция меню Вставка или щелкните мышкой по инструменту fx панели стандартных инструментов.
· В развернувшемся диалоговом окне Мастер функций - шаг 1 из 2:
Ÿ выберите категорию функций, затем саму функцию;
Ÿ щелкните по кнопке Ok.
· На развернувшейся панели формулы в соответствующих полях наберите аргументы функций и щелкните по кнопке Ok.
На панели формулы в качестве аргумента функции можно задать значения другой функции (допускается до 7 уровней вложенности). Для ввода функции в качестве аргумента следует выбрать имя этой функции в поле-списке в левой части строки формулы. Затем, чтобы снова вернуться к вводу аргументов функции верхнего уровня вложенности, щелкните мышкой по ее имени в строке формул.
Помимо автоматизации процесса набора функции, Мастер функций предоставляет краткую справочную информацию по каждой функции в процессе работы с ним. Исчерпывающую информацию по выбранной функции можно получить, если щелкнуть мышкой по кнопке Справка в диалоговом окне Мастер функций.
7.2. Математические функции
Ниже представлен почти полный перечень математических функций Excel (за исключением матричных функций):
ABS Возвращает модуль (абсолютную величину) числа
ACOS Возвращает арккосинус числа
ACOSH Возвращает гиперболический арккосинус числа
ASIN Возвращает арксинус числа
ASINH Возвращает гиперболический арксинус числа
ATAN Возвращает арктангенс числа
ATAN2 Возвращает арктангенс для заданных координат x и y
ATANH Возвращает гиперболический арктангенс числа
COS Возвращает косинус числа
COSH Возвращает гиперболический косинус числа
EXP Возвращает экспоненту заданного числа
LN Возвращает натуральный логарифм числа
LOG Возвращает логарифм числа по заданному основанию
LOG10 Возвращает десятичный логарифм числа
SIN Возвращает синус данного угла
SINH Возвращает гиперболический синус числа
TAN Возвращает тангенс числа
TANH Возвращает гиперболический тангенс числа
ГРАДУСЫ Преобразует радианы в градусы
ДВФАКТР Возвращает двойной факториал числа
ЗНАК Возвращает знак числа
КОРЕНЬ Возвращает положительное значение квадратного корня
КОРЕНЬПИ Возвращает значение квадратного корня из числа ПИ
МУЛЬТИНОМ Возвращает мультиномиальный коэффициент множества чисел
НЕЧЁТ Округляет число до ближайшего нечетного целого
НОД Возвращает наибольший общий делитель
НОК Возвращает наименьшее общее кратное
ОКРВВЕРХ Округляет число до ближайшего целого или до ближайшего кратного указанному значению
ОКРВНИЗ Округляет число до ближайшего меньшего по модулю целого
ОКРУГЛ Округляет число до указанного количества десятичных разрядов
ОКРУГЛВВЕРХ Округляет число по модулю до ближайшего большего целого
ОКРУГЛВНИЗ Округляет число до ближайшего меньшего по модулю целого
ОКРУГЛТ Возвращает число, округленное с желаемой точностью
ОСТАТ Возвращает остаток от деления
ОТБР Отбрасывает дробную часть числа, так что остается целое число
ПИ Возвращает число Пи
ПРОИЗВЕД Возвращает произведение аргументов
РАДИАНЫ Преобразует градусы в радианы
РИМСКОЕ Преобразует число в Арабской записи к числу в Римской как текст
РЯД.СУММ Возвращает сумму степенного ряда, вычисленную по заданной формуле
СЛЧИС Возвращает случайное число между 0 и 1
СЛУЧМЕЖДУ Возвращает случайное число между двумя заданными числами
СТЕПЕНЬ Возвращает результат возведения в степень
СУММ Суммирует аргументы
СУММЕСЛИ Суммирует ячейки, специфицированные заданным критерием
СУММКВ Возвращает сумму квадратов аргументов
СЧЁТЕСЛИ Подсчитывает количество непустых ячеек, удовлетворяющих заданному критерию внутри интервала
ФАКТР Возвращает факториал числа
ЦЕЛОЕ Округляет число до ближайшего меньшего целого
ЧАСТНОЕ Возвращает частное от деления нацело
ЧЁТН Округляет число до ближайшего четного целого
ЧИСЛКОМБ Возвращает количество комбинаций для заданного числа объектов
Как уже отмечалось, получить исчерпывающую информацию о функции можно, щелкнув мышкой по кнопке Справка в диалоговом окне Мастер функций. Поэтому подробно описывать каждую функцию не имеет смысла.
В качестве примера рассмотрим функцию СУММЕСЛИ. Вот какая информация содержится об этой функции в справке Excel.
Синтаксис: СУММЕСЛИ(диапазон; условие; диапазон_суммирования)
· Диапазон - это интервал ячеек, содержимое которых проверяется на соответствие условию.
· Условие - это критерий в форме числа, выражения или текста, который определяет, какая ячейка будет добавлена в сумму. Например, условие может быть выражено как 32, "32", ">32", "доллар".
· диапазон_суммирования - это интервал ячеек для суммирования. Ячейки в диапазоне_суммирования суммируются, только если соответствующие им ячейки в аргументе диапазон удовлетворяют заданному условию. Если диапазон_суммирования опущен, то суммируются все ячейки диапазона, удовлетворяющие условию.
Пример 7.2.1:
Пусть ячейки A1:A4 содержат следующие величины стоимости для четырех домов: руб., руб., руб., руб., соответственно. Пусть ячейки B1:B4 содержат следующие величины комиссионных при продаже соответствующих домов: 7 000 руб., 13000 руб.,руб.,руб.
A | B | C | D | E | F | G | |
1 | 7 000 | ||||||
2 | 13 000 | 58 000 | |||||
3 | 19 000 | ||||||
4 | 26 000 | ||||||
5 |
Тогда, если в ячейку D2 вставить формулу =СУММЕСЛИ(A1:A4;">160000";B1:B4), то результатом будет сумма вруб.
¿ Практикум
ü Используя данные из таблиц 1 и 2, найдите общий объем продаж по тем филиалам, стаж работы руководителей которых не превышает заданного времени, например 2-х лет.
ü Подсчитайте количество таких филиалов, используя функцию СЧЕТЕСЛИ.
7.3. Функции даты и времени
Прежде чем перейти к описанию функций даты и времени, перечислим форматы представления даты и времени в Excel, таких форматов четыре.
Формат 1: это представление даты и времени в виде, установленном для всех Windows-приложений в программе Стандарты из Панели управления Windows.
Формат 2: описан в разделе "Форматы представления данных" ( Форматы “Дата” и “Время” ), этот формат зависит от шаблона даты и времени, который установлен для соответствующей ячейки.
Формат 3 (текстовый формат): в этом случае дата и время, записанные в форматах 1 или 2, просто заключаются в кавычки.
Формат 4: числовой формат, соответствует внутреннему представлению даты и времени в Excel. Напомним, что Excel запоминает даты с помощью натуральных чисел в диапазоне от 1 до 2 958 465. Этому диапазону чисел соответствует диапазон дат от 1 января 1900 года до 31 декабря 9999 года. Время в Excel задается несколько сложнее, чем дата. Один час представляется дробью 1/24, одна минута - 1/(12*60), а одна секунда - 1/(12*3600). Таким образом, диапазону времени от 00:00:00 ( 12:00:00 ночи ) до 23:59:59 ( 11:59:59 вечера ) соответствует диапазон дробных чисел от 0 до 0,.
В числовом формате можно одним числом задать дату и время: цифры справа от десятичной запятой представляют время; цифры слева от десятичной запятой представляют дату. Поэтому фраза “дата в числовом формате” может означать числовой формат и времени и даты.
Вышесказанное о форматах представления даты и времени проиллюстрировано примерами в таблице 7.1.
Таблица 7.1.
Формат 1 | Формат 2 | Формат 3 | Формат 4 |
1.1.2000 | 1.янв.2000 | “1.янв.2000” | 36526 |
15:25:34 | 03:25:34 РМ | “03:25:34 РМ” | 0, |
1.1.2000 15:25:34 | 1янв.2000 15:25:34 | “1янв.2000 15:25:34” | 36526, |
А теперь перейдем непосредственно к функциям даты и времени. К ним в Excel относятся следующие функции:
ВРЕМЗНАЧ Преобразует время из текстового формата в числовой формат.
ВРЕМЯ Возвращает время в числовом формате для заданного времени.
ГОД Преобразует дату в числовом формате в год.
ДАТА Возвращает дату в числовом формате для заданной даты.
ДАТАЗНАЧ Преобразует дату из текстового формата в числовой.
ДАТАМЕС Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.
ДЕНЬ Преобразует дату в числовом формате в день месяца.
ДЕНЬНЕД Преобразует дату в числовом формате в номер дня недели.
ДНЕЙ360 Вычисляет количество дней между двумя датами на основе 360-дневного года.
ДОЛЯГОДА Возвращает долю года, которую составляет количество дней между начальной и конечной датами.
КОНМЕСЯЦА Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
МЕСЯЦ Преобразует дату в числовом формате в номер месяца.
МИНУТЫ Преобразует дату в числовом формате в минуты.
РАБДЕНЬ Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.
СЕГОДНЯ Возвращает текущую дату в формате 1.
СЕКУНДЫ Преобразует дату в числовом формате в секунды.
ТДАТА Возвращает текущую дату и время в формате 1.
ЧАС Преобразует дату в числовом формате в часы.
ЧИСТРАБДНИ Возвращает количество рабочих дней между двумя датами.
В качестве иллюстраций использования функций даты и времени рассмотрим несколько типичных примеров.
Пример 7.3.1.
Вывести в ячейку текущую дату. Для этого необходимо в ячейку вставить формулу =СЕГОДНЯ(). Чтобы вывести текущие дату и время следует воспользоваться функцией ТДАТА(). Данные функции выведут дату и время в формате 1 или, если предварительно ячейка отформатирована, то в формате 2.
Если же вам захочется вывести в отдельную ячейку только текущее время: установите для ячейки один из форматов категории Время и вставьте в ячейку формулу =ТДАТА()-СЕГОДНЯ().
Excel обновляет ячейки с формулами, выводящими текущую дату и время, после очередной загрузки файла рабочей книги или после выполнения перевычислений (автоматически время не обновляется, если данные в таблицах не изменялись, в этом случае для обновления времени надо нажать F9, даже если установлен режим автовычислений).
Если требуется, чтобы ячейка была заполнена текущей датой или временем один раз и далее ее содержимое не изменялось, надо предусмотреть для получения текущей даты или времени специальную обновляемую ячейку, а в заполняемую ячейку копировать только значение даты и времени. Для копирования только значения следует поместить содержимое обновляемой ячейки в буфер обмена, затем выполнить команду Специальная вставка меню Правка, установив в развернувшемся диалоге режим Значения. Логично эти действия оформить в виде макроса.
Пример 7.3.2.
Пусть ячейки А1 и А2 содержат соответственно текущие дату и время, а ячейки В1 и В2 некоторые произвольные дату и время. Найти, сколько полных суток и часов разделяют эти две даты.
Функция ЦЕЛОЕ(А1-В1) выдаст количество полных суток (напомним, что функция ЦЕЛОЕ() округляет число до ближайшего меньшего целого), а функция ЧАС(А2-В2) - количество часов. Функция ЧАС(дата_как_число) возвращает час, соответствующий заданной дате в числовом формате. Час определяется как целое в интервале от 0 (12:00 AM) до:00 PM).
Замечание.
Microsoft Excel для Windows и Microsoft Excel для Macintosh используют по умолчанию различные системы дат. Microsoft Excel для Macintosh использует Систему Дат 1904, в которой отсчет даты начинается со 2 января 1904 года, а не с 1 января 1900 года. Можно изменить используемую систему дат, устанавливая или сбрасывая флажок Система Дат 1904 на вкладке Вычисления диалогового окна Параметры (команда Параметры, меню Сервис).
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 5 6 7 8 9 10 11 |
