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

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

1. Выделите ячейку для размещения в ней формулы.
2. Введите знак "=" для определения формулы.
3. Выделите ячейку, которая должна быть представлена в формуле. Если должен быть выделен диапазон, выделите начальную ячейку, а затем протащив указатель мыши до конечной ячейки, — весь диапазон. Вокруг ячейки или диапазона появится подвижная рамка.
4. Введите оператор, например, сравнения или арифметический.
5. Выделите другую ячейку или диапазон. Если это последняя ссылка в формуле на ячейку или диапазон, то нажмите 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 (МЕДИАНА):

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

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

1. Результат появится в поле 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 (ТДАТА) служит для ввода текущего времени и даты в ячейку рабочего листа. В этом случае время и дата будут обновляться всякий раз, когда будет пересчитываться рабочий лист.

Решение уравнений. Поиск параметра. Поиcк решения. Как Вы уже знаете, формулы в Microsoft Excel позволяют определить значение функции по ее аргументам. Однако может возникнуть ситуация, когда значение функции известно, а аргумент требуется найти (т.е. решить уравнение). Для решения подобных проблем предназначена специальная функция Goal Seek (Подбор параметра). Поиск параметра. Специальная функция Goal Seek (Подбор параметра) позволяет определить параметр (аргумент) функции если известно ее значение. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение. Рассмотрим процедуру поиска параметра на простом примере: решим уравнение 10 * x - 10 / x = 15. Здесь параметр (аргумент) - x. Пусть это будет ячейка A3. Введем в эту ячейку любое число, лежащее в области определения функции (в нашем примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3. Введем формулу =10*A3-10/A3, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3. Теперь можно запустить функцию поиска параметра, выбрав команду Goal Seek (Подбор параметра) в меню Tools (Сервис). Введите параметры поиска: В поле Set cell (Установить в ячейке) введите ссылку на ячейку, содержащую необходимую формулу. Введите искомый результат в поле To value (Значение). В поле By changing cell (Изменяя значение ячейки) введите ссылку на ячейку, содержащую подбираемое значение. Кликните на клавише OK. По окончании работы функции на экране появится окно, в котором будут отображены результаты поиска. Найденный параметр появится в ячейке, которая была для него зарезервирована. Обратите внимание на тот факт, что в нашем примере уравнение имеет два решения, а параметр подобран только один - это происходит потому, что параметр изменяется только до тех пор, пока требуемое значение не будет возвращено. Первый найденный таким образом аргумент и возвращается к нам в качестве результата поиска. Если в качестве начального значения в нашем примере указать -3, тогда будет найдено второе решение уравнения: -0,5. Достаточно сложно правильно определить наиболее подходящее начальное значение. Чаще мы можем сделать какие-либо предположения об искомом параметре, например, параметр должен быть целым (тогда мы получим первое решение нашего уравнения) или неположительным (второе решение). Задачу поиска параметра при налагаемых граничных условиях поможет решить специальная надстройка Microsoft Excel Solver (Поиск решения). Поиcк решения. Надстройка Microsoft Excel Solver (Поиск решения) не устанавливается автоматически при обычной установке: В меню Tools (Сервис) выберите команду Add-Ins (Надстройки). Если диалоговое окно Add-Ins (Надстройки) не содержит команды Solver (Поиск решения), нажмите кнопку Browse (Обзор) и укажите диск и папку, в которой содержится файл надстройки Solver.xla (как правило, это папка Library\Solver) или запустите программу установки Microsoft Office, если найти файл не удается. В диалоговом окне Add-Ins (Надстройки) установите флажок Solver (Поиск решения). Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, связанных с формулой в целевой ячейке. Процедура изменяет значения во влияющих ячейках до тех пор, пока не получит оптимальный результат по формуле, содержащейся в целевой ячейке. Чтобы сузить множество значений, применяются ограничения, которые могут иметь ссылки на другие влияющие ячейки. Процедуру поиска решения можно также использовать для определения значения влияющей ячейки, которое соответствует экстремуму целевой ячейки, например, количество учебных занятий, обеспечивающее максимальную успеваемость. В диалоговом окне Solver (Поиск решения) так же, как и в диалоговом окне Goal Seek (Подбор параметра), необходимо указать целевую ячейку, ее значение и ячейки, которые следует изменять для достижения цели. Для решения задач оптимизации целевую ячейку следует указать равной максимальному или минимальному значению. Если Вы щелкните на кнопке Guess (Предположить), Excel сам попытается найти все ячейки, влиящие на формулу. Вы можете добавить граничные условия, кликнув на клавише Add (Добавить). Кликнув на кнопке Options (Параметры), можно изменить условия поиска решения: максимальное время поиска решения, количество итераций, точность решения, допуск на отклонение от оптимального решения, метод экстраполяции (линейная или квадратичная), алгоритм оптимизации и т.д. Вернемся к предыдущему примеру: для того, чтобы получить второе (неположительное) решение, достаточно добавить граничное условие A3<=-0.01. Так же как и при подборе параметра, на экране появится окно, в котором будет отображен отчет о результатах поиска требуемого решения. Само решение будет показано в предназначенных для него ячейках (в ячейке A3 отобразится значение -0.50). Надстройка Microsoft Excel Solver (Поиск решения) позволяет, также, решать системы уравнений или неравенств. Рассмотрим простой пример: попробуем решить систему уравнений x + y = 2 x - y = 0 Введем в ячейки, предназначенные для решения (A1:A2) произвольные величины, лежащие в области определения (начальные значения). В ячейки B1 и B2 внесем формулы, по которым должны вычисляться правые части уравнений (= A1 + A2 и = A1 - A2). Запустим Solver (Поиск решения) из меню Tools (Сервис). Выберем одну из ячеек, содержащих формулы, в качестве целевой ячейки (например, B1), сделаем ее равной 2. Кликнем на кнопке Guess (Предположить) для того, чтобы Excel определил влияющие ячейки (A1:A2). Добавим ограничение B2 = 0. Кликнем на клавише Solve (Выполнить). Результаты поиска отобразятся в предназначенных для решения ячейках (A1:A2), отчет о результатах появится на экране.