Как составить формулу в excel. ТОП 15 формул в Excel

Как в Excel создаются формулы и таблицы. Пошагово

Работа в Excel c формулами и таблицами для чайников. Как же делать формулы и таблицы?

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

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

Видеообзор на тему: Формулы и таблицы в Excel – это просто 

ФОРМУЛЫ В EXCEL ДЛЯ ЧАЙНИКОВ

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

В Excel используются обычные математические операторы:

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

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

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

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

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

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

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

  1. Поставили курсор в ячейку В3 и ввели =.
  2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки возникло в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
  3. Ввели символ *, значение 0,5 с клавиатуры и нажали ВВОД.

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

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

КАК В ФОРМУЛЕ EXCEL ОБОЗНАЧИТЬ ПОСТОЯННУЮ ЯЧЕЙКУ

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные меняются, абсолютные остаются постоянными.

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

  1. Вручную заполним 1-ые графы учебной таблицы. У нас – таковой вариант:

2. Вспомним из математики: чтоб отыскать стоимость пары единиц продукта, необходимо стоимость за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = стоимость за единицу * количество. Константы формулы – ссылки на ячейки с надлежащими значениями.

3. Жмем ВВОД – программа показывает значение умножения. Те же манипуляции нужно произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в остальные строчки. Относительные ссылки – в помощь.

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

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

Ссылки в ячейке соотнесены со строкой.

Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении либо копировании константа остается постоянной (или постоянной).

Чтобы указать Excel на абсолютную ссылку, юзеру нужно поставить символ бакса ($). Проще всего это сделать с помощью клавиши F4.

  1. Создадим строчку «Итого». Найдем общую стоимость всех продуктов. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это спектр D2:D9

2. Воспользуемся функцией автозаполнения. Клавиша находится на вкладке «Главная» в группе инструментов «Редактирование».

3. Опосля нажатия на значок «Сумма» (или композиции кнопок ALT+«=») слаживаются выделенные числа и отображается итог в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого продукта в общей стоимости. Для этого нужно:

  1. Разделить стоимость 1-го продукта на стоимость всех продуктов и итог умножить на 100. Ссылка на ячейку со значением общей стоимости обязана быть абсолютной, чтоб при копировании она оставалась неизменной.

2. Чтоб получить проценты в Excel, не непременно умножать личное на 100. Выделяем ячейку с результатом и жмем «Процентный формат». Либо жмем комбинацию горячих клавиш: CTRL+SHIFT+5

3. Копируем формулу на весь столбец: изменяется лишь 1-ое значение в формуле (относительная ссылка). 2-ое (абсолютная ссылка) остается прежним. Проверим корректность вычислений – найдем результат. 100%. Все правильно.

Читайте также  Посмотреть историю буфера обмена. История буфера обмена Windows 10

При разработке формул употребляются последующие форматы абсолютных ссылок:

  • $В$2 – при копировании остаются неизменными столбец и строка;
  • B$2 – при копировании неизменна строка;
  • $B2 – столбец не изменяется.

КАК СОСТАВИТЬ ТАБЛИЦУ В EXCEL С ФОРМУЛАМИ

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

Простейшие формулы наполнения таблиц в Excel:

  1. Перед наименованиями продуктов вставим еще один столбец. Выделяем всякую ячейку в первой графе, щелкаем правой клавишей мыши. Жмем «Вставить». Либо нажимаем поначалу комбинацию клавиш: CTRL+ПРОБЕЛ, чтоб выделить весь столбец листа. А позже комбинация: CTRL+SHIFT+”=”, чтоб вставить столбец.
  2. Назовем новейшую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем 1-ые две ячейки – «цепляем» левой клавишей мыши маркер автозаполнения – тянем вниз.

3.По такому же принципу можно заполнить, к примеру, даты. Ежели промежутки меж ними схожие – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим 1-ые две ячейки и «протянем» за маркер вниз.

4. Найдем среднюю стоимость продуктов. Выделяем столбец с ценами + еще одну ячейку. Открываем меню клавиши «Сумма» – избираем формулу для автоматического расчета среднего значения.

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

Ну вот! Сейчас мы умеем создавать формулы и таблицы в Excel.

Это тоже интересно:

Как закрепить строчку и столбец в Excel при прокрутке

Как Работать в Excel с таблицами. Пошаговая аннотация для чайников

Как в Эксель посчитать сумму столбца

Как в 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 я желаю получить их сумму — можно написать формулу 2-мя способами:

  • первый: “=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 будет “Саша” будут сложены (обратите внимание на показательный скриншот ниже).

Сумма с условием

Примечание: критерий может быть несколько и инспектировать их можно по различным столбцам.

 

*

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

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

Ну, к примеру, сколько раз имя “Саша” встречается в таблице ниже (см. скриншот). Разумеется, что 2 раза (но это поэтому, что таблица очень малая и взята в качестве приятного примера). А как это посчитать формулой?

Формула:

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

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

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

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

 

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

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

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

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

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

Счет строк с 2-мя и наиболее условиями

 

*

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

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

👉 В помощь!

Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] —заметка для начинающих

 

Читайте также  Попытка входа в сеть не удалась. Справка по Скайпу

Самый обычной метод, в котором просто нереально запутаться — это употреблять правило “квадрата”, либо пропорции.

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

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

В принципе, используя это правило, запутаться в процентах фактически нереально 👌.

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

 

*

PS 

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

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

 

*

Дополнения по теме приветствуются!

Удачи!

👋

Первая публикация: 21.01.2017

Корректировка: 5.01.2020

ТОП 15 формул в Excel

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

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

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

Стало интересно? Тогда добро пожаловать в обзор самых нужных 15 формул Excel.

Немного терминологии

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

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

Следующая составная часть функции – это имя. Фактически, им и является заглавие формулы, которое поможет Excel осознать, что желает юзер. Вслед за ним в скобках приводятся аргументы. Это характеристики функции, учитываемые для выполнения определенных операций. Бывает несколько типов аргументов: числовые, текстовые, логические. Также заместо их часто употребляются ссылки на ячейки либо определенный спектр. Каждый аргумент отделяется от другого с помощью точки с запятой.

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

А сейчас давайте все это проверим на практике.

Формула 1: ВПР

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

Синтаксис

Это достаточно непростая формула, имеющая 4 аргумента, а ее внедрение имеет множество особенностей.

Синтаксис такой:

=ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Давайте наиболее тщательно разглядим все аргументы:

  1. Значение, которое ищется.
  2. Таблица. Нужно, чтоб было разыскиваемое значение, находящееся в первой колонке, а также значение, которое ворачивается. Крайнее размещается где-угодно. Юзер может без помощи других определиться, куда вставлять итог формулы. 
  3. Номер столбца.
  4. Интервальный просмотр. Ежели в этом нет необходимости, то можно не приводить значение этого аргумента. Оно являет собой логическое выражение, обозначающее степень точности совпадения, которое обязано быть найдено функцией. Ежели указан параметр «Истина», то тогда Excel будет находить самое близкое значение к указанному в качестве искомого. Ежели же указан параметр «Ложь», то функция будет находить лишь те значения, которые находятся в первой колонке.

На этом скриншоте мы пробуем с помощью формулы осознать, сколько просмотров было осуществлено по запросу «купить планшет».

Формула 2: Если

Эта функция нужна, ежели юзер желает задать определенное условие, при котором должен осуществляться расчет либо выводиться конкретное значение. Она может принимать два варианта: истинное и ложное.

Синтаксис

В формулу данной функции заходит три главных аргумента, и она смотрится так:

=ЕСЛИ(логическое_выражение;«значение_если_истина»;«значение_если_ложь»).

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

Есть еще один вариант, как можно использовать функцию ЕСЛИ – вложенные функции. Тут может быть намного больше критерий, вплоть до 64. Пример рассуждения, соответственного формуле, приведенной на скриншоте, таковой. Ежели ячейка А2 равна двум, то необходимо вывести значение «Да». Ежели же она имеет другое значение, то нужно проверить, равна ли ячейка D2 двум. Ежели да, то необходимо.

Оставьте комментарий