Копирование формулы из ячейки в буфер

Затем выделяем диапазон ячеек excel, в который мы хотим вставить формулу, жмем на нем правой клавишей мышки и выбираем пункт меню «Вставить» см. рисунок.

 

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

Если посмотреть на вставленные Excel формулы, то мы увидим, что в ячейке C2 формула ‘=A1+B1’ изменилась на ‘=A2+B2’, а в ячейке C3 на ‘=A3+B3’.

 

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

Насколько мы сместились относительно ячейки, из которой мы копировали формулу (C1), настолько изменились ссылки в формуле, во второй строке A2 и B2, в третьей строке A3 и B3. Это произошло потому, что формулы Excel содержали относительные ссылки (A1,B1, A2,B2, A3,B3).

Теперь изменим формулу в ячейке С1 с ‘=A1+B1’ на ‘=A1+$B$1’ и снова скопируем ее в ячейки C2 и C3. Смотрим на вставленные формулы и видим в ячейке C2 формула ‘=A1+$B$1’ изменилась на =A2+$B$1’, а в ячейке C3 на ‘=A3+$B$1’ см. рисунок.

 

Формула с относительной и абсолютной ссылкой

Мы видим, что ссылка на ячейку A1 менялась, как и раньше, а ссылка на ячейку $B$1 осталась без изменений и использовала в формуле значение из ячейки B1, это произошло потому, что в этот раз вместо относительной ссылки B1 мы использовали абсолютную ссылку $B$1.

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

Ну вот, мы и добрались до смешанных ссылок. Как видно из названия смешанная ссылка представляет собой смесь относительной и абсолютной. $A1 и A$1 – типичные смешанные ссылки. Разница в том, что при «движении» формулы по ячейкам у ссылки $A1 будет оставаться неизменным индекс столбца «A» ($A2, $A3, $A4), а для ссылки A$1 номер строки (B$1, C$1, D$1).

Вывод: если вы хотите, чтобы при копировании формулы в смешанной ссылке не менялся столбец, ставьте ‘$’ перед индексом столбца, пример — $B5, если вы хотите, чтобы при копировании формулы в смешанной ссылке не менялся номер строки, ставьте ‘$’ перед номером строки, пример — B$5.

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

Допустим в ячейке A1 текущего листа, необходимо получить значение из ячейки B2 листа 2, для этого:

1. Ставим знак “=” в ячейке A1.
2. Кликаем мышкой на Лист2 в левой нижней части Excel документа.
3. Выделяем ячейку B2, жмем клавишу “Enter”.
4. В ячейке A1 появляется формула ‘=Лист2!B2’ и значение из данной ячейки.

Внешние ссылки

Ссылки на другие книги (файлы Excel) называются внешними ссылками. Допустим в ячейке A1 нам нужно значение из той же самой ячейки B2 листа 2, но другой книги (файла Excel), тогда формула в ячейке A1 будет иметь вид:

=[Книга2]Лист2!B2

Понятно, что вместо ‘Книга2’ может использоваться полный путь к нужному файлу Excel, тогда формула может иметь например такой вид:

=’C:\temp\[формулы в excel.xls]Лист1′!$B$2

Операторы

Второй незаменимый компонент в любой Excel формуле – операторы, они делятся на арифметические, логические, операторы ссылок и операторы объединения текста.

Арифметические операторы

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

Сложение: «+» (пример формулы: «=2+2»);

вычитание: «-» (пример формулы: «=2-2»);

умножение: «*» (пример формулы: «=2*2»);

деление: «/»(пример формулы: «=2/2»);

возведение в степень: «^»(пример формулы: «=3^4»);

процент: «%» (пример формулы: «=5%»), проще говоря, добавление % после числа, делит это число на 100, «=5%» вернет результат 0,05. Если нам нужно найти 5% от 20, вводим в ячейку формулу «=20*5%».

Логические операторы

Тут тоже просто, при использовании логического оператора мы можем получить всего два значения «ИСТИНА» или «ЛОЖЬ». Например, формула в ячейке C1 “=A1>B1” возвращает значение «ИСТИНА» см. рисунок,

 

Применение логического оператора в формуле

т.к. значение в ячейке A1 больше значения в ячейке B1, иначе вернула бы «ЛОЖЬ». Ниже перечислю логические операторы, вдруг кто-то забыл школьный курс.:-)

Больше: “>”;

меньше: “<”;

больше, либо равно: “>=”;

меньше, либо равно: “<=”;

равно (проверяется равенство): “=”;

неравно (проверяется неравенство): “<>”.

Операторы объединения строк

Объединение строк в Excel осуществляется с помощью двух операторов “СЦЕПИТЬ” и “&” (амперсанд). Например, в ячейке D1 необходимо объединить фамилию, имя, отчество из ячеек A1, B1, C1. Введя в ячейку D1 формулу “=A1&B1&C1”получим такой результат ИвановСергейПетрович см. рисунок.

Объединение строк в Excel с помощью оператора "&"

Чтобы вставить пробелы между объединяемыми строками необходимо изменить формулу так =A1&“ ”&B1&“ ”&C1.

Аналогичного результата можно добиться, используя формулу =СЦЕПИТЬ(A1;“ ”;B1;“ ”;C1), см. пример:

 

Объединение строк с помощью оператора "СЦЕПИТЬ"

Операторы ссылок

Двоеточие “:”. Ставится между ссылками на первую и последнюю ячейку диапазона. Является ссылкой на диапазон ячеек. Пример формулы Excel с использованием оператора “:” =СУММ(A1:A5). Формула выдаст сумму значений содержащихся в ячейках A1, A2, A3, A4, A5.

Точка с запятой “;”. Объединяет несколько ссылок в одну ссылку. Пример формулы с использованием оператора “;” =СУММ(A5:A10;B5:B10).

Пробел “ ”. Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов. Пример формулы с использованием оператора “ ”. В ячейку A1 вводим формулу =СУММ(A2:D2 C1:C4) см. рисунок.

Пример использования оператора пересечения множеств в Excel

Формула выдаст сумму значений содержащихся в общих ячейках двух диапазонов A2:D2 и C1:C4. Общая ячейка у этих двух диапазонов одна C2, поэтому в ячейке A1 мы видим тоже значение.