Простой способ формирования ссылок.

Самый надежный способ получения правильной адресации ячейки или диапазона в формуле — это выделение последних с помощью мыши:

Выделите ячейку для размещения в ней формулы.
Введите знак "=" для определения формулы.
Выделите ячейку, которая должна быть представлена в формуле. Если должен быть выделен диапазон, выделите начальную ячейку, а затем протащив указатель мыши до конечной ячейки, — весь диапазон. Вокруг ячейки или диапазона появится подвижная рамка.
Введите оператор, например, сравнения или арифметический.
Выделите другую ячейку или диапазон. Если это последняя ссылка в формуле на ячейку или диапазон, то нажмите Enter или кнопку ввода в строке формул.

Вы увидите адреса ячеек или диапазонов в строке формул.

Если при выделении диапазона для включения в формулу Вы используете мышь, Excel автоматически вставляет оператор диапазона (:). При выделении несмежных ячеек или диапазонов также автоматически вставляется оператор объединения (;).

Именованные диапазоны.

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

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

Чтобы присвоить имя диапазону, следует выполнить следующее:

Выделите диапазон ячеек, которому Вы хотите присвоить имя.
Выберите последовательно команды Name (Имя), Define(Присвоить) меню Insert (Вставка).
В строке ввода Names in workbook (Имя) укажите имя диапазона.
Кликните на кнопке OK.

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

Абсолютные и относительные ссылки.

Как Вы уже знаете, каждая ячейка имеет свой адрес, который определяется соответствующими столбцом и строкой. Например, на пересечении столбца A со строкой 3 располагается ячейка A3. Такая запись называется - относительная ссылка. Если Вы переместите ячейку, формула, содержащая относительную ссылку на эту ячейку будет изменена так, чтобы обращаться уже к новой ячейке. Например, Вы ввели формулу =A3, после чего переместили ячейку A3 на одну позицию вниз. Теперь формула будет выглядеть так: =A4. Причем Excel сделает это автоматически, Вам не надо заботиться о корректировке формул после перемещения ячеек. Это удобно и в том случае, если Вы заполняете ячейки с помощью автозаполнения. Вам достаточно ввести формулу в одну ячейку, а затем протянуть за маркер автозаполнения, после чего во всех ячейках появятся скорректированные формулы.

Однако может возникнуть ситуация, когда ссылка на ячейку меняться не должна (например, несколько формул используют цену, которая постоянна для определенного вида товара). В этом случае необходимо использовать абсолютную ссылку, зафиксировав столбец и/или строку знаком $. Например, если ссылка выглядит так: =$B$1, то при автозаполнении все ячейки будут содержать формулу =$B$1.

Рассмотрим подробнее как изменяются ссылки при перемещении и копировании ячеек. Предположим, ячейка A3 содержит формулу =A1+$A$2. Здесь A1 - относительная ссылка, а $A$2 -абсолютная.

При перемещении ячеек, на которые ссылается формула (перенесем A1 в B1 и A2 в B2) относительные и абсолютные ссылки в формуле изменяются (ячейка A3 содержит теперь формулу =B1+$B$2).

Если Вы копируете ячейки, к которым обращается формула (скопируем A1 в B1 и A2 в B2, относительные и абсолютные ссылки в формуле останутся прежними (=A1+$A$2).

При перемещении ячейки, в которой находится формула (перенесем A3 в B3), относительные и абсолютные ссылки в формуле останутся прежними (=A1+$A$2).

Если Вы копируете ячейку, содержащую формулу (скопируем A3 в B3), относительные ссылки меняются, а абсолютные остаются прежними (=B1+$A$2).

При протягивании ячейки, в которой находится формула, за маркер атозаполнения (протянем A3 до C3), относительные ссылки меняются, а абсолютные остаются прежними (=C1+$A$2).

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

Циклические ссылки.

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

При попытке ввести формулу, содержащую циклическую ссылку, появится диалоговое окно, предупреждающее о возможной ошибке. Если Вы кликните на клавише OK, на экране отобразится панель инструментов Circular Reference (Циклические ссылки), которая позволит последовательно просмотреть каждую ячейку в циклической ссылке и внести необходимые изменения в формулу. При этом на рабочем листе появятся стрелки слежения, показывающие взаимосвязь между активной и связанными ячейками. Для перехода к следующей ячейке в циклической ссылке следует дважды щелкнуть на соответствующей стрелке слежения.

Однако циклическая ссылка не всегда является ошибкой. Циклические ссылки довольно-таки часто используются в научных и инженерных расчетах. При таких вычислениях значение каждой ячейки рассчитывается на основе результатов предыдущих итераций. Чтобы разрешить такие вычисления, следует включить флажок Iteration (итерации) на вкладке Calculation (Вычисления) диалогового окна Options (Параметры) меню Tools (Сервис). По умолчанию вычисления прекращаются после выполнения 100 итераций или после того, как изменение значения не будет превышать 0,001 за один шаг. Эти параметры тоже можно изменить, воспользовавшись вкладкой Calculation (Вычисления).

Ошибки в формулах Microsoft Excel.

Если формула обработана неправильно, Microsoft Excel отображает ошибку. Причины возникновения ошибок могут быть самыми разными:

##### - результат обработки формулы не умещается в ячейке или результатом выполнения формулы, оперирующей датами и временем, является отрицательное число.
#ЗНАЧ! - используется недопустимый тип аргумента или операнда.
#ДЕЛ/0! - в формуле предпринимается попытка деления на ноль.
#ИМЯ? - Excel не может распознать имя, используемое в формуле.
#Н/Д - неопределенные данные (чаще всего встречается, если некорректно определены аргументы функции).
#ССЫЛКА! - используется недопустимая ссылка на ячейку (например, ячейки, на которые ссылается формула, были удалены).
#ЧИСЛО! - возвращаемое числовое значение слишком велико или слишком мало, чтобы его можно было представить в Microsoft Excel (диапазон отображаемых чисел от -10³⁰⁷ до 10³⁰⁷).
#ПУСТО! - задано пересечение двух областей, которые в действительности не имеют общих ячеек.

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

Работа с функциями.

Использование мастера функций.
Повседневные функции.

Функция указывает программе, что делать. Она может заключать в себе множество операторов. Переменную часть функции называют аргументом. Это еще одно название значений, ссылок или текста. Аргументы — это данные, которые используются функцией для получения результата. У одних функций аргументы — это числа или текст. У других — даты и время. У некоторых функций может вообще не быть аргументов; у других их очень много.

В ячейку нельзя ввести просто функцию. Даже если кроме одной-единственной функции в ячейке ничего нет, все равно перед функцией необходимо поставить знак равенства, дающий знать Excel, что в ячейке — формула, пусть и состоящая из одной функции. Работу многих функций можно продублировать формулами, иногда даже не очень сложными. Но в этом нет необходимости. Ведь использовать функции вместо формул намного проще и удобнее (и быстрее!) — это неоспоримый факт. Кроме того, применяемая функция всегда даст правильный результат (если только Вы выберете соответствующую функцию). Одна из самых простых и часто используемых в Excel функций — это функция автоматического суммирования.

Использование мастера функций.

Не все функции в Excel определяются так же просто, как функция SUM (СУММ). Для некоторых из них нужно много аргументов, к тому же аргументы должны быть правильных типов и введены синтаксически правильно. Чем сложнее функция, тем сложнее правильно ее использовать. Мастер функций существенно облегчает запись функций.

Щелкните на кнопке Paste Function (Вставка функции), и мастер:

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

Изначально Excel в списке Function category (Категория) отображает стандартный список категорий функций. После того, как Вы поработаете с мастером функций, последний включит в список категорий перечень функций, использовавшихся последними («10 недавно использовавшихся»).

Рассмотрим работу мастера функций более подробно на примере вычисления серединного значения. Предположим, что Вам понадобилось найти медиану для диапазона значений (медиана - это середина множества чисел, т.е. половина чисел меньше этого значения, половина - больше). В этом случае придется обратиться к мастеру функций и использовать функцию MEDIAN (МЕДИАНА):

Выделите ячейку для помещения результата.
Щелкните на кнопке Paste Function (Вставка функции) и выберите из списка Function category (Категория) пункт "Statistical" ("Статистические"); затем прокрутите список имен функция найдите и выберите функцию MEDIAN (МЕДИАНА).

Щелкните на кнопке ОК, после чего на экране появится диалоговое окно.
Щелкните на поле "Number1" ("число 1"), чтобы поместить туда курсор (если он там отсутствует). С помощью мыши выделите диапазон значений для функции (в рассматриваемом примере это A6:C6). Можно также ввести каждое значение отдельно в полях Number1 (число 1), Number2 (число 2) и т.д. (Excel добавит при необходимости еще поля), но удобнее и быстрее выделить диапазон.

Результат появится в поле Formula result (Значение). Теперь щелкните на кнопке ОК. Медиана будет перенесена в выделенную ячейку.

Если вернуться и внимательно посмотреть на содержимое диалогового окна, то можно заметить, что Number1 (число 1) выделено полужирным шрифтом, тогда как Number2 (число 2) — нет. У функций бывают как обязательные аргументы, так и необязательные. Обязательные аргументы выделяются полужирным шрифтом. Для построения функции необходимо ввести обязательное значение.

Повседневные функции.

Функция SUM (СУММ) используется очень часто, а поэтому она выведена на стандартную панель инструментов (кнопка AutoSum (Автосумма)). Для вызова любых других функций следует щелкнуть на кнопке Paste Function (Вставка функции). Рассмотрим более подробно некоторые наиболее часто используемые функции:

Логические функции.

Функцию IF (ЕСЛИ) удобно использовать для проверки содержимого ячейки. Функция возвращает одно значение, если указанное значение истинно и другое, если оно ложно.

Статистические функции.

Функция COUNT (СЧЕТ) в Excel вычисляет количество чисел в списке аргументов. Эта функция считает также даты и время, т.к. внутреннее представление таких данных - численное. Эта функция игнорирует текст, логические значения или пустые ячейки, так как их нельзя конвертировать в числа.

Чтобы сосчитать не только числа, но и текстовые значения (например, количество клиентов в таблице), предусмотрена функция COUNTA (СЧЕТЗ). Эта функция считает количество значений в списке аргументов и игнорирует только пустые ячейки.

Функции даты и времени.

Всякий раз, когда Вы вводите дату, Excel преобразует введенное значение в порядковый номер. В связи с таким представлением дат календарь Excel имеет свои особенности: он начинается 1 января 1900 года и заканчивается 31 декабря 9999 года (это, так называемая, система дат 1900). При вводе даты в ячейку рабочего листа Excel она выглядит как нормальная календарная дата. Но во внутреннем представлении Excel даты — это порядковые номера от 1 до 2958325, потому что с 1 января 1900 года по 31 декабря 9999 года 2958325 дней. Таким образом, даты в интервале с 01.01.1900 по 31.12.9999 хранятся в Excel как соответствующий порядковый номер. Например, 31.05.57 хранится как 20971, т.к. между 01.01.1900 и 31.05.57 как раз 20971 день. Такое представление дат в Excel позволяет оперировать с ними как с числами. Система дат 1900 по умолчанию используется в Microsoft Excel для Windows.

Надо заметить, что в Microsoft Excel для Macintosh по умолчанию используется система дат 1904, когда за точку отсчета берется 2 января 1904 года. MS Excel для Windows позволяет выбрать эту систему дат, для этого достаточно установить флажок 1904 date system (система дат 1904) на вкладке Calculation (Вычисления) диалогового окна Options (Параметры) меню Tools (Сервис). Если Вы открываете документ, подготовленный для другой аппаратной платформы, смена системы дат произойдет автоматически.

Функция DATE (ДАТА) запрашивает ввод года, месяца и дня. Затем она возвращает соответствующую дату. То, в каком виде отображается дата в ячейке рабочего листа, зависит от режима форматирования этой ячейки. Но вне зависимости от внешнего представления Excel хранит дату в виде порядкового номера.

Предположим, Вы планируете реализацию очень важного проекта, и Вам нужно рационально распределить свое дефицитное время. Вам известно время начала и окончания работ. Теперь Вы хотели бы определить этапы проекта. Можно было бы поработать с календарем, а можно обратиться к функции DAYS360 (ДНЕИ360), сэкономив тем самым драгоценное время. Эта функция вычисляет количество дней между двумя датами на основе 360-дневного года (двенадцать 30-дневных месяцев).

При работе со временем Excel использует уже знакомое нам представление. Часы, минуты и секунды хранятся как числа от 0 до 0,99999999. Такой ряд десятичных дробей представляет все значения времени (с шагом в одну секунду) между 00:00:00 и 23:59:59 по двадцатичетырехчасовой шкале. Таким образом одна секунда = 1 / (количество секунд в сутках) = 1 / 86400 = 0,0000115740... Например, время 13:52 хранится как 0,5777...8. Для безошибочного ввода времени можно использовать функцию TIME (ВРЕМЯ). Она предлагает ввести часы (от 0 до 23), минуты (от 0 до 59) и секунды (от 0 до 59). Функция TIME (ВРЕМЯ) хранит указанное время в виде десятичной дроби, а отображает его в определенном Вами формате.

Надо заметить, что дата и время хранятся в одном том же числе, целая часть которого служит для запоминания даты, а десятичная - времени. Поэтому, если Вы введете только время, а потом посмотрите эти данные в формате даты, Вы увидите 0 Январь 1900, т.к. именно такая дата имеет внутреннее представление - 0.

Функция NOW (ТДАТА) служит для ввода текущего времени и даты в ячейку рабочего листа. В этом случае время и дата будут обновляться всякий раз, когда будет пересчитываться рабочий лист.