Относительные ссылки

Если вы ставите в какой то ячейке знак «=", затем щелкаете левой кнопкой мыши на какой то ячейке, Excel подставляет после "=" относительную ссылку на эту ячейку. Эта ссылка "запоминает», на каком расстоянии (в строках и столбцах) Вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили «=" (смещение в строках и столбцах). Например, вы щелкнули на ячеку 3-мя столбцами левее и на 2 строки выше. Если после нажатия Enter потянуть вниз за маркер автозаполнения, эта формула скопируется во все ячейки, через которые мы протянули. И в каждой ячейке эта ссылка будет указывать на ячейку, расположенную на 3 столбца влево и 2 строки вверх ОТНОСИТЕЛЬНО положения ссылки. Это можно проверить, дважды щелкнув на одной из скопированных формул, или выделив ее, и нажав F2. Для лучшего понимания вспомните, как ходит шахматный конь. Он ходит буквой "Г» и из центра доски бьёт 8 клеток. «Упростим» немного правило хода коня: представим, что он может ходить только одной буквой «Г» — 2 клетки вперед и одну вправо. На какую бы клетку доски мы не поставим коня, каждый раз он ОТНОСИТЕЛЬНО своего положения отсчитывает смещение в строках и столбцах — 2 строки вверх и один столбец влево. Точно таким же образом работают относительные ссылки, только правило их «хода» задает пользователь. Каждый раз, когда мы тянем за маркер автозаполнения формула, содержащую относительные ссылки, Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с их «правилом хода» (у каждой относительной ссылки в формуле может быть свое «правило»).

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

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

Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной такого типа A$1, если третий раз — такого $A1, если в четвертый раз — ссылка опять станет относительной. И так по кругу.

Смешанные ссылки

Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки. Это самый сложный для понимания тип ссылки. Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных форумулах будут указывать на ячейку A1, то есть будет вести себя как абсолютные. Однако, если потянем вправо или влево — ссылки будет вести себя как относительные, то есть Excel будет пересчитывать ее адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится номер столбца (A, B, C...).

[править] Именованные ссылки

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

Для создания именованной ссылки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ссылка может ссылаться на несвязный диапазон ячеек (выделенный с "Ctrl").

Для вставки именованной ссылки можно воспользоваться кнопкой со стрелкой вниз:

или нажать клавишу "F3", откроется следующее окно:

Пример использования: "=СУММ(tablica_1);"

Тема 13	Литература:
Формулы в Microsoft Excel
	Содержание
; ; .

Общие сведения

Excel - программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Формулой Excel считает все, что начинается со знака «=". Если в ячейке написать просто "1+1», Excel не будет вычислять это выражение. Однако, если написать «=1+1» и нажать Enter, в ячейке появится результат вычисления выражения - число 2. После нажатия Enter формула не пропадает, ее можно увидеть снова, если сделать двойной щелчок по ячеке, или если выделить ее и нажать F2 или просто нажать Ctrl+Апостроф. Также ее можно увидеть в панели инструментов «Строка формул», если опять же выделить ячейку. После двойного щелчка, нажатия F2 или после щелчка в строке формул, можно изменить формулу, и для завершения нажать клавишу Enter.

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

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

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

Использование ссылок в формулах

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

Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень удобно. Пример: напишите в какой либо ячейке формулу «=A1+D1», нажмите Enter, затем два раза щелкнете по ячейке. В ячейке вы увидите формулу с разноцветными ссылками, а вокруг ячеек A1 и D1 будут прямоугольники соответствующих цветов. Гораздо проще найти, куда указывет ссылка, по цвету прямоугольника, чем просматривать буквы столбцов и номера строк. Наведите курсор мыши на один из разноцветных прямоугольников и перетащите левой кнопкой за границу в другое место. Вы увидите, что при этом меняются и адреса ячеек в формуле — часто это самый быстрый способ подправить адреса в формуле, особенно после копирования маркером автозаполнения.

[править] Операторы

Операторы в Excel бывают бинарные и унарные. Бинарные операторы работают 2 значениями. Например, оператор «*» умножает число слева от себя на число справа от себя. Если число слева или справа опустить, то Excel выдаст ошибку.

Унарные операторы оперируют одним значением. Пример унарных операторов: унарный «+» (ничего не делает), унарный «-» (меняет знак числа справа на противоположенный) или знак «%» (делит число слева на 100).

[править] Арифметические операторы

• «+» — сложение (Пример: «=1+1»);
• «-» — вычитание (Пример: «=1-1»);
• «*» — умножение (Пример: «=2*3»);
• «/» — Деление (Пример: «=1/3»);
• «^» — Возведение в степень (Пример: «=2^10»);
• «%» — Процент (Пример: «=3 %» — преобразуется в 0,03; «=37*8 %» — нашли 8 % от 37). То есть если мы дописываем после числа знак «%», то число делится на 100.

Результатом вычисления любого арифметического выражения будет число

[править] Логические операторы

• «>» — больше;
• «<" — меньше;
• ">=» — больше, либо равно;
• «<=" — меньше, либо равно;
• "=" — равно (проверка на равенство);
• "<>» — неравно (проверка на неравенство).

[править] Оператор объединения 2-х строк текста в одну

Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк. Например, в ячейке A1 текст «мама», в ячейке A2 текст «мыла раму». В A3 пишем формулу «=A1 & A2». В результате в ячейке A3 появится текст «мамамыла раму». Как видим, пробел между двумя строками автоматически не ставится. Чтобы вставить этот пробел, нужно изменить формулу вот так: «=A1 & " " & A2».

[править] Операторы ссылок

• : (двоеточие). Ставится между ссылками на первую и последнюю ячейку диапазона. Такое сочетание является ссылкой на диапазон (A1:A15);
• ; (точка с запятой). Объединяет несколько ссылок в одну ссылку (СУММ(A1:A15;B1:B15));
• (пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8).

[править] Выражения

Выражения в Excel бывают арифметические и логические. Арифметическое выражение (например, «=2*(2+5)», результат — 14) в результате дает числовое значение (положительное, отрицательное, дробное число). Логическое выражение (например, «=3>5», результат — логическое значение «ЛОЖЬ»)в результате может дать лишь 2 значения: «ЛОЖЬ» или «ИСТИНА» (одно число либо больше другого, либо не больше, других вариантов нет).

[править] Функции в Microsoft Excel

В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь используется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок (кода), предназначенный для решения каких-то задач.

Все функции в Excel характеризуются:

• Названием;
• Предназначением (что, собственно, она делает);
• Количеством аргументов (параметров);
• Типом аргументов (параметров);
• Типом возвращаемого значения.

В качестве примера разберем функцию «СТЕПЕНЬ»

• Название: СТЕПЕНЬ;
• Предназначение: возводит указанное число в указанную степень;
• Количество аргументов: РОВНО два (ни меньше, ни больше, иначе Excel выдаст ошибку!);
• Тип аргументов: оба аргумента должны быть числами, или тем, что в итоге преобразуется в число. Если вместо одного из них вписать текст, Excel выдаст ошибку. А если вместо одно из них написать логические значения «ЛОЖЬ» или «ИСТИНА», ошибки не будет, потому что Excel считает «ЛОЖЬ» равно 0, а истину — любое другое ненулевое значение, даже −1 равно «ИСТИНА». То есть логические значения в итоге преобразуются в числовые;
• Тип возвращаемого значения: число — результат возведения в степень.

Пример использования: «=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячкейке и нажать Enter, в ячейке будет число 1024. Здесь 2 и 10 — аргументы (параметры), а 1024 — возвращаемое функцией значение.

Пример формулы для вычисления длины окружности, содержащую функцию ПИ():

[править] Синтаксис записи функции

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

Общий синтаксис записи любой функции в Excel:

имя_функции([аргумент_1; аргумент_2; … ; аргумент_N])

Список аргументов заключен в квадратные скобки, что говорит о том, что это необязательная часть.

• Некоторые функции вообще не принимают аргументов. Например, функция ПИ() просто возвращает в ячеку значение константы «3,1415…», а функция СЕГОДНЯ() вставляет в ячейку текущую дату. Однако, даже если функция не принимает аргументов, пустые круглые скобки писать обязательно, иначе Excel выдаст ошибку!

• Некоторые функции принимают РОВНО ОДИН аргумент. Например функции sin(число), cos(число) и т. п.

• Некоторые функции принимают больше, чем один аргумент. В таком случае аргументы разделяются между собой точкой с запятой «;».

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

[править] Ввод функций вручную

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

К недостаткам набора формул вручную можно отнести:

• Ручками ставить «=»;
• набирать имя функции;
• открывать/закрывать круглые скобки;
• расставлять точки с запятой;
• следить за порядком вложенности
• заключать текст в двойные кавычки;
• не иметь возможность посмотреть промежуточные расчеты;
• и т. п.

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

[править] Ввод функции с помощью кнопки "сигма"

Одно из средств облегчить и ускорить работы с функциями — кнопка на панели инструментов «Стандартная». В ней разработчики Microsoft «спрятали» пять часто используемых функций:

• СУММ(минимум один, максимум 30 аргументов). Суммирует свои аргументы.

Полезный совет: Чтобы быстро узнать сумму значений в диапазоне, выделяем его и смотрим на строку состояния — там должна отображаться сумма;

• СРЗНАЧ(минимум один, максимум 30 аргументов). Находит среднее арифметическое аргументов;
• СЧЁТ(минимум один, максимум 30 аргументов). Подсчитывает количество чисел в списке аргументов (используется для подсчета количества ячеек с числами, пустые ячейки и текст игнорируются);
• МАКС(минимум один, максимум 30 аргументов). Возвращает максимальный аргумент;
• МИН(минимум один, максимум 30 аргументов). Возвращает минимальный аргумент.

Принцип работы:

1. активизируем ячейку, где должен быть результат (просто щелкаем);
2. Нажимаем на стрелочку справа от кнопки "сигма";
3. Выбираем нужную функцию. После выбора Excel сам вставит знак «=», имя функции, круглые скобки, и даже попытается угадать диапазон, который мы ходим выделить (хотя он редко угадывает);
4. Выделяем связный диапазон ячеек. Excel вствит в круглые скобки адрес выделенного диапазона;
5. Если нужно, например, проссуммировать числа из несвязных диапазонов, зажимаем Ctrl, и выделяем нужное количество диапазонов. Excel сам поставит точку с запятой, и вставит ссылку на другой диапазон;
6. Когда выделили все нужные диапазоны, для завершения нажимаем Enter;

В процессе выделения можно нажимать F4 для изменения типа ссылки и F3 для вставки именованных ссылок.

[править] Работа с мастером функций

[править] Общие сведения. Способы запуска

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

Существует 3 способа запуска мастера функций:

1. С помощью кнопки в строке формул;
2. С помощью команды "Другие функции..." кнопки ;
3. С помощью пункта меню "Вставка" --> "Функция";

[править] Первый шаг

После выполнения одного из этих действий откроется окно мастера функций:

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

[править] Второй шаг

Открывается следующее окно:

[править] Вставка вложенной функции

В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 -- до 64). Конечно, функцию можно записать вручную (писать название вложенной функции, открывать скобки, ставить точки с запятой). Однако это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать пользователя от ошибок и свести к минимуму ручную работу. Существует более удобный способ вложить функцию -- специальная кнопка на панели "Строка формул":

После выбора нужной функции из выпадающего списка Excel вставит называние фукнции и круглые скобки в указанное место в формуле (в активное текстовое поле аргумента). После этого окно мастера функций для предыдущей функции (в этом примере "СУММ") сменится на окно для вставляемой функции ("СТЕПЕНЬ"), и ее название в формуле сделается жирным:

[править] Переключение на другую функцию в формуле

Чтобы опять вернуться к окну для функции "СУММ", достаточно просто щелкнуть в строке формул на ее названии, и окно для степени сменится на окно для "СУММ". После этого функция "СУММ" в названии станет жирной, показывая, что в данный момент окно открыто именно для нее.

[править] Типичные ошибки при работе с мастером функций

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

[править] Полезные советы по работе с мастером функций

• Окно мастера функций можно перетаскивать за любую точку;
• В окне мастера функций, выделяя ссылку и нажимая "F4" один, или несколько раз, можно поменять тип ссылки (сделать абсолютной или смешанной);
• Если в окне мастера функций нажать "F3", откроется окно для вставки именованных ссылок (если они существуют);
• Если какую-то функцию в формуле нужно заменить на другую, выделяем эту функцию в строке формул и вставляем нужную функцию. Выделенная функция заменится на вставляемую;
• Клавиша "Tab" служит для переключения на следующий аргумент (текстовое поле), а сочетание "Shift+Tab" -- на предыдущее;
• Если выделить ячейку, содержащую формулы с функциями и нажать кнопку Fx, то откроется окно мастера функций для одной из функций в формуле;
• Для того, чтобы написать формулу типа "=СУММ(J1:J3)/СУММ(K1:K3)", откройте мастер функций, выберете функцию "СУММ", выделите нужный диапазон, затем щелкнете мышкой в строку формул и в ней вручную наберите "/", затем нажмите на кнопку для вставки функции, и вставьте вторую функцию "СУММ". Все будет работать;
• Если в текстовом поле нужно ввести ТОЛЬКО текст, не обязательно вручную ставить двойные кавычки. Можно написать текст без кавычек и нажать Tab, или просто щелкнуть в другое поле. Excel проставит кавычки автоматом (работает не во всех текстовых полях);
• В тех полях, где требуется ввести логическое значение "ЛОЖЬ" или "ИСТИНА", достаточно ввести "0" для "ЛОЖЬ", и любое ненулевое значение для "ИСТИНА" (принято использовать 1).

[править] Основные функции

[править] Простейшие математические и статистические функции

[править] МИН, МАКС

Синтаксис: