Прикладная информатика (стр. 15 )

Таблица 9.2

Операторы сравнения Excel

Оператор сравнения	Значение	Пример
= (равенства)	Равно	A1=B1
> (больше)	Больше	А1> В1
< (меньше)	Меньше	А1< В1
>= (больше и равенства)	Больше или равно	А1>=В1
<= (меньше и равенства)	Меньше или равно	А1<=В1
< > (больше и меньше)	Не равно	А1<>В1

Текстовый оператор «&» (амперсант) используется для обозначения операции объединения последовательностей символов в единую последовательность. Например, результатом выполнения выражения «Северный» & «ветер» будет: «Северный ветер».

Адресные операторы объединяют диапазоны ячеек для осуществления вычислений (табл. 9.3).

Таблица 9.3

Адресные операторы Excel

Адресный оператор	Значение	Пример
: (двоеточие)	Оператор диапазона, ссылающийся на ячейки между границами диапазона включительно	В5:В15
, (запятая)	Оператор объединения, который ссылается на объединение ячеек диапазонов	СУММ(В5:В15,D5:D15)
(пробел)	Оператор пересечения, который ссылается на общие ячейки диапазонов	СУММ(В5:В15 А7:D7) В этом примере ячейка В7 является общей для двух диапазонов

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

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

Для использования функций в формуле нужно указать ее имя и аргументы. Имена большинства записываются с использованием символов кириллицы, однако есть функции, названия которых не переведены на русский язык. Функции выполняют вычисления над аргументами в указанном порядке, называемом синтаксисом. Например, функция СУММ складывает значения группы ячеек (для этой часто используемой функции есть специальная кнопка на панели инструментов Автосумма , а функция ПЛТ вычисляет величину выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки.

Список аргументов может состоять из чисел, текста, логических величин (например, ИСТИНА или ЛОЖЬ), массивов, значений ошибок (например, #Н/Д), функций или ссылок. Необходимо следить за соответствием типов аргументов. Аргументы перечисляются в круглых скобках после имени функции, отделяясь друг от друга точкой с запятой. Максимальное количество аргументов функции не должно превышать 30, а длина формулы не может быть более 1024 символов.

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

•  выбрав значок в панели инструментов Стандартная или в строке ввода формул;

•  нажать Shift+F3;

•  Вставка – Функция.

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

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

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

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

После вставки функции в Панели формул отображается имя функции и ее аргументы, а также возвращаемое функцией и формулой значение (рис. 9.3).

Рис. 9.3. Панель формул

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

Для ввода формулы, содержащей функцию, нужно:

•  указать ячейку, в которую следует ввести формулу, и нажать кнопку «=» – Изменить формулу;

•  в списке поля Функции с помощью «стрелки вниз» выбрать подходящую функцию и ввести ее аргументы. Если функция отсутствует в списке, выберите команду Другие функции...

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

Формулы массива и их ввод

Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива обрабатывает несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, с той разницей, что для ввода такой формулы используются клавиши Ctrl+Shift+Enter.

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

9.4. Редактирование данных

Выделение различных частей таблицы

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

Для выделения прямоугольного диапазона можно щелкнул левой кнопкой мыши в левом верхнем углу диапазона ячеек, за тем, удерживая нажатой клавишу Shift, щелкнуть в правом нижнем углу диапазона. Или очертить диапазон ячеек указателем мыши.

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

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

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

Excel позволяет изменять ширину столбцов и высоту строк. Paзмер столбцов и строк можно изменять, перетаскивая границу между заголовками столбцов и строк.

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

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

Для установки ширины столбца по умолчанию используется команда Формат – Столбец – Ширина.

Манипуляции с ячейками

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

Команды Правка – Заполнить – Вниз/Влево/Вправо/Вверх позволяют скопировать содержимое первой из выделенных ячеек во все остальные в заданном направлении (рис. 9.4.).

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

•  выделить ячейки, содержащие данные, которые нужно скопировать или переместить;

•  для перемещения выбранных символов выбирается команда Вырезать, а для копирования – команда Копировать;

•  указывается левый верхний угол диапазона, в который следует поместить вырезанные или скопированные ячейки;

•  Вставка – Вырезанные/Скопированные ячейки;

•  выбрать направление, в котором следует сместить соседние ячейки.

Для копирования форматов одних ячеек на другие нужно выделить ячейку или диапазон ячеек, формат которых необходимо скопировать, и нажать кнопку Формат по образцу на панели инструментов Стандартная, затем нужно выделить диапазон ячеек, в который необходимо скопировать формат. Вместо копирования содержимого ячеек целиком можно скопировать только часть, например значение, формулу, примечание или формат ячейки, без копирования самой формулы, тогда используется команда Копировать. Затем указывается левый верхний yгол области вставки и выполняется команда Правка – Специальная вставка, в блоке Вставить выбирается нужный параметр.

Рис. 9.4. Запуск команды Правка – Заполнить

Рис. 9.5. Диалоговое окно «Специальная вставка»

Для удаления содержимого ячеек используется клавиша Del. Команда Правка – Удалить используется для удаления самих ячеек или строк/столбцов.

Для вставки новых ячеек (строк/столбцов) используется команда Вставка – Ячейки (Строки/Столбцы).

Перемещение и копирование формулы

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

1. Выберите ячейку, содержащую копируемую или перемещаемую формулу.

2. Установите указатель на рамку выделения и нажмите левую кнопку мыши.

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

Чтобы скопировать ячейку, при перетаскивании удерживайте нажатой клавишу Ctrl.

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

Формула в MS Excel выполняет вычисления над значениями на листе. Обычно формула задает вычисления над всеми значениями в заданном диапазоне.

Когда формула должна выполнять проверку условия, можно использовать условные формулы в Excel. Excel включает три функции, которые вычисляют результат в зависимости от выполнения условия. Чтобы подсчитать число появлений определенного значения в диапазоне ячеек, используйте функцию СЧЁТЕСЛИ. Чтобы подсчитать общую сумму с использованием одного условия, применяйте функцию СУММЕСЛИ. Чтобы вернуть одно из двух значений используйте функцию ЕСЛИ.

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

Функция ЕСЛИ проверяет условие, которое может принимать знчения ИСТИНА или ЛОЖЬ. Если условие выполняется, функция возвращает одно значение, если условие ложно – другое. Функция имеет три аргумента: проверяемое условие, значение, возвращаемое при истинном условии, и значение, возвращаемое при ложном условии.

9.5. Форматирование данных

Формат числа

Формат числа, назначаемый ячейке, определяет способ просмотра числа на листе.

Числовые форматы позволяют изменять внешний вид чисел, включая даты и время, не изменяя внутреннее представление чисел (см. рис. 9.6).

Рис. 9.6. Примеры различных форматов числа в ячейках

Чтобы изменить числовой формат, выделяются ячейки, содержащие числа, и выбирается команда Формат – Ячейки – вкладки Число или кнопка требуемого формата (см. табл. 9.4).

Таблица 9.4

Кнопки форматирования числа

Для отображения	Нажмите кнопку
Разделителей групп разрядов	Формат с разделителями
Символов валюты	Денежный формат
Процентов	Процентный формат
Меньшего количества знаков после запятой	Уменьшить разрядность
Большего количества знаков после запятой	Увеличить разрядность

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

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

Можно создавать собственные пользовательские форматы: Формат – Ячейки – вкладка Число – Все команды – ввести нужную последовательность форматирующих символов. Примеры приведены в таблице ниже.

Пользовательский числовой формат может содержать до четырех частей (спецификаций), разделенных точками с запятой, каждая из которых определяет формат соответственно для положительных отрицательных, нулевых чисел и текста:

Положительный # ###,00_)

Отрицательный [Красный] (# ###,00)

Нулевой 0,00

Текстовый продажи @

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

Используются следующие шаблоны:

Таблица 9.5

Шаблоны отображения значений

Для отображения	Шаблон	Чтобы показать	Используется формат
Числа	# – выводятся только значащие числа, не-значащие нули не отображаются	1234,59 как 1234,6	####,#
0 (нуль) – отображаются незначащие нули	1234,568 как 1234,57	#,0#
? – до и после десятичной запятой вместо незна-чащих нулей отобра-жаются пробелы	44,,65 2,8 выровненными по десятичному разделителю 5,25 как5 1/4 или 5,3 как 5 3/10	???,??? # ???/???
Дней, месяцев и лет	AM или РМ – если применяется 12-часовая система, где «AM» или «А» указывает на время суток от полуночи до полудня, а «РМ» или «Р» указывает на время суток от полудня до полуночи. В противном случае используется 24-часовая система. Коды «м» или «мм» должны стоять непосредственно после кодов «ч» или «чч» или непосредственно перед кодом «ее», в противном случае Excel отображает месяц, а не минуты.
Проценты	% — знак процента
Представле-ния в экспонен-циальном виде	Е-, Е+ (е-, е+) – если формат содержит коды 0 (нуль) или # (знак номера) справа от кода экспоненты, Excel отображает числа в экспоненциальном формате и включает символы «Е» или «е.» Количество знаков 0 или # справа от кода соответствует количеству цифр в показателе степени. При задании кодов Е - или е - перед отрицательным показателем степени отражается знак минус. При задании кодов Е+ или е+ перед отрицательными показателями степени ставится знак минус, а перед положительными – плюс.
Добавление символов	« » (двойные кавычки) – для отображения числа и текста. Например, для отображения отрицательных сумм в виде «Дефицит -125,74р.» задается формат «Избыток» 0,00р.; «Дефицит» -0,00р.. Кавычки использовать не нужно для отображения пробела и символов: $ – + /():! ^ & ' (правая и левая одинарные кавычки) ~ { } = < > \ (косая черта) – перед одиночным символом.
Включение раздела для текста	@ – например, «общие поступления для »@
Добавление интервала	__ (подчеркивание) – для задания интервала, имеющего шири ну символа. Например, (_))– положительные числа будут расположены в одну колонку с отрицательными, заключенными в скобки.
Повторяю-щиеся символы	* – например, для заполнения дефисами пустой области ячейки справа от числа следует указать 0*-

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

Формат	Содержимое ячейки	Вид на экране
«Расход: »# ##0,00 ) р.; «Приход: »(# ##0,00) р.; «Не делится»; «Ошибка: » @	12,98	Расход: 12,98 р.
-12,98	Приход: (12,98) р.
0	Не делится
Это не счет!	Ошибка: Это не счет

С помощью кодов формата можно также изменять цвет выводимых на экран данных, для этого в поле Код нужно ввести название нового цвета в квадратных скобках ([Черный] [Голубой] [Розовый] [Белый] [Синий] [Зеленый] [Красный] [Желтый]) перед кодом формата, например

[Синий]# ##0 р.; -# ## р.

Задаваемый таким образом цвет имеет более высокий приоритет, чем цвет, задаваемый путем выбора кнопки «Цвет шрифта» т панели инструментов Форматирование или Формат – Ячейки – вкладка Шрифт.

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

[Синий][>500000]# ## р.; [Красный][<0]-# ##0 р.; # ##0 р.,

то данные в ячейке с числами свыше 500000 будут выведены синим цветом, отрицательные числа – красным цветом, а все остальные – цветом по умолчанию (черным). Задав пустой формат ;;; , можно скрыть данные в ячейке.

Для удаления пользовательского формата Формат ячеек – вкладка Число – Удалить. Удалить встроенные форматы нельзя.

Форматирование таблиц

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

Чтобы отобразить все данные ячеек без изменения размеров строк или столбцов, задайте на вкладке Выравнивание (Формат – Ячейки) размещение в ячейке нескольких строк данных. Чтобы текст уместился в ячейке, его можно изобразить в сжатом виде.

Основные параметры формата листа

К параметрам формата листа относятся: высота строк, ширина столбцов, выравнивание данных в ячейках. Кнопки выравнивания панели инструментов Форматирование служат для выравнивания текста по горизонтали. Чтобы разместить данные другим образом, например разместить заголовки столбцов под углом, выберите Формат – Ячейки – Выравнивание.

Объединение ячеек для ввода текста

При создании заголовков столбцов в пользовательских таблицах, так называемой «шапке» таблицы, часто используют прием, позволяющий отобразить текст, введенный в одну ячейку таблицы, сразу в нескольких соседних с ней ячейках. Этот прием заключается в использовании кнопки Объединить и поместить в центре , расположенной на панели инструментов Форматирование. Предварительно нужно ввести в одну ячейку текст, затем выделить объединяемые ячейки, включая ячейку с введенным текстом, затем нажать на эту кнопку. Другой способ для объединения – Формат – Ячейки – вкладка Выравнивание – флажок Объединение ячеек. Чтобы отказаться от объединения ячеек, нужно сделать активной зону объединенных ячеек и, вызвав тот же пункт меню, снять флажок Объединение ячеек.