5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.
Многие кто не пользуются Excel — даже не представляют, какие возможности дает эта программа! ☝
Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, создавать собственные условия и т.д. — в общем-то, по сути мини-язык программирования для решения "узких" задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал) .
В этой статье хочу показать несколько примеров, как можно быстро решать повседневные офисные задачи: что-то сложить, вычесть, посчитать сумму (в том числе и с условием) , подставить значения из одной таблицы в другую и т.д.
То есть эта статья будет что-то мини гайда по обучению самому нужному для работы (точнее, чтобы начать пользоваться Excel и почувствовать всю мощь этого продукта!) .
Возможно, что прочти подобную статью лет 17-20 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения "простых" задач. 👌
Обучение основам Excel: ячейки и числа
Примечание : все скриншоты ниже представлены из программы Excel 2016 (как одной из самой новой на сегодняшний день).
Многие начинающие пользователи, после запуска Excel — задают один странный вопрос: "ну и где тут таблица?". Между тем, все клеточки, что вы видите после запуска программы — это и есть одна большая таблица!
Теперь к главному : в любой клетке может быть текст, какое-нибудь число, или формула. Например, ниже на скриншоте показан один показательный пример:
- слева : в ячейке (A1) написано простое число "6". Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число "6".
- справа : в ячейке (C1) с виду тоже простое число "6", но если выбрать эту ячейку, то вы увидите формулу "=3+3" — это и есть важная фишка в Excel!
Просто число (слева) и посчитанная формула (справа)
👉 Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например "=3+5+8" (без кавычек). Результат вам писать не нужно — Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!
Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму — можно написать формулу двумя способами:
- первый: "=5+6" (не совсем удобно, представьте, что в ячейке A1 — у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
- второй: "=A1+B1" — а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!).
Сложение ячеек, в которых уже есть числа
Распространение формулы на другие ячейки
В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:
- в строке 2 написать формулу "=A2+B2" , в строке 3 — "=A3+B3" и т.д. (это долго и утомительно, этот вариант никогда не используют) ;
- выбрать ячейку D1 (в которой уже есть формула) , затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже) . Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! ( Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)) .
Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 — то увидите формулу "=A2+B2" (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .
Как задать константу (ячейку, которая не будет меняться при копировании формулы)
Довольно часто требуется в формулах (когда вы их копируете), чтобы какой-нибудь значение не менялось. Скажем простая задача: перевести цены в долларах в рубли. Стоимость рубля задается в одной ячейке, в моем примере ниже — это G2.
Далее в ячейке E2 пишется формула "=D2*G2" и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу "D3*G3", в 4-ю строку: "D4*G4" и т.д. Надо же, чтобы G2 везде оставалась G2.
Чтобы это сделать — просто измените ячейку E2 — формула будет иметь вид "=D2*$G$2". Т.е. значок доллара $ — позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже) .
Константа / в формуле ячейка не изменяется
Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)
Можно, конечно, составлять формулы в ручном режиме, печатая "=A1+B1+C1" и т.п. Но в Excel есть более быстрые и удобные инструменты.
Один из самых простых способов сложить все выделенные ячейки — это использовать опцию автосуммы (Excel сам напишет формулу и вставить ее в ячейку) .
Что нужно сделать, чтобы посчитать сумму определенных ячеек:
- сначала выделяем ячейки (см. скрин ниже 👇) ;
- далее открываем раздел "Формулы" ;
- следующий шаг жмем кнопку "Автосумма" . Под выделенными вами ячейками появиться результат из сложения;
- если выделить ячейку с результатом (в моем случае — это ячейка E8) — то вы увидите формулу "=СУММ(E2:E7)" .
- таким образом, написав формулу "=СУММ(xx)" , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк.
Автосумма выделенных ячеек
Как посчитать сумму с каким-нибудь условием
Довольно часто при работе требуется не просто сумма всего столбца, а сумма определенных строк (т.е. выборочно). Предположим простую задачу: нужно получить сумму прибыли от какого-нибудь рабочего (утрировано, конечно, но пример более чем реальный) .
Я в своей таблицы буду использовать всего 7 строк (для наглядности) , реальная же таблица может быть намного больше. Предположим, нам нужно посчитать всю прибыль, которую сделал "Саша". Как будет выглядеть формула:
- " =СУММЕСЛИМН( F2:F7 ; A2:A7 ;"Саша") " — ( прим .: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру "СУММ. "), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
- F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
- A2:A7 — это столбик, по которому будет проверяться наше условие;
- "Саша" — это условие, те строки, в которых в столбце A будет "Саша" будут сложены (обратите внимание на показательный скриншот ниже) .
Сумма с условием
Примечание : условий может быть несколько и проверять их можно по разным столбцам.
Как посчитать количество строк (с одним, двумя и более условием)
Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию.
Ну, например, сколько раз имя "Саша" встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой?
"=СЧЁТЕСЛИ( A2:A7 ; A2 )" — где:
- A2:A7 — диапазон, в котором будут проверяться и считаться строки;
- A2 — задается условие (обратите внимание, что можно было написать условие вида "Саша", а можно просто указать ячейку).
Результат показан в правой части на скрине ниже.
Количество строк с одним условием
Теперь представьте более расширенную задачу: нужно посчитать строки, где встречается имя "Саша", и где в столбце "B" будет стоять цифра "6". Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже) .
Формула будет иметь вид:
=СЧЁТЕСЛИМН( A2:A7 ; A2 ; B2:B7 ;"6") — (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня) , где:
A2:A7 ; A2 — первый диапазон и условие для поиска (аналогично примеру выше);
B2:B7 ;"6" — второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).
Счет строк с двумя и более условиями
Как посчитать процент от суммы
Тоже довольно распространенный вопрос, с которым часто сталкиваюсь. Вообще, насколько я себе представляю, возникает он чаще всего — из-за того, что люди путаются и не знают, что от чего ищут процент (да и вообще, плохо понимают тему процентов (хотя я и сам не большой математик, и все таки. ☝) ).
👉 В помощь!
Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] — заметка для начинающих
Самый простой способ, в котором просто невозможно запутаться — это использовать правило "квадрата", или пропорции.
Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал "Саша", нужно найти какую. ).
По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).
В принципе, используя это правило, запутаться в процентах практически невозможно 👌.
Пример решения задач с процентами
PS
Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь "пяток" формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. 👌
Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать всё самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel) , и даже не догадывается как быстро это можно сделать. ✔
Как в Эксель посчитать сумму столбца
Эксель предназначен для работы с табличными данными и ведения расчетов с использованием разного уровня сложности формул. Посчитать сумму по столбцу или строке, сразу по нескольким областям значений – легкое задание, если знать, как правильно это сделать.
Способ 1 – Автосуммирование
- Курсор установить в месте, в котором надо вывести значение подсчетов.
- На вкладке «Главная» нажать значок Автосуммы или воспользоваться сочетанием клавиш Alt и =.
Если требуется просуммировать сразу несколько столбцов или строк, то:
- Выделить ячейки, в которых будет отражена сумма по каждому из столбцов.
- Нажать символ Автосуммы.
- Enter. Итоги будут посчитаны сразу по двум столбцам.
Если требуется найти сумму вместе с несколькими дополнительными ячейками:
- Выделить область для вывода итогов.
- Нажать на иконку Автосуммы.
- Диапазон автоматически определится в составе всех вышерасположенных ячеек.
- Зажать Ctrl и выделить дополнительные области и нажать Enter.
Способ 2. Ручной ввод формулы
Удобен при необходимости подсчитать итог сложения значений, хаотично расположенных по отношению друг к другу. Создаем формулу пошагово:
- Курсор поставить на ячейку с будущим итогом.
- Ввести =. С равенства начинается любая формула в Excel.
- Левой кнопкой мыши щелкать по нужным для суммирования ячейкам, ставя между ними + и Enter.
Способ 3. Наглядное суммирование
Особенность этого способа в том, что итог не выводится в отдельную ячейку, нет необходимости использовать формулы. Конечный результат будет виден в нижнем правом углу листа документа при выделенном диапазоне ячеек. Как только выделение снимается, сумма исчезает. Поэтапные действия:
- Мышкой выделить необходимый столбец или строку.
- Посмотреть на результат.