5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр

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

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

1. Сделайте любую ячейку активной. Нажмите на строку ввода формулы. Поставьте знак равенства.
2. Введите любое выражение. Вы можете использовать как числа, так и ссылки на ячейки.

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

Кому важно знать формулы Excel и где изучить основы

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

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

Особенности определения суммы в разных ситуациях

Одним из самых простых вычислений, для которых используется функция СУММ, является сложение чисел в столбце. Для определения суммы достаточно выделить верхнюю ячейку при указании аргумента, нажать правую кнопку мыши и отпустить, выделить нужный диапазон.
Если результат сложения должен быть в первой строке после суммируемых значений, можно вообще не указывать функцию, а просто нажать на символ автосуммы. Значение будет установлено в правильном месте.

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

Может возникнуть необходимость определить результат сложения для одной и той же области на нескольких страницах одновременно. Например, для определения суммы расходов по одним и тем же статьям в каждом месяце. В этом случае формула =СУММ(Январь:Апрель!B4) вернет число, полученное путем сложения ячейки B4 на каждом из 4 указанных листов.

Еще одна интересная дополнительная функция позволяет учитывать только те ячейки таблицы, которые отображаются на экране. Значения из скрытой области игнорируются. Он называется «Промежуточные итоги» и предназначен для более сложных расчетов, чем простое сложение. Но вы также можете использовать его, чтобы получить сумму.

Например, ПРОМЕЖУТОЧНЫЙ ИТОГ(109; G5:G19) отобразит итог в диапазоне от G5 до G19, игнорируя ячейки, которые пользователь скрыл вручную (например, 11 и 12). Если при фильтрации строки скрыты, вместо аргумента 109 необходимо ввести цифру 9.

Формулы эксель: основные виды

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

Простые

Они позволяют выполнять одну простую операцию: складывать, вычитать, делить или умножать. Простейшая формула =СУММ.

Например:

=СУММ(A1,B1) — сумма значений двух соседних ячеек.

=СУММ(С1;М1;Р1) — сумма конкретных ячеек.

=СУММ(B1:B10) — сумма значений в указанном диапазоне.

Сложные

Это составные формулы для более продвинутых пользователей. Эта категория включает ЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН. Мы подробно опишем их ниже.

Комбинированные

Excel позволяет совмещать несколько функций: сложение + умножение, сравнение + умножение. Это удобно, когда, например, нужно вычислить сумму двух чисел, и если результат больше 100, то его надо умножить на 3, а если меньше, то на 6.

Выглядит формула так ↓

=ЕСЛИ(СУММ(A1;B1)<100;СУММ(A1;B1)*3;(СУММ(A1;B1)*6))

Встроенные

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

• нажмите на нужную ячейку таблицы;
• одновременно нажать Shift+F3;
• выбрать нужную формулу из предложенного списка;
• введите свои данные в поле «Аргументы функции.

Примеры работ, которые можно выполнять с формулами

Разберем основные действия, которые можно выполнить с помощью формул в таблицах Excel и рассмотрим полезные «фишки» для упрощения работы.

Способ 1: Кнопка «Вставка функции»

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

1. При вставке формулы первой всегда выделяется ячейка, где в дальнейшем будет располагаться конечное значение. Сделайте это, нажав на соответствующий блок ЛКМ.
2. Затем перейдите к инструменту «Вставить функцию», нажав отведенную для этого кнопку на верхней панели.
3. Следующим шагом является поиск правильной функции. Для этого вы можете ввести его краткое описание или определиться с категорией.
4. Посмотрите на список в блоке ниже, чтобы выбрать там актуальную функцию.
5. При выборе ниже появится краткая информация о действии и принципе записи.
6. Чтобы получить подробную информацию от разработчиков, нужно нажать на выделенную надпись «Помощь по этой функции».
7. Как только функция будет выбрана, появится отдельное окно, где будут заполнены аргументы. В качестве примера мы взяли формулу MAX, которая выводит максимальное значение из всего списка аргументов. Поэтому в качестве числа здесь указывается список ячеек, входящих в диапазон для подсчета.
8. Вместо того, чтобы заполнять вручную, вы можете нажать на таблицу и выбрать все ячейки, которые должны попадать в один и тот же диапазон.
9. MAX, как и другие функции, такие как самая распространенная SUM, может принимать несколько списков аргументов и вычислять значения из всех из них. Для этого заполните следующие блоки «Число2», «Число3» и так далее в том же порядке
10. После нажатия кнопки «ОК» или клавиши «Ввод» формула будет вставлена ​​в ранее выбранную ячейку с уже отображаемым результатом. При нажатии на нее в верхней панели вы увидите синтаксис формулы и при необходимости сможете его отредактировать.

Способ 2: Вкладка «Формулы»

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

1. Перейдите на вкладку «Формулы» через верхнюю панель.
2. Отсюда вы можете открыть ранее упомянутое окно «Вставить функцию», чтобы начать ее создание, выбрать формулу из библиотеки или воспользоваться инструментом «Автосумма», который мы предлагаем вам рассмотреть.
3. Необходимо выделить все ячейки для суммирования и затем нажать на строку «Автосумма».
4. Формула вставляется автоматически со всеми аргументами, а результат появится в конце блока ячеек, попадающих в диапазон.

Способ 3: Ручное создание формулы

Иногда проще использовать ручной способ вставки формул, потому что Мастер создания может не справиться с задачей, например, при работе с большим количеством условий в ЕСЛИ или других распространенных функциях. В таких случаях быстрее и проще заполнить ячейку самостоятельно.

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

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

Способ 4: Вставка математической формулы

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

1. откройте вкладку «Вставка» и разверните раздел «Символы».
2. Начните создание формулы, нажав кнопку «Уравнение».
3. Активируйте место для уравнения, мгновенно измените его размер для удобства, а затем используйте символы или предопределенные структуры, чтобы упростить создание формул.
4. Когда вы закончите, вы можете переместить формулу куда угодно и изменить ее внешние параметры.

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

Использование операторов

Операторы в редакторе Excel указывают, какие именно операции следует произвести над указанными элементами формулы. Расчет всегда выполняется в одном и том же порядке:

• скобка;
• экспоненты;
• умножение и деление (в зависимости от порядка);
• сложение и вычитание (также в зависимости от порядка).

Арифметические

К ним относятся:

• дополнение — «+» (плюс);

2+2

• отрицание или вычитание — «-» (минус);

2-2

-2

Если перед числом поставить минус, оно примет отрицательное значение, но абсолютное значение останется точно таким же.

• умножение – «*»;

2*2

• разделение «/»;

2/2

• процент «%»;

20%

• возведения в степень – «^».

2^2

Операторы сравнения

Эти операторы используются для сравнения значений. Операция возвращает TRUE или FALSE. К ним относятся:

• знак «равенства» – «=»;

C1=D1

• больше символа» – «>»;

C1>D1

• знак «меньше» — «<»;

C1>

• знак больше или равно» — «>=»;

C1>=D1

• знак «меньше или равно» — «<=»;

C1<=D1

• символ «не равно» — «<>».

C1<>D1

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

Для этого используется специальный символ «&» (амперсанд). С его помощью вы можете соединять разные фрагменты в единое целое — по тому же принципу, что и с функцией «СОЕДИНИТЬ». Вот некоторые примеры:

1. Если вы хотите объединить текст в ячейках, вам нужно использовать следующий код.

А1&А2&А3

1. Чтобы вставить символ или букву между ними, используйте следующую конструкцию.

A1&»,»&A2&»,»&A3

1. Вы можете комбинировать не только клетки, но и обычных персонажей.

«Авто»&»Мобильный»

Текст, кроме ссылок, должен быть заключен в кавычки. В противном случае формула вернет ошибку.

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

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

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

• для создания простой ссылки на нужный диапазон ячеек достаточно указать первую и последнюю ячейку в этом диапазоне, а между ними символ «:»;
• для объединения ссылок используйте символ «;»;
• при необходимости определить ячейки, находящиеся на пересечении нескольких областей, между ссылками ставится «пробел». В этом случае будет отображаться значение ячейки «С7».

Так как только это подпадает под определение «перекрестный набор». Это имя этого оператора (пробел).

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

Читайте также: Выравнивание по ширине в «Ворде»

Использование ссылок

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

Формулы в Excel для чайников

Чтобы ввести формулу для ячейки, необходимо ее активировать (поставить курсор) и ввести равно (=). Вы также можете ввести знак равенства в строке формул. После ввода формулы нажмите Enter. Результат расчета отобразится в ячейке.

Excel использует стандартные математические операторы:

Оператор	Операция	Пример
+ (плюс)	Добавление	=В4+7
— (минус)	Вычитание	=А9-100
* (звезда)	Умножение	=А3*2
/ (косая черта)	Разделение	=А7/А8
^ (циркумфлекс)	Степень	=6^2
= (знак равенства)	Равно
<	Меньше
>	Более
<=	Меньше или равно
>=	Больше или равно
<>	Не похоже

При умножении обязательно используется символ «*». Опускать его, как это принято при письменных арифметических расчетах, недопустимо. То есть запись (2+3) 5 Excel не поймет.

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

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

При изменении значений в ячейках формула автоматически пересчитывает результат.

Ссылки можно комбинировать в одной формуле с простыми числами.

Оператор умножил значение ячейки B2 на 0,5. Чтобы вставить ссылку на ячейку в формулу, просто нажмите на эту ячейку.

В нашем примере:

1. Поместите курсор в ячейку B3 и введите =.
2. Щелкнули по ячейке B2 — Excel ее «обозначил» (в формуле появилось название ячейки, вокруг ячейки образовался «мерцающий» прямоугольник).
3. Ввели с клавиатуры символ *, значение 0.5 и нажали ENTER.

Если в одной формуле используется несколько операторов, программа будет обрабатывать их в следующем порядке:

• %, ^;
• *, /;
• +, -.

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

Обучение основам Excel: ячейки и числа

Примечание: все скриншоты ниже из Excel 2016 (один из последних на сегодняшний день. Если у вас версия 2019, все будет так же).

Многие новички после запуска Excel задаются странным вопросом: «ну а где таблица?». При этом все ячейки, которые вы видите после запуска программы, представляют собой одну большую таблицу!

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

• слева: ячейка (A1) содержит простое число «6». Обратите внимание, что при выборе этой ячейки в строке формул (Fx) отображается только число «6″.
• справа: в ячейке (C1) это тоже выглядит как одиночное число «6», но если выделить эту ячейку, то вы увидите формулу «=3+3» — это важная функция в Excel!

Просто число (слева) и расчетная формула (справа)

Дело в том, что Excel может считать как калькулятор, если выделить ячейку, а затем ввести формулу, например «=3+5+8» (без кавычек). Вам не нужно вводить результат — Excel сам его посчитает и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но вы можете писать в формулах и добавлять не только числа, но и числа, которые уже посчитаны в других ячейках. На скриншоте ниже в ячейках А1 и В1 числа соответственно 5 и 6. В ячейке D1 я хочу получить их сумму — формулу можно написать двумя способами:

• сначала: «=5+6» (не очень удобно, представьте, что в ячейке А1 — у нас тоже есть число, вычисляемое по другой формуле, и оно меняется. Вы же не хотите каждый раз заменять число вместо 5?!);
• второй: «=A1+B1» — но это идеальный вариант, просто сложите значения ячеек A1 и B1 (независимо от того, какие числа в них содержатся!).

Добавить ячейки, в которых уже есть числа

Распространить формулу на другие ячейки

В приведенном выше примере мы добавили два числа в столбцы A и B в первой строке. Но тогда у нас 6 строк, и чаще всего в реальных задачах приходится добавлять числа в каждую строку! Для этого вы можете:

1. в строке 2 напишите формулу «=A2+B2», в строке 3 — «=A3+B3» и так далее (это долго и скучно, этот вариант никогда не используется);
2. выделите ячейку D1 (в которой уже есть формула), затем подведите указатель мыши к правому углу ячейки так, чтобы появился черный крестик (см скриншот ниже). Затем зажмите левую кнопку и растяните формулу на весь столбец. Практично и быстро! (Примечание. Вы также можете использовать комбинации Ctrl+C и Ctrl+V для формул (копировать и вставлять соответственно)).

Кстати, обратите внимание, что сам Excel заменил формулы в каждой строке. То есть, если сейчас выделить ячейку, например D2, то вы увидите формулу «=A2+B2» (т.е. Excel автоматически заменит формулы и сразу выдаст результат).

Как установить константу (ячейка, которая не меняется при копировании формулы)

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

Затем в ячейку E2 пишем формулу «=D2*G2» и получаем результат. Только теперь, если мы расширим формулу, как мы это делали раньше, мы не увидим результата в других строках, потому что Excel помещает формулу «D3*G3» в строку 3, «D4*G4» в строку 4 и так далее. G2 везде должен оставаться G2…

Для этого достаточно изменить ячейку E2 — формула будет иметь вид «=D2*$G$2». Знак доллара $ — позволяет указать ячейку, которая не меняется при копировании формулы (т.е мы получаем константу, пример ниже).

Константа / в формуле не меняет ячейку

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Конечно, вы можете вводить формулы вручную, набрав «=A1+B1+C1» и так далее. Но в Excel есть более быстрые и удобные инструменты.

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

Что нужно сделать, чтобы вычислить сумму определенных ячеек:

1. сначала выберите ячейки (см скрин ниже);
2. затем откройте раздел «Формулы»;
3. следующий шаг — нажать кнопку «Автосумма». Под выбранными вами ячейками будет отображаться результат сложения;
4. если выделить ячейку с результатом (в моем случае это ячейка Е8), то вы увидите формулу «=СУММ(Е2:Е7)».
5. таким образом, написав формулу «=СУММ(хх)», где вместо хх вы ставите (или выбираете) любую ячейку, вы можете подсчитать самые разные ячейки, столбцы, строки…

Как рассчитать сумму с некоторым условием

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

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

1. «=СУММЕСЛИМН(F2:F7;A2:A7;»Саша»)» — (примечание: обратите внимание на кавычки для условия — они должны быть как на скриншоте ниже, а не как я сейчас написал в блоге) . Также обратите внимание, что Excel при запуске в начале формулы (например, «СУММ…») сам спрашивает и заменяет возможные варианты — а формул в Excel сотни!;
2. F2:F7 — диапазон, в который будут добавляться (суммироваться) числа из ячеек;
3. A2:A7 — столбец, с которым будет проверяться наше условие;
4. «Саша» — это условие, будут добавлены те строки, где «Саша» будет в столбце А (обратите внимание на иллюстративный скриншот ниже).

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих условию.

Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см скриншот). Очевидно в 2 раза (но это потому что таблица слишком маленькая и взята как наглядный пример). Как это можно рассчитать по формуле?

Формула:

«=СЧЁТЕСЛИ(A2:A7;A2)» — где:

• A2:A7 — область проверки и подсчета строк;
• А2 — задано условие (обратите внимание, можно написать условие типа «Саша», а можно просто указать ячейку).

Результат отображается в правой части скриншота ниже.

Количество строк с одним условием

Теперь представьте себе более сложную задачу: вам нужно посчитать строки, где встречается имя «Саша», и где число «6» будет в столбце «Б». Забегая вперед, скажу, что такая строка всего одна (скрин с примером ниже).

Формула будет выглядеть так:

=COUNT(A2:A7;A2;B2:B7;»6″) — (обратите внимание: обратите внимание на кавычки — они должны быть как на скриншоте ниже, а не как у меня), где:

A2:A7;A2 — первый диапазон и условие поиска (аналогично примеру выше);

B2:B7;»6″ — второй диапазон и условие поиска (обратите внимание, что условие можно указать по-разному: либо указать ячейку, либо просто написать текст/цифры в кавычках).

Как посчитать процент от суммы

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

Самый простой способ, где запутаться просто невозможно, это воспользоваться правилом «квадрата», или пропорции.

Вся суть показана на скрине ниже: если у вас есть общая сумма, допустим, что в моем примере это число 3060 — ячейка F8 (т.е это 100% прибыли, и «Саша» сделал часть, вам нужно найти какой…).

Условно формула будет выглядеть так: = F10 * G8 / F8 (т.е крест за крестом: сначала умножаем два известных числа по диагонали, а затем делим на оставшееся третье число).

В принципе, используя это правило, заблудиться в процентах практически невозможно .