Формулы в Excel

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

 

Как ввести формулу в Excel

 

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

Пример, вводим в ячейку Excel A1 формулу ‘=1+3’, а в ячейку B1 формулу ‘1+3’ см. рисунки.

Формула в excel

пример вставки формулы в Excel

 

Ввод формулы в Excel без знака "="

Excel не воспринимает формулу без знака "="

 

В ячейке A1 мы сразу видим результат вычисления, в ячейке B1 просто текст ‘1+3’. Причина в отсутствие знака равенства перед выражением ‘1+3’, поэтому Excel не воспринимает его в качестве формулы.

 

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

 

Обычно в формулах Excel используются не просто числа, а значения из ячеек таблиц, т.е. если мы в ячейке С1 таблицы хотим получить сумму значений из ячеек A1 и B1 в нее необходимо ввести формулу ‘=A1+B1’ см. рисунок.

Excel формула со ссылками

Пример формулы содержащей ссылки на ячейки

Как видите, в формуле используются ссылки на ячейки, в которых эти значения содержатся. Теперь сосчитаем суммы значений во второй и третьей строке, для этого копируем формулу из ячейки C1, жмем правой клавишей мышки на ячейке с формулой и выбираем пункт меню “Копировать”, см. рисунок.

Копирование формулы в Excel

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

 

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

 

Вставка формулы в ячейку Excel

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

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

 

Относительные ссылки в формуле Excel

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

 

Насколько мы сместились относительно ячейки, из которой мы копировали формулу (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’ см. рисунок.

 

Абсолютная ссылка в строке формул в excel

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

Мы видим, что ссылка на ячейку 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” возвращает значение «ИСТИНА» см. рисунок,

 

Логические операторы в формуле excel

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

 

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

 

Больше: “>”;

 

меньше: “<”;

 

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

 

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

 

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

 

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

 

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

 

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

Объединение строк в формуле Excel

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

 

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

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

 

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

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

 

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

 

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

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

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

 

Оператор пересечения множеств в Excel таблицах

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

 

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

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

 

Использование функций в формулах значительно расширяет возможности пользователей Excel. Давайте рассмотрим простой пример использование функции СУММ в формуле. Допустим, нам необходимо в ячейке C3 из суммы значений ячеек A1, A2, A3, вычесть значение ячейки B3, для этого нам понадобиться такая формула “ =СУММ(A1:A3)-B3”, см. рисунок.

функции в Excel таблицах

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

 

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





Теги: [ ]
Вы можете прочитать комментарии к этой записи в формате RSS 2.0. Вы можете оставить комментарий или обратную ссылку с вашего сайта.

4 Comments к записи “Формулы в Excel”

  • Владимир
    18 Окт 2013, 23:08 г.

    Отличная статья. Все очень понятно рассказано в отличие от официального сайта Microsoft. Только здесь нашел наглядное описание про объединение строк. Спасибо!

  • Оксана
    5 Мар 2014, 11:42 г.

    Спасибо. Самое главное и в удобной форме.

  • Azazello
    15 Апр 2014, 10:57 г.

    Пожалуйста, рад что статья пригодилась.

  • Светлана
    28 Окт 2014, 17:36 г.

    Подскажите, какую функцию использовать в моем случае. Приведу пример для наглядности. 1. Иванов А.А. – 15000
    2. Иванов А.А. -17700
    3. Петров И.П. – 19000
    4. Петров И.П. – 15800
    5. Петров И.П. – 12000.
    Фамилии постоянно меняются, их очень много, встречаются разное количество раз. Какую функцию использовать, чтобы сложить значения пофамильно?

Оставить комментарий

Вы должны войти для комментирования записи.