Система дат меняется автоматически, когда открывается документ, подготовленный в другой платформе.
¿ Практикум
ü Решите задачу, рассмотренную в примере 7.3.2.
ü Найдите, на сколько минут и секунд (дополнительно к часам) отличается время, записанное в ячейках А2 и В2.
7.4. Логические функции
Excel имеет шесть логических функций:
ЕСЛИ Определяет выполняемую логическую проверку.
И Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.
ИЛИ Возвращает ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.
ИСТИНА Возвращает логическое значение ИСТИНА.
ЛОЖЬ Возвращает логическое значение ЛОЖЬ.
НЕ Меняет на противоположное логическое значение своего аргумента.
Наибольший интерес представляет логическая функция ЕСЛИ.
Синтаксис функции:
ЕСЛИ(лог _ выражение; значение _ если _ истина; значение _ если _ ложь)
· Лог _ выражение - это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
· Значение _ если _ истина - это значение, которое возвращается, если лог _ выражение имеет значение ИСТИНА. Если лог _ выражение имеет значение ИСТИНА и значение _ если _ истина опущено, то возвращается значение ИСТИНА.
· Значение _ если _ ложь - это значение, которое возвращается, если лог _ выражение имеет значение ЛОЖЬ. Если лог _ выражение имеет значение ЛОЖЬ и значение _ если _ ложь опущено, то возвращается значение ЛОЖЬ.
Пример 7.4.1.
Вывести в ячейку сообщения: “Обед” - если текущее время находится в интервале от 13.00 до 14.00; “Работаем” - в остальных случаях.
Поместим в ячейку А1 текущее время, вставив в нее формулу =ТДАТА() - СЕГОДНЯ().
Заданное сообщение будет выдавать функция:
ЕСЛИ(И(А1>=ВРЕМЯ(13;0;0) ; А1<=ВРЕМЯ(14;0;0)) ; “ОБЕД” ; “РАБОТАЕМ ” )
Здесь функция ВРЕМЯ возвращает время в числовом формате для заданного времени.
Синтаксис: ВРЕМЯ(часы; минуты; секунды)
· Часы - это число от 0 до 23, представляющее час.
· Минуты - это число от 0 до 59, представляющее минуту.
· Секунды - это число от 0 до 59, представляющее секунду.
Функция И реализует операцию логического “И”.
Синтаксис: И(логическое _ значение1; логическое _ значение2; ...)
· Логическое _ значение1, логическое _ значение2, ... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
Аргументы должны быть логическими значениями, массивами или ссылками, которые содержат логические значения. Если аргумент, который является ссылкой или массивом, содержит тексты или пустые ячейки, то такие значения игнорируются. Если указанный интервал не содержит логических значений, то И возвращает значение ошибки #ЗНАЧ!.
¿ Практикум
ü Вывести в ячейку сообщения: “Обед” - если текущее время находится в интервале от 13.00 до 14.00; “Работаем” - если текущее время находится в интервале от 9.00 до 18.00 (кроме обеда); “Закрыто” - в остальных случаях.
7.5. Текстовые функции
К текстовым функциям относятся:
ДЛСТР Возвращает количество символов в текстовой строке.
ЗАМЕНИТЬ Заменяет символы в тексте.
ЗНАЧЕН Преобразует текстовый аргумент в число.
КОДСИМВ Возвращает числовой код первого символа в текстовой строке.
ЛЕВСИМВ Возвращает самые левые символы текстового значения.
НАЙТИ Ищет вхождение одного текста в другой (с учетом регистра).
ПЕЧСИМВ Удаляет все непечатаемые символы из текста.
ПОВТОР Повторяет текст заданное число раз.
ПОДСТАВИТЬ Заменяет новым текстом старый текст в текстовой строке.
ПОИСК Ищет вхождение одного текста в другой (без учета регистра).
ПРАВСИМВ Возвращает самые правые символы текстовой строки.
ПРОПИСН Делает все буквы в тексте прописными.
ПРОПНАЧ Делает прописной первую букву в каждом слове текста.
ПСТР Возвращает заданное число символов из строки текста, начиная с заданной позиции.
РУБЛЬ Преобразует число в текст, используя денежный формат.
СЖПРОБЕЛЫ Удаляет из текста пробелы.
СИМВОЛ Возвращает символ с заданным кодом.
СОВПАД Проверяет идентичность двух текстов.
СТРОЧН Делает все буквы в тексте строчными.
СЦЕПИТЬ Объединяет несколько текстовых элементов в один.
Т Преобразует свой аргумент в текст.
ТЕКСТ Форматирует число и преобразует его в текст.
ФИКСИРОВАННЫЙ Форматирует число и преобразует его в текст с заданным числом десятичных знаков.
Использование текстовых функций рассмотрим на примере формирования различных окончаний у слов в зависимости от конкретной ситуации.
Пример 7.5.1.
Необходимо в таблице вывести количественный состав фирмы в виде сообщения “Общая численность персонала фирмы - 200 человек”. Основная трудность здесь состоит в том, что в зависимости от цифры следует выводить в конце сообщения либо “человек”, либо “человека”. Предлагается следующий алгоритм:
· Если второй справа символ цифры есть единица, то выводить “человек”.
· Если второй справа символ цифры есть не единица, то, если первый символ цифры справа есть или “2” или ”3” или “4” - выводить “человека”, в противном случае - “человек”.
Пусть в ячейке А1 содержится сообщение "Общая численность персонала фирмы –", а в ячейку В1 вводится количество человек. В ячейку С1 будем выводить сообщение “человек” или ”человека”.
Для упрощения чтения конечной формулы поместим в ячейку А2 отформатированное и переведенное в текст число человек из ячейки В1. Для этого вставим в ячейку А2 формулу =ТЕКСТ(В1;”00”).
Функция ТЕКСТ форматирует число и переводит его в текст.
Синтаксис: ТЕКСТ(значение; строка_формат).
· Значение - это либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое значение.
· Строка_формат - это шаблон формата числа, записанный в текстовой форме (см. раздел "Форматы "Дата" и "Время").
Формат “00” позволяет в данном случае представить числа от 0 до 9 в виде строк текста “00”, “01” ... “09”. Это необходимо, так как предложенный алгоритм предусматривает анализ последних двух символов цифры. Двузначные числа и имеющие большее количество знаков будут переведены в текстовый формат без изменения.
Теперь для получения конечного результата осталось только вставить в ячейку С1 формулу:
=ЕСЛИ( ПСТР( A2; ДЛСТР(A2)-1; 1 ) = "1"; "человек"; ЕСЛИ( ИЛИ( ПРАВСИМВ( A2; 1)="2"; ПРАВСИМВ( A2; 1)="3"; ПРАВСИМВ( A2; 1 )="4"); "человека"; "человек"))
В этой формуле новыми являются следующие текстовые функции.
ПСТР - возвращает заданное число символов из строки текста, начиная с указанной позиции.
Синтаксис: ПСТР (текст; нач _ ном; число_литер)
· Текст - это текстовая строка, содержащая извлекаемые символы.
· Нач _ ном - это позиция первого символа, извлекаемого из текста.
· Число_литер указывает, сколько символов нужно вернуть.
ДЛСТР - возвращает количество символов в текстовой строке.
Синтаксис: ДЛСТР(текст)
· Текст - это текст, длина которого определяется. Пробелы считаются как символы.
ПРАВСИМВ - возвращает последние (самые правые) символы текстовой строки.
Синтаксис: ПРАВСИМВ(текст; число_литер)
· Текст - это текстовая строка, содержащая извлекаемые символы.
· Число _ литер - это количество извлекаемых символов. Если число _ литер опущено, оно полагается равным 1.
¿ Практикум
ü Решите задачу, рассмотренную в примере 7.5.1.
ü Составить таблицу, позволяющую рассчитать плату за прокат (например, автомобиля, телевизора и т. п.). Время проката рассчитывать как разницу между временем сдачи вещи в прокат и текущим временем. Применить гибкую систему тарифов, предусматривающую скидки за увеличение времени проката. Например, при сроке проката до 1 дня - тариф 520 р. \день; при сроке до 1 недели - 500 р. \день; при сроке до 1 месяца - 400 р. \день; при сроке до 1 года - 300 р. \день.
7.6. Функции ссылок и массивов
Функции из этого набора позволяют анализировать массивы данных, обращаясь к данным либо через прямую или косвенную адресацию ячеек, строк и столбцов, либо через просмотр и поиск ячеек с требуемой информацией. При описании действий функций используются новые, еще не знакомые вам понятия:
· Массив - прямоугольная область данных (общее понятие массива в Excel несколько более емкое, но в данном случае достаточно такой трактовки массива).
· Вектор - массив, который содержит только одну строку или один столбец.
Ниже представлен список функций ссылок и массивов.
АДРЕС Возвращает ссылку на одну ячейку в рабочем листе в виде текста.
ВПР Ищет значение в первом столбце массива и возвращает значение из ячейки в найденной строке и указанном столбце.
ВЫБОР Выбирает значение из списка значений.
ГПР Просматривает верхнюю строку массива и возвращает значение соответствующей ячейки.
ДВССЫЛ Возвращает ссылку, заданную текстовым значением.
ИНДЕКС Использует индекс для выбора значения из ссылки или массива.
ОБЛАСТИ Возвращает количество областей в ссылке.
ПОИСКПОЗ Ищет значения в ссылке или массиве.
ПРОСМОТР Ищет значения в векторе или массиве.
СМЕЩ Возвращает ссылку, смещенную относительно заданной ссылки.
СТОЛБЕЦ Возвращает номер столбца, на который указывает ссылка.
СТРОКА Возвращает номер строки, определяемой ссылкой.
ТРАНСП Возвращает транспонированный массив.
ЧИСЛСТОЛБ Возвращает количество столбцов в ссылке.
ЧСТРОК Возвращает количество строк в ссылке.
В качестве примера рассмотрим применение функции ПРОСМОТР.
Пример 7.6.1. Применение функции ПРОСМОТР.
Пусть задана таблица, содержащая табельные номера сотрудников фирмы и их Ф. И.О. Требуется:
· вывести в ячейку Ф. И.О. сотрудника по его табельному номеру, заданному в ячейке С2;
· вывести в ячейку табельный номер сотрудника по его Ф. И.О., заданным в ячейке С4.
А | В | С | |
1 | Табельный номер | Ф. И.О. | |
2 | 12367 | 34876 | |
3 | 34876 | ||
4 | 36987 | ||
5 | 43762 |
=ПРОСМОТР(С2;А2:А5;В2:В5) выдает фамилию по табельному номеру из ячейки С2. В данном случае функция возвратит
=ПРОСМОТР(С4;В2:В5;А2:А5) выдает табельный номер по фамилии из ячейки С4. В данном случае функция возвратит 43762.
Синтаксис функции:
ПРОСМОТР(искомое _ значение; вектор _ просмотра; вектор _ результата)
Функция ищет ячейку в векторе _ просмотра, содержимое которой равно искомому _ значению. После того, как такая ячейка найдена, функция возвращает содержимое соответствующей ячейки из вектора _ результата.
Если функция не может найти искомое значение, то подходящим считается наибольшее значение в векторе _ просмотра, которое меньше, чем искомое _ значение.
Замечание. Тексты в верхнем и нижнем регистре считаются эквивалентными. Функция может выдать неверное значение, если вектор _ просмотра не будет отсортирован в порядке возрастания.
Для того чтобы отсортировать вектор, выделите его, затем выполните команду Сортировка меню Данные.
¿ Практикум
ü Решите задачу, рассмотренную в примере 7.6.1.
ü Составьте таблицу со шкалой налогообложения. Вычислите сумму налога с начисленной зарплаты (величина начисленной зарплаты задается).
7.7. Контрольные вопросы
52. Какие типы функций входят в набор встроенных функций Excel? Как получить справочную информацию по любой функции? Каким образом можно вставить функцию в формулу? Как это сделать с помощью мастера функций?
53. Какие действия позволяют выполнить математические функции Excel? Приведите свои примеры использования функций СУММЕСЛИ и СЧЕТЕСЛИ?
54. Какие действия позволяют выполнить функции даты и времени? Как вывести в ячейку текущие дату и время? Как в Excel рассчитываются временные промежутки?
55. Каково назначение логических функций в Excel? Что такое логическое выражение, какие значения может принимать логическое выражение? Приведите свой пример использования функции ЕСЛИ.
56. Какие действия позволяют выполнить текстовые функции Excel? Предложите свой алгоритм для вывода одного из сообщений "день" \ "дня" \ "дней" после ячейки, где содержится срок проката в днях, в зависимости от количества дней. Составьте формулу, которая реализует этот алгоритм.
57. Для чего предназначены функции ссылок и массивов? Приведите свой пример практического использования одной из функций данной группы.
8. ОФОРМЛЕНИЕ ВНЕШНЕГО ВИДА РАБОЧИХ ЛИСТОВ
8.1. Модель ячейки в Excel
Информация, которую мы видим при отображении ячеек таблицы на экране компьютера - это только “надводная часть айсберга”. На самом деле каждая ячейка может хранить много больше информации, чем выводится на экран. Структура модели ячейки в Excel, отображающая ее многоуровневый характер, представлена на рис. 8.1.
А | ||||||||
1 | Отображаемая информация | |||||||
2 | Формат ячейки | |||||||
Внутреннее представление данных | ||||||||
3 | Формула | |||||||
Имя ячейки | ||||||||
4 | Комментарий |
| ||||||
Рис. 8.1 Структура модели ячейки в Excel.
Верхний уровень - это изображение на экране. Здесь отображаются отформатированные текст и числа. Формат отображения информации в ячейке сохраняется на втором уровне. Третий уровень хранит данные во внутреннем формате Excel. Четвертый уровень содержит формулу, если она, конечно, вставлена в ячейку. На пятом уровне хранится имя, которое может быть присвоено ячейке. А шестой уровень предусмотрен для записи комментирующей информации о ячейке.
Разбиение на уровни в данном случае, конечно, носит чисто условный характер.
8.2. Просмотр формата ячейки, формул и внутреннего представления данных
Вы уже знакомы с тем, как установить формат ячейки, вставить в нее формулу. Формат текущей ячейки можно посмотреть в диалоге Формат ячеек ( вызывается по команде Ячейки меню Формат. В частности на панели Число можно узнать, какой шаблон назначен данной ячейке. Вставленная в текущую ячейку формула выводится в строке формул.
Можно получить режим, когда в ячейки будут выведены вставленные в них формулы, а данные будут представлены во внутреннем формате Excel. Для этого следует:
· открыть диалог Параметры (команда Параметры меню Сервис);
· перейти на панель Вид;
· установить флажок в поле Формулы группы полей Параметры окна.
В этом режиме в ячейку также выводится внутреннее представление данных Excel. Например, вы сможете просмотреть внутреннее представление даты и времени (см. раздел " Функции даты и времени").
¿ Практикум
ü Просмотрите ранее созданные таблицы 1 и 2 в режиме вывода в ячейки формул и внутреннего представления данных.
ü Определите число, соответствующее текущей дате и времени. Для этого наберите в ячейке текущие дату и время (например, 12.10.2000 12:10) и нажмите Enter.
8.3. Комментарии
В каждую ячейку можно записать дополнительную текстовую информацию-комментарий:
· сделайте ячейку текущей;
· выполните команду Примечание меню Вставка;
· в развернувшемся окне наберите комментарий;
· щелкните мышкой за пределами окна комментария.
Ячейка, которая содержит комментарий, будет помечена индикатором в виде красной точки в правом ее верхнем углу.
На панели Вид диалога Параметры можно установить порядок вывода примечаний:
не отображать – индикатор и само примечание не выводятся;
только индикатор – выводится только индикатор, чтобы просмотреть примечание, надо установить мышь на индикатор примечания;
примечание и индикатор – окна примечаний и индикаторы постоянно присутствуют на рабочем листе.
Чтобы удалить комментарий, записанный в ячейке:
· сделайте ячейку текущей;
· выполните команду Изменить примечание меню Вставка;
· щелкните мышкой по границе развернувшегося окна примечания;
· нажмите Delete.
¿ Практикум
ü В таблице 1 объемов продаж компьютерной техники для ячейки, содержащей общий итог по всем филиалам и кварталам, запишите комментарий "Общий объем продаж за отчетный год".
8.4. Форматирование ячеек
Под форматированием ячеек, в отличие от форматирования данных, хранящихся в ячейках, в Excel понимается задание внешнего вида самих ячеек (обрамление) и их содержимого (цвет, тип и размеры шрифта; заполнение фона; порядок расположения текста в пределах ячейки).
По умолчанию все выполняемые установки будут применены к текущей ячейке, в этом смысле ячейка в Excel есть аналог абзаца в редакторе Word. Чтобы изменить формат блока ячеек, его следует предварительно выделить. Все действия по форматированию ячеек выполняются на панелях диалогового окна Формат ячеек, вызываемого по команде Ячейки меню Формат.
8.5. Задание цвета, типа и размера шрифта
Для изменения параметров шрифта:
· Выделите блок ячеек.
· Выполните команду Ячейки меню Формат.
· Перейдите на панель Шрифт.
· Произведите необходимые установки.
Надеемся, что вы еще не забыли текстовый редактор Word, поэтому установки данной панели не требуют комментарий. Отметим только, что здесь вы можете выбрать тип шрифта, установить его размер в пунктах, стиль (обычный, курсив, полужирный, полужирный курсив), выбрать цвет символов, тип подчеркивания, задать эффекты (зачеркнутый, верхний и нижние индексы). В поле Образец всегда можно посмотреть, какой вид будет иметь содержимое ячеек в выбранном формате.
Для выбора шрифта, его размера и стиля вы можете использовать поля и кнопки, расположенные на панели инструментов форматирования.
¿ Практикум
ü Измените на свой вкус параметры шрифтов названий, заголовков строк и столбцов таблиц 1 и 2.
8.6. Порядок расположения текста в пределах ячейки
Порядок расположения текста в пределах ячейки задается на панели Выравнивание диалогового окна Формат ячеек:
· Выделите блок ячеек.
· Выполните команду Ячейки меню Формат.
· Перейдите на панель Выравнивание.
· Произведите необходимые установки.
В группе Выравнивание по горизонтали задается горизонтальное выравнивание. Эта группа имеет опции: по значению (выравнивает текст по левому краю, числа, даты и время —по правому краю и центрирует логические значения и значения ошибок, это выравнивание по умолчанию), по левому краю, по правому краю, по центру, по ширине (выравнивает текст внутри ячейки по правому и левому краю; для того чтобы это имело смысл, нужно, чтобы внутри ячейки содержалось более одной строки текста), с заполнением, по центру выделения.
С помощью опции С заполнением можно заполнить ячейку повторяющимися символами, для этого перед вызовом диалогового окна Формат ячеек следует ввести этот символ в ячейку, а затем выделить ячеки одной строки для заполнения. Например, можно создать эффект границы путем помещения в ячейку символов тире (–) или звездочка (*), а затем использовать данный параметр для повторения этого символа вдоль всей строки.
Опция по центру выделения позволяет выровнять текст в ячейке относительно центра выделенного блока ячеек, для такого выравнивания перед вызовом диалогового окна Формат ячеек необходимо выделить блок ячеек, включая и выравниваемую.
Группа Выравнивание по вертикали позволяет расположить текст в ячейке на разной высоте, относительно верхнего и нижнего краев ячейки.
В группе Ориентация выбирается направление вывода символов в ячейке.
На панели Выравнивание имеется также переключатель Переносить по словам, который включает режим, позволяющий заполнять ячейку в несколько строк. В этом случае, при заполнении ячейки, если информация не вместилась в ячейку по ширине, текст будет перенесен в ячейку, стоящую ниже, если она пустая (при переносе слова не разбиваются). Если же стоящая ниже ячейка уже заполнена, то текущая ячейка будет увеличена по высоте и следующая строка текста будет записана в текущей ячейке (см. рис. 8.2.).
A | B | C | D | A | B | C | |||
1 | 1 | ||||||||
2 | Ввод текста в режиме без переноса | Ввод текста | |||||||
3 | 5123 | 2 | в режиме с | ||||||
4 | переносом | ||||||||
5 | 3 | 5 123 |
Рис. 8.2. Иллюстрация к режиму Переносить по словам.
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 5 6 7 8 9 10 11 |
