Как написать формулу в Excel? Обучение. Самые нужные формулы

Формулы в Excel для начинающих. Курсы Эксель

Как написать формулу в Excel? Обучение. Самые нужные формулы

Индивидуальные курсы:
По вопросам обучения обращайтесь по телефону 8 (495) 281-54-75 (для жителей Москвы и МО) или 8 (962) 211-56-92 (для других регионов России).

25 декабря 2016 года

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

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

На самом же деле Excel — сложная программа с множеством функций. Всё это подробно я разбираю на соответствующем курсе обучения Excel по Skype.

Сейчас вкратце остановлюсь на одной из важнейших и, к сожалению, не сильно используемой возможности Эксель, а именно — расчёты по формулам.

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

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

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

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

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

Для расстановки приоритетов вычислений используются стандартные круглые скобки, как все привыкли ещё со школы. Формула считается законченной, если вы нажали Enter.

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

artemvm.info_a55f44bf68ab4a2161d082f87c42e550

Если в написанной формуле будет ошибка, то вместо результата появится вот такое.

artemvm.info_a55f44bf68ab4a2161d082f87c42e550

От формулы, содержащей только числа, написанные вручную, мало пользы. Гораздо более полезно применять в формулах Excel ссылки на ячейки. В формулу можно подставить адрес любой ячейки, кроме ячейки самой формулы (что привело бы к ошибке). Адреса ячеек лучше не писать вручную, а просто щёлкать мышкой на тех ячейках листа, адреса которых требуется подставить в формулу Excel.

artemvm.info_a55f44bf68ab4a2161d082f87c42e550

Запомните: при вводе формулы не щёлкайте просто так по листу мышкой — это приведёт к добавлению в формулу Excel адресов ячеек, по которым вы кликнули!

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

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

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

Функций в Excel очень много и их использование выходит за рамки данной статьи.

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

Формулы — это значительная часть функционала Excel! Конечно, все функции вам никогда не пригодятся, но знать как пользоваться формулами и применять их для обработки данных нужно обязательно, если вы часто пользуетесь таблицами.

Подведём итоги

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

При вводе формул будьте внимательны — даже несущественные на вид ошибки могут привести к неработоспособности написанного в ячейке выражения!

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

Как написать формулу в экселе

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

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

Для удобства восприятия я разделил формулы на несколько групп и буду показывать работу с ними от простого к сложному.

Нужно запомнить: вне зависимости от задачи все формулы начинаются со знака «=». 

Сумма ячеек в экселе

Пожалуй самая востребованная формула. Пример простого написания формулы: =A1+B1

Давайте разберем ее. Вы уже наверное знаете, что таблица делится на столбцы, строки и ячейки. Столбцы маркируются с помощью букв «A, B, C» и т.д. Для строк используются цифры «1, 2, 3…».

Для определения ячейки используется  комбинация из буквы и цифры, что соответствует столбцу и номеру строки в нем. Таким образом первая ячейка будет иметь индекс «А1». Ячейка ниже первой будет иметь обозначение «А2», так как находится в столбце «А» и во второй строке.

Давайте проверим как Вы восприняли данную информацию. Какой индекс будет иметь ячейка, которая находится справа от ячейки «А1» и расположена с ней в одной строке? Правильно! Эта ячейка будет иметь обозначение «B1», так как находится в столбце «В» и в первой строке.

Давайте вернемся к нашей формуле

Сумма двух ячеек в Exel

Данная формура суммирует значение двух ячеек. В нашем случае это ячейки «А1» и «В1». Результат выводится в ту ячейку где Вы прописали формулу. Принцип я думаю понятен. Теперь покажу как это сделать на практике.

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

С суммой ячеек разобрались. Двигаемся далее.

Деление и умножение ячеек в экселе

Так же довольно проста в написании. Общую теорию мы уже разобрали, поэтому сразу переходим к практике.

  1. Дважды кликаем по ячейке, в которой будем прописывать формулу. 
  2. Пишем знак «=» и кликаем по первой ячейке. После указываем какое действие мы хотим совершить («*» или «/»). 
  3. Нажимаем «Enter» и получаем результат. Различные действия можно объединять в одной формуле. Давайте сумму ячеек «А1 В1» разделим на сумму «С1 D1». Для этого пишем знак «=» и открываем скобку.
  4. Далее суммируем ячейки  «А1 В1». Как это делать Вы уже знаете. После закрываем скобу и пишем знак умножения.
  5. После открываем скобку и задаем сумму для ячеек «С1 D1» и жмем «Enter».
  6. Таким образом мы получили нужный результат. Вот по такому простому принципу Вы можете комбинировать различные действия для получения необходимого результата.

Среднее значение в экселе

Еще одна полезная функция.

  1. Дважды кликаем по нужной ячейке и пишем знак «=».
  2. В экселе есть отличный конструктор формул, который находится вверху слева. Выберите из выпадающего списка «СРЗНАЧ».
  3. Теперь просто выделите те ячейки, среднее значение которых нужно получить. Подсказка: для выделения можно использовать «Ctrl». Зажмите клавишу и кликайте по нужным ячейкам. Таким образом можно выбирать ячейки, между которыми есть какой либо контент. После нажмите «Ок».
  4. В итоге мы получаем среднее значение выбранных ячеек.

Логические функции в экселе

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

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

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

  1. Пишем формулу для получения среднего значения. Как это делать мы разобрали выше.
  2. Пишем знак «=» и в конструкторе формул выбираем «ЕСЛИ».
  3. В поле «Выражение» задаем наше условие (если меньше 50). Кликаем на ячейку, где у нас средне значение и пишем «

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

Здравствуйте!

Многие кто не пользуются Excel — даже не представляют, какие возможности дает эта программа! Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, складывать по условию и т.д. — в общем-то, по сути мини-язык программирования для решения «узких» задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал)…

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

Возможно, что прочти подобную статью лет 15-17 назад, я бы сам намного быстрее начал пользоваться 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 — задается условие (обратите внимание, что можно было написать условие вида «Саша», а можно просто указать ячейку).

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

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

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

Формула будет иметь вид:

=СЧЁТЕСЛИМН(A2:A7;A2;B2:B7;»6″)  (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня), где:

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

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

Счет строк с двумя и более условиями

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

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

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

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

Пример решения задач с процентами

Собственно, на этом я завершаю данную статью.

 Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать.

Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel), и не знаешь как быстрее это сделать…

Удачи!

RSS (как читать Rss)

Полезный софт:

  • МОНТАЖ
  • Отличное ПО для начала создания своих собственных видеороликов (все действия идут по шагам!).

    сделает даже новичок!

  • Ускоритель компьютера
  • Программа для очистки Windows от мусора (ускоряет систему, удаляет мусор, оптимизирует реестр).

Другие записи:

  • Почему Bluetooth не находит устройства: ноутбук не видит телефон…
  • Какие плюсы у лицензионной Windows, и как узнать лицензионный ключ
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: