Здравствуйте!
Многие кто не пользуются 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), и даже не догадывается как быстро это можно сделать… ✔