Основные формулы для работы в excel: Excel: 10 формул для работы в офисе

Содержание

Excel: 10 формул для работы в офисе

1 СУММ: Сложение

Сумма — самая популярная функция Excel, которой пользуются абсолютно все – и школьники, и серьезные бухгалтеры, и аналитики. Как следует из названия, функция складывает значения в указанном диапазоне. Все просто – поставьте в ячейку знак равно, выберете функцию СУММ, выделите область, в которой находятся числа для сложения, и нажмите «ОК». В ячейке появится сумма выбранных значений.

Функция может сложить как два, так и миллион чисел — настоящий супер-калькулятор.

2 ЕСЛИ: Условие

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

Аргументами функции являются:

  • Лог_выражение — здесь надо задать параметр, по которому будут отбираться значения.
  • Значение_если_истина — то, что будет на экране, если логическое условие верно.
  • Значение_если_ложь — имя значения, которое не соответствует критерию, указанному в логическом выражении.

Вот небольшой пример: необходимо рассортировать данные таблицы с товаром. Зададим логическое выражение: «если стоимость больше 50». Если выражение верно, будем считать это значение как «Много», если нет — как «Мало». Нажмем «ОК».

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

3 МАКС: Максимум

Как найти максимум в Excel? Используйте функцию МАКС. Это одна из самых популярных операций в офисной программе. Для использования данной функции выберите ячейку, в которой хотите увидеть результат, поставьте в нее равно и выберете МАКС. Выделите ячейки, среди которых необходимо найти максимальное значение: помните, диапазон должен содержать не больше, чем 255 чисел.

По тому же принципу работает функция для поиска минимума – МИН.

4 СРЗНАЧ: Среднее значение

Данная формула служит для поиска среднего значения среди множества ячеек таблицы. Называется она довольно очевидно – СРЗНАЧ. Аргументом данной функции является диапазон, в котором вы хотите найти среднее арифметическое.

5. СЧЕТ: Подсчет количества значений

Функция СЧЕТ подсчитывает количество ячеек в указанном диапазоне, которые содержат числа. Например, у вас есть 9 позиций товаров, а надо узнать, сколько из них было куплено. Выделяем необходимую область со значениями и нажимаем «ОК» — функция выдаст результат. В нашем случае все можно было посчитать и вручную, а что делать, если таких значений сотни? Верно — использовать операцию СЧЕТ.

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

6 СЧЕТЕСЛИ: Подсчет определенных значений

Также в Excel есть функция СЧЕТЕСЛИ — она позволяет подсчитывать ячейки по заданному критерию. Аргументами в таком случае является не только диапазон, но и условие подсчета.

Для более сложных операций существует функция СЧЕТЕСЛИМН — в ее аргументах можно задать несколько критериев счета.

7 СУММЕСЛИ: Сложение при условии

Функция СУММЕСЛИ служит для того, чтобы складывать избранные числа из выделенного диапазона. По сути, это сочетание СУММ и ЕСЛИ. Аргументами операции являются диапазон условия, критерий и область суммирования.

Например, в таблице с товарами есть фрукты по разной цене. Нужно посчитать, сколько было куплено товаров стоимостью меньше 2. Для этого выделим диапазон цен, укажем условие «<2» и область сложения — столбец с количеством. Функция поддерживает и более сложные условия, например, вложенные. Полностью раскрыть ее возможности можно при работе с большими таблицами в бухгалтерии или экономике.

8. СУММЕСЛИМН: Сложение по многим критериям

Если вам необходимо сложить значения, которые нельзя описать одним условиям, используйте функцию СУММЕСЛИМН. Она позволяет задать до 127 разных критериев. Данная операция — одна из самых востребованных в экономике, так как открывает действительно широкие возможности для работы с таблицами в Excel.

8 СЦЕПИТЬ: Объединение текстовых значений

Очень часто в текстовых таблицах хранится много отдельных значений, которые удобно читать вместе. Например, в таблице с ФИО. Для объединения текста служит операция СЦЕПИТЬ. Все просто: выделите необходимые строки, которые хотите соединить, и нажмите «ОК». Но помните, что пробелы придется добавить самому — сразу в функции или в строках с текстом.

10. ВПР: Поиск значений

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

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

Аргументами ВПР являются искомое значение, таблица, номер столбца, из которого надо вытащить информацию, и интервальный просмотр. В последнем аргументе необходимо задать ЛОЖЬ, если вы ищете точное значение, и ИСТИНА для поиска приблизительного.

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

Читайте также:
Как закрепить строку в Excel при прокрутке
Как выполнять расчеты времени в Excel

как пользоваться, создавать и изменять

Формулы Excel используют, когда данных очень много. Например, чтобы посчитать сумму нескольких чисел быстрее, чем на калькуляторе. Преимуществ много, поэтому работодатели часто указывают эту программу в требованиях. В конце марта 2022 года 64 225 вакансий на хедхантере содержали формулировки вроде «уверенный пользователь Excel», «работа с формулами в Excel».

Кому важно знать Excel и где выучить основы

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

Программу можно освоить самостоятельно, например по статьям в интернете. Но это поможет понять только основные формулы. Если нужны глубокие знания — как строить сложные прогнозы, собирать калькулятор юнит-экономики, — пройдите курсы.

На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой. Кроме Excel вы изучите Metabase, SQL, Power BI, язык программирования Python.

Еще используют символы сравнения:

равенство =

меньше <

больше >

меньше либо равно <=

больше либо равно >=

не равно <>

Основные виды

Все формулы в Excel делятся на простые, сложные и комбинированные. Их можно написать самостоятельно или воспользоваться встроенными.

Простые

Применяют, когда нужно совершить одно простое действие, например сложить или умножить.

✅ СУММ. Складывает несколько чисел. Сумму можно посчитать для нескольких ячеек или целого диапазона.

</p>
<p>=СУММ(А1;В1) — для соседних ячеек;</p>
<p>=СУММ(А1;С1;h2) — для определенных ячеек;</p>
<p>=СУММ(А1:Е1) — для диапазона.</p>
<p>

Сумма всех чисел в ячейках от А1 до Е1

✅ ПРОИЗВЕД. Умножает числа в соседних, выбранных вручную ячейках или диапазоне.

</p>
<p>=ПРОИЗВЕД(А1;В1)</p>
<p>=ПРОИЗВЕД(А1;С1;h2)</p>
<p>=ПРОИЗВЕД(А1:Е1)</p>
<p>

Произведение всех чисел в ячейках от А1 до Е1

✅ ОКРУГЛ. Округляет дробное число до целого в большую или меньшую сторону. Укажите ячейку с нужным числом, в качестве второго значения — 0.

</p>
<p>=ОКРУГЛВВЕРХ(А1;0) — к большему целому числу;</p>
<p>=ОКРУГЛВНИЗ(А1;0) — к меньшему.</p>
<p>

Округление в меньшую сторону

✅ ВПР. Находит данные в таблице или определенном диапазоне.

</p>
<p>=ВПР(С1;А1:В6;2)</p>
<p>
  • С1 — ячейка, в которую выписывают известные данные. В примере это код цвета.
  • А1 по В6 — диапазон ячеек. Ищем название цвета по коду.
  • 2 — порядковый номер столбца для поиска. В нём указаны названия цвета.

Формула вычислила, какой цвет соответствует коду

✅ СЦЕПИТЬ. Объединяет данные диапазона ячеек, например текст или цифры. Между содержимым ячеек можно добавить пробел, если объединяете слова в предложения.

</p>
<p>=СЦЕПИТЬ(А1;В1;С1) — текст без пробелов;</p>
<p>=СЦЕПИТЬ(А1;" ";В1;" "С1) — с пробелами. </p>
<p>

Формула объединила три слова в одно предложение

✅ КОРЕНЬ. Вычисляет квадратный корень числа в ячейке.

</p>
<p>=КОРЕНЬ(А1)</p>
<p>

Квадратный корень числа в ячейке А1

✅ ПРОПИСН. Преобразует текст в верхний регистр, то есть делает буквы заглавными.

</p>
<p>=ПРОПИСН(А1:С1)</p>
<p>

Формула преобразовала строчные буквы в прописные

✅ СТРОЧН. Переводит текст в нижний регистр, то есть делает из больших букв маленькие.

</p>
<p>=СТРОЧН(А2)</p>
<p>

✅ СЧЕТ. Считает количество ячеек с числами.

</p>
<p>=СЧЕТ(А1:В5)</p>
<p>

Формула вычислила, что в диапазоне А1:В5 четыре ячейки с числами

✅ СЖПРОБЕЛЫ. Убирает лишние пробелы. Например, когда переносите текст из другого документа и сомневаетесь, правильно ли там стоят пробелы.

</p>
<p>=СЖПРОБЕЛЫ(А1)</p>
<p>

Формула удалила двойные и тройные пробелы

Сложные

✅ ПСТР. Выделяет определенное количество знаков в тексте, например одно слово.

</p>
<p>=ПСТР(А1;9;5)</p>
<p>
  1. Введите =ПСТР.
  2. Кликните на ячейку, где нужно выделить знаки.
  3. Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
  4. Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 5.

В ячейке А1 формула выделила 5 символов, начиная с 9-го

✅ ЕСЛИ. Анализирует данные по условию. Например, когда нужно сравнить одно с другим.

</p>
<p>=ЕСЛИ(A1&gt;25;"больше 25";"меньше или равно 25")</p>
<p>

В формуле указали:

  • А1 — ячейку с данными;
  • >25 — логическое выражение;
  • больше 25, меньше или равно 25 — истинное и ложное значения.

Первый результат возвращается, если сравнение истинно. Второй — если ложно.

Число в А1 больше 25. Поэтому формула показывает первый результат — больше 25.

✅ СУММЕСЛИ. Складывает числа, которые соответствуют критерию. Обычно критерий — числовой промежуток или предел.

</p>
<p>=СУММЕСЛИ(В2:В5;"&gt;10")</p>
<p>

В формуле указали:

  • В2:В5 — диапазон ячеек;
  • >10 — критерий, то есть числа меньше 10 не будут суммироваться.

Число 8 меньше указанного в условии, то есть 10. Поэтому оно не вошло в сумму.

✅ СУММЕСЛИМН. Складывает числа, когда условий несколько. В формуле указывают диапазоны — ячейки, которые нужно учитывать. И условия — содержание подходящих ячеек. Например:

</p>
<p>=СУММЕСЛИМН(D2:D6;C2:C6;"сувениры";B2:B6;"ООО ХY")</p>
<p>
  • D2:D6 — диапазон, из которого суммируем числа;
  • C2:C6 — диапазон ячеек для категории;
  • сувениры — условие, то есть числа другой категории учитываться не будут;
  • B2:B6 — диапазон ячеек для компании;
  • ООО XY — условие, то есть числа другой компании учитываться не будут.

Под условия подошли только ячейки D3 и D6: их сумму и вывела формула

Комбинированные

В Excel можно комбинировать несколько функций: сложение, умножение, сравнение и другие. Например, вам нужно найти сумму двух чисел. Если значение больше 65, сумму нужно умножить на 1,5. Если меньше — на 2.

</p>
<p>=ЕСЛИ(СУММ(A1;B1)&lt;65;СУММ(A1;B1)*1,5;(СУММ(A1;B1)*2))</p>
<p>

То есть если сумма двух чисел в А1 и В1 окажется меньше 65, программа посчитает первое условие — СУММ(А1;В1)*1,5. Больше 65 — Excel задействует второе условие — СУММ(А1;В1)*2.

Сумма в А1 и В1 больше 65, поэтому формула посчитала по второму условию: умножила на 2

Встроенные

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

  1. Поместите курсор в нужную ячейку.
  2. Откройте диалоговое окно мастера: нажмите клавиши Shift + F3. Откроется список функций.
  3. Выберите нужную формулу. Нажмите на нее, затем на «ОК». Откроется окно «Аргументы функций».
  4. Внесите нужные данные. Например, числа, которые нужно сложить.

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

Как скопировать

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

  1. Впишите функцию в ячейку и кликните на нее.
  2. Наведите курсор на правый нижний угол — курсор примет форму креста.
  3. Нажмите левую кнопку мыши, удерживайте ее и тяните до нужной ячейки.
  4. Отпустите кнопку. Появится итог.

Посчитали сумму ячеек в трех строках

Как обозначить постоянную ячейку

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

  1. Нажмите на ячейку с формулой.
  2. Поместите курсор в нужную ячейку и нажмите F4.
  3. В формуле фрагмент с описанием ячейки приобретет вид $A$1. Если вы протянете формулу, то ссылка на ячейку $A$1 останется на месте.

Как поставить «плюс», «равно» без формулы

Когда нужна не формула, а данные, например +10 °С:

  1. Кликните правой кнопкой по ячейке.
  2. Выберите «Формат ячеек».
  3. Отметьте «Текстовый», нажмите «ОК».
  4. Поставьте = или +, затем нужное число.
  5. Нажмите Enter.

Главное о формулах в Excel

  • Формула состоит из математических знаков. Чтобы ее вписать, используют символы = ( ) ; : .
  • С помощью простых формул числа складывают, умножают, округляют, извлекают из них квадратный корень. Чтобы отредактировать текст, используют формулы поиска, изменения регистра, удаления лишних пробелов.
  • Сложные и комбинированные формулы помогают делать объемные вычисления, когда нужно соблюдать несколько условий.

Список 10 основных формул и функций Excel (с примерами)

Вот список 10 основных формул и функций Excel.

  1. Сумма
  2. СЧЕТ
  3. Counta
  4. Countblank
  5. Среднее
  6. MIN Excel
  7. MAX Excel
  8. LEN Excel
  9. TRIM Excel
  10. , если Excel

Теперь дайте нам.

Содержание
  • Список 10 основных формул и функций Excel
    • #1 SUM in Excel
      • Example
    • #2 COUNT Excel Function
      • Example
    • #3 COUNTA in Excel
      • Example
    • #4 COUNTBLANK in Excel
      • Example
    • #5 СРЕДНЕЕ в Excel
      • Пример
    • #6 МИН. Формула в Excel
      • Пример
    • #7 МАКС. Формула в Excel
      • Пример

      # 9 L в Excel 9EN00060030

    • Пример
  • #9 TRIM в Excel
    • Пример
  • #10 If Excel
    • Пример
  • Вещи, чтобы запомнить
  • Fase Excel Formulas Video
  • . Рекомендуемые арт. Вы можете скачать этот шаблон Excel с основными формулами здесь — Шаблон Excel с основными формулами