Тема 4. 4. Создание, форматирование и вывод таблиц.

 

Формула – это краткая запись некоторой последовательности действий, приводящих к конкретному результату. Формула может содержать не более 1024 символов. Структуру и порядок элементов в формуле определяет ее синтаксис.  Все формулы в Excel должны начинаться со знака равенства. Без этого знака все введенные символы рассматриваются как текст или число, если они образуют правильное числовое значение.

Формулы содержат вычисляемые элементы (операнды) и операторы. Операндами могут быть константы, ссылки или диапазоны ссылок, заголовки, имена, функции.

По умолчанию вычисления по формуле осуществляется слева направо, начиная с символа «=». Для изменения порядка вычисления в формуле используются скобки.

Пример формулы:

=А1+В1

Пример функции:

= ВПР (A4;$A$34:$D$40;4; ЛОЖЬ)

В Excel включено 4 вида операторов: арифметические, текстовые, операторы сравнения, адресные операторы.

Арифметические операторы используются для выполнения основных математических вычислений над числами. Результатом вычисления формул, содержащих арифметические операторы, всегда является число. К арифметическим операторам относятся: +, -, *, /, %,^.

Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом вычисления формул, содержащих операторы сравнения, являются логические значения Истина или Ложь. К операторам сравнения относятся: =, >, <, >=, <=, <>.

Текстовый оператор & осуществляет объединение последовательностей символов в единую последовательность.

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

: - оператор диапазона, который ссылается на все ячейки между границами диапазона включительно;

, - оператор объединения, который ссылается на объединение ячеек диапазона. Например, СУММ (В5:В15,С15:С25);

“ “ – оператор пересечения, который ссылается на общие ячейки диапазона. Например, в формуле СУММ (В4:С6 В4:D4) ячейки В4 и С4 являются общими для двух диапазонов. Результатом вычисления формулы будет сумма этих ячеек.

Приоритет выполнения операций: операторы ссылок (адресные) «:», «,», «»;знаковый минус ‘-‘ вычисление процента %;арифметические ^, *, /, +, -;текстовый оператор &;операторы сравнений =, <, >, <=, >=, <>.

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

Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например «на две строки выше». При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула. Например, если в клетке С1 записана формула: =А1+В1, то при копировании ее в клетку С2 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1: =В1+С1.

Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки.Например: $ A $1

Смешанные ссылки - это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, фиксированный столбец и относительная строка: $D6.

Ссылки на ячейки других листов книги имеют следующий формат:  <имя раб. листа>! ссылка на ячейку, например: Лист2!А1:А10.

Если имя рабочего листа содержит пробелы, то оно заключается в одинарные кавычки, например: ‘лицевой счет’!А1:А10.

Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной. Например: Лист1: Лист5!$A$1:$D$3.

Ссылки на ячейки других книг имеют следующий формат:

[имя книги]<имя листа>!ссылка на ячейку, например: [книга2]Лист3!Е5:Е15.

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

Тема 4.5. Организация вычислений в таблицах.

 

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

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

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

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

В общем виде любая функция может быть записана в виде:

= <имя _ функции> (аргументы)

 Существуют следующие правила ввода функций:

1) Имя функции всегда вводится после знака «=».

2) Аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов.

3) Между именем функции и знаком «(«пробел не ставится.

4) Вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные.

Для ввода функций можно использовать Библиотеку функций.

В Excel определено семь ошибочных значений:

1).#ДЕЛ/0! - попытка деления на 0. Эта ошибка обычно возникает, если в формуле делитель ссылается на пустую ячейку.

2).#ИМЯ? – в формуле используется имя, отсутствующее в списке имен диалога ПРИСВОЕНИЕ ИМЕНИ. Excel также вводит это ошибочное значение в том случае, когда строка символов не заключена в двойные кавычки.

3).#ЗНАЧ! – выдается при указании аргумента или операнда недопустимого типа, например, введена математическая формула, которая ссылается на текстовое значение, а также в том случае, когда Excel не может исправить формулу средствами авто исправления.  

4).#ССЫЛКА! – отсутствует диапазон ячеек, на который ссылается формула (возможно, он удален).

5).#Н/Д – нет данных для вычислений. Аргумент функции или операнд формулы является ссылкой на ячейку, не содержащую данные. Любая формула, которая ссылается на ячейки, содержащие #Н/Д, возвращает значение #Н/Д.

6).#ЧИСЛО! – задан неправильный аргумент функции, например, √(-5). #ЧИСЛО! может также указывать на то, что значение формулы слишком велико или слишком мало и не может быть представлено на листе.

7).#ПУСТО! – в формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек.

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

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

Выбор формата данных осуществляется на вкладке Главная/в окне команд Формат. Для форматирования дробей и чисел с десятичной точкой в форматах используются следующие символы шаблонов:

# - символ указывает, что в данную позицию выводятся только значащие цифры, незначащие нули не отображаются. Например, ###,# 123,46 => 123,5; 0,12=>,1

0 (ноль) – символ указывает на отображение незначащих нулей. Например, #,00 1,2=>1,20; 1=>1,00.

? –при использовании этого символа до или после десятичной точки вместо незначащих нулей отображаются пробелы. Например,???,??? 1,2=> 1,2.

 (пробел) - символ указывает на необходимость вставки пробела в качестве разделителя групп разрядов числа. Например, # ### 10000=>10 000

Запятая - как символ определяет положение десятичной точки.

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

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

Д – для отображения дней: Д – дни 1 – 31; ДД – 01 –31; ДДД – Пн –Вс; ДДДД – Понедельник – Воскресенье.

М – для отображения месяца: М – 1 – 12; ММ – 01 – 12; МММ – янв – дек; ММММ – январь – декабрь; МММММ – первая буква месяца.

Г – для отображения лет: ГГ – 00 – 99; ГГГГ – 1900 – 9999.

ч – для отображения часов: ч – 0 – 23; чч – 00 – 23.

м – для отображения минут: м – 0 – 59; мм – 00 – 59.

с – для отображения секунд: с – 0 –59; сс – 00 – 59.

0 (ноль) – для отображения долей секунд: ч:мм:сс.00

AM/PM – для отображения двенадцатичасовой системы

[ ] – для отображения интервалов времени. Позволяет выводить значения, превышающие 24 часа, 60 минут или 60 секунд.

Форматы могут быть составными, т.е. иметь числовую и текстовую часть. Текстовая часть всегда является последней в формате. Символы шаблона:

“” \ - двойные кавычки или обратная косая черта указывают на необходимость вставки, начиная с позиции шаблона, текстовых символов, следующих за ним в формате. Например, 0,00р.»Излишки»;[красный]-0,00р.»Недостатки»

@ - указывает, что, начиная с этой позиции формата, могут выводиться любые символы (текст);

* - указывает, что, начиная с позиции символа в формате, необходимо многократно вывести символ, следующий за * (пока столбец не окажется заполненным по ширине);

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

Пользовательские форматы чисел, дат и времени.

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

Условное форматирование - это форматирование выделенных ячеек на основе условий, заданных числами и формулами. Предназначено для выделения данных. Если данные ячейки удовлетворяют заданным условиям, то к ячейке будут применены установленные форматы.