Как писать формулы в windows excel
Перейти к содержимому

Как писать формулы в windows excel

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, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

  1. в строке 2 написать формулу "=A2+B2" , в строке 3 — "=A3+B3" и т.д. (это долго и утомительно, этот вариант никогда не используют) ;
  2. выбрать ячейку 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 сам напишет формулу и вставить ее в ячейку) .

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

  1. сначала выделяем ячейки (см. скрин ниже 👇) ;
  2. далее открываем раздел "Формулы" ;
  3. следующий шаг жмем кнопку "Автосумма" . Под выделенными вами ячейками появиться результат из сложения;
  4. если выделить ячейку с результатом (в моем случае — это ячейка E8) — то вы увидите формулу "=СУММ(E2:E7)" .
  5. таким образом, написав формулу "=СУММ(xx)" , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк.

Автосумма выделенных ячеек

Как посчитать сумму с каким-нибудь условием

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

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

  1. " =СУММЕСЛИМН( F2:F7 ; A2:A7 ;"Саша") " — ( прим .: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру "СУММ. "), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
  2. F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
  3. A2:A7 — это столбик, по которому будет проверяться наше условие;
  4. "Саша" — это условие, те строки, в которых в столбце 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) , и даже не догадывается как быстро это можно сделать. ✔

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

В Эксель формулы – это то, что делает программу «живой». Благодаря этой возможности, Майкрософт Эксель получил широкое распространение и почти бесконечное поле применения. Давайте разберемся, как грамотно писать формулы, и наслаждаться работой высокого уровня!

В ячейках с формулами первый символ всегда знак равенства «=». Так Microsoft Excel понимает, что дальше будет записана формула. Если этот знак не поставить, или он будет не в начале строки, программа посчитает, что в ячейку внесен обычный текст. Когда вы ввели формулу – нажимайте Enter , программа рассчитает результат и отобразит в ячейке его значение (хотя фактически в ячейке будет формула). Чтобы увидеть и откорректировать формулу, выделите нужную ячейку и произведите все манипуляции в строке формул.

Частями формул могут быть:

  • Знаки математических и логических операций
  • Ссылки на другие ячейки
  • Функции
  • Числа или текст

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

Какие операторы применяются в формулах Эксель

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

Вставка имён в формулы и использование в расчетах

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

Автозаполнение при указании имени ячейки Excel

  1. Если вы помните имя, которое нужно вставить, просто введите его в нужном месте формулы. При вводе будет работать Автозаполнение, так что можно ввести первые буквы имени и выбрать из появившегося списка имя.

Автозаполнение при указании имени ячейки

  1. Установите курсор в нужное место формулы и нажмите F3 Откроется диалоговое окно Вставка имени , выберите нужное и сделайте на нем двойной клик для вставки. Если в книге нет заданных имён, нажатие F3 ни к чему не приведет

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

Так, можно присвоить имя константе. Для этого, выполните такие действия:

  1. Кликните на ленточной команде Формулы – Определенные имена – Присвоить Имя . Откроется окно Создание имени
  2. В поле Имя запишите имя будущей константы
  3. В поле Область выберите область видимости константы – вся книга или какой-то конкретный лист
  4. В Примечании можете оставить свой комментарий
  5. В поле Диапазон запишите числовое значение, которое будете именовать и нажмите ОК

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

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

Редактирование формул

Если вы хотите внести изменения в формулу (а такое случается часто), это делается очень легко. Как всегда MS Excel предлагает несколько вариантов. Сначала установите курсор в ячейку с формулой, после этого выполните одно из действий:

  • Измените формулу в строке формул. Можете выделять участки формулы, заменять их другими, вставлять и удалять куски или отдельные символы. Когда закончите – жмите Enter
  • Дважды кликните левой кнопкой мыши на активной ячейке. Формула отобразится в ячейке вместо результата вычислений. Теперь внесите изменения на своё усмотрение и нажмите Ввод
  • Выделите ячейку и нажмите клавишу F2 , она так же отобразит формулу в ячейке. Редактируем и нажимаем Enter .

Режимы вычислений

В Microsoft Excel есть 3 режима вычислений, умелое использование которых позволит вам хорошо управляться с формулами и экономить своё рабочее время. Чтобы выбрать режим вычислений, выполните на ленте: Формулы – Вычисления – Параметры вычислений . Откроется список для выбора одного из параметров вычислений:

Параметры вычислений в Excel

Параметры вычислений в Эксель

  1. Автоматически. Этот режим используется в программе по умолчанию. Формулы просчитываются сразу, при изменении влияющей ячейки, зависимые формулы будут моментально пересчитаны. Расчёт выполняется в естественной последовательности: сначала влияющие ячейки, затем зависимые. Этот режим подходит для небольших файлов, когда Эксель не оказывает большой нагрузки на процессор.
  2. Автоматически, кроме таблиц данных. Автоматически вычисляются все ячейки, кроме диапазонов, связанных в таблицу. Табличные формулы просчитываются вручную (после вашей команды на пересчёт). Режим используют, когда в рабочей книге есть большие таблицы с формулами, пересчёт которых длится значительное время. Тогда, вы сначала откорректируете влияющие ячейки с исходными данными, а потом скомандуете пересчитать таблицу (как это сделать – читайте в следующем пункте).
  3. Вручную. Формулы не пересчитываются, пока вы не дадите команду. Чтобы вычислить формулы, можно использовать такие комбинации клавиш:
    • F9 – пересчитать формулы всех открытых документов Excel
    • Shift+F9 – пересчитать формулы активного рабочего листа
    • Ctrl+Shift+F9 – пересчитать все формулы.

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

    Как видите, продуманное переключение режимов позволит вам экономить ресурсы компьютера и своё личное время.

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

    Добавить комментарий

    Ваш адрес email не будет опубликован.