Работа в эксель формулы: Создание простой формулы в Excel

Содержание

Создание простой формулы в Excel

Excel

Формулы и функции

Формулы

Создание простой формулы в Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще…Меньше

Вы можете создать простую формулу для с суммы, вычитания, умножения и деления значений на вашем компьютере. Простые формулы всегда начинаются со знака равной(=),за которым следуют константы, которые являются числами и операторами вычислений, такими как «плюс»(+),«минус» (— ),«звездочка»*или «косая черта»(/)в начале.

В качестве примера рассмотрим простую формулу.

org/ListItem»>
Выделите на листе ячейку, в которую необходимо ввести формулу.
Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении.

В нашем примере введите =1+1.

Примечания:
- Вместо ввода констант в формуле можно выбрать ячейки с нужными значениями и ввести операторы между ними.
- В соответствии со стандартным порядком математических операций, умножение и деление выполняются до сложения и вычитания.
org/ListItem»>
Нажмите клавишу ВВОД (Windows) или Return (Mac).

Рассмотрим другой вариант простой формулы. Введите =5+2*3 в другой ячейке и нажмите клавишу ВВОД или Return. Excel перемножит два последних числа и добавит первое число к результату умножения.

Использование автосуммирования

Для быстрого суммирования чисел в столбце или строке можно использовать кнопку «Автосумма». Выберите ячейку рядом с числами, которые необходимо сложить, нажмите кнопку Автосумма на вкладке Главная, а затем нажмите клавишу ВВОД (Windows) или Return (Mac).

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

Примечание: Также в ячейке можно ввести ALT+= (Windows) или ALT++= (Mac), и Excel автоматически вставит функцию СУММ.

Приведем пример. Чтобы сложить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая непосредственно под столбцом чисел. Затем нажмите кнопку «Автоумма». Формула появится в ячейке B7, а Excel выделит ячейки, которые вы суммируете.

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

Примечания:

Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.
Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.
Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку
Автосумма и суммировать два столбца одновременно.

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

Примечание: Чтобы эти формулы выводили результат, выделите их и нажмите клавишу F2, а затем — ВВОД (Windows) или Return (Mac).

Данные
2
5
Формула	Описание	Результат
=A2+A3	Сумма значений в ячейках A1 и A2	=A2+A3
=A2-A3	Разность значений в ячейках A1 и A2	=A2-A3
=A2/A3	Частное от деления значений в ячейках A1 и A2	=A2/A3
=A2*A3	Произведение значений в ячейках A1 и A2	=A2*A3
=A2^A3	Значение в ячейке A1 в степени, указанной в ячейке A2	=A2^A3
Формула	Описание	Результат
=5+2	Сумма чисел 5 и 2	=5+2
=5-2	Разность чисел 5 и 2	=5-2
=5/2	Частное от деления 5 на 2	=5/2
=5*2	Произведение чисел 5 и 2	=5*2
=5^2	Число 5 во второй степени	=5^2

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

10 формул в Excel, которые облегчат вам жизнь

15 мая 2020ЛикбезТехнологии

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

Чтобы применить любую из перечисленных функций, поставьте знак равенства в ячейке, в которой вы хотите видеть результат. Затем введите название формулы (например, МИН или МАКС), откройте круглые скобки и добавьте необходимые аргументы. Excel подскажет синтаксис, чтобы вы не допустили ошибку.

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

Есть и альтернативный способ указать аргументы. Если после названия функции добавить пустые скобки и нажать на кнопку «Вставить функцию» (fx), появится окно ввода с дополнительными подсказками. Можете использовать его, если вам так удобнее.

1. МАКС

Синтаксис: =МАКС(число1; [число2]; …).

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

2. МИН

Синтаксис: =МИН(число1; [число2]; …).

Функция «МИН» противоположна предыдущей: отображает наименьшее число в выбранных ячейках. В остальном принцип действия такой же.

Сейчас читают 🔥

Как скачать видео с YouTube на любое устройство

3. СРЗНАЧ

Синтаксис: =СРЗНАЧ(число1; [число2]; …).

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

4. СУММ

Синтаксис: =СУММ(число1; [число2]; …).

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

5. ЕСЛИ

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

Формула «ЕСЛИ» проверяет, выполняется ли заданное условие, и в зависимости от результата отображает одно из двух указанных пользователем значений. С её помощью удобно сравнивать данные.

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

6. СУММЕСЛИ

Синтаксис: =СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]).

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

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

7. СЧЁТ

Синтаксис: =СЧЁТ(значение1; [значение2]; …).

Эта функция подсчитывает количество выбранных ячеек, которые содержат числа. Аргументами могут выступать отдельные клетки и диапазоны. Для работы функции необходим как минимум один аргумент. Будьте внимательны: «СЧЁТ» учитывает ячейки с датами.

8. ДНИ

Синтаксис: =ДНИ(конечная дата; начальная дата).

Всё просто: функция «ДНИ» отображает количество дней между двумя датами. В аргументы сначала добавляют конечную, а затем начальную дату — если их перепутать, результат получится отрицательным.

9. КОРРЕЛ

Синтаксис: =КОРРЕЛ(диапазон1; диапазон2).

«КОРРЕЛ» определяет коэффициент корреляции между двумя диапазонами ячеек. Иными словами, функция подсчитывает статистическую взаимосвязь между разными данными: курсами доллара и рубля, расходами и прибылью и так далее. Чем больше изменения в одном диапазоне совпадают с изменениями в другом, тем корреляция выше. Максимальное возможное значение — +1, минимальное — −1.

10. СЦЕП

Синтаксис: =СЦЕП(текст1; [текст2]; …).

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

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

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

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

Из чего состоит формула в Excel

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

Простые

Сложные

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

Встроенные

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

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

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

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

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

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

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

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

Урок из курса «Аналитик данных» в Skypro

Из чего состоит формула в Excel

Основные знаки:

= с него начинают любую формулу;

( ) заключают формулу и ее части;

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

: ставят, чтобы обозначить диапазон ячеек, а не выбирать всё подряд вручную.

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

равенство =

меньше <

больше >

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

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

не равно <>

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

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

Простые

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

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

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

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

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

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

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

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

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

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

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

=ВПР(С1;А1:В6;2)

С1 — ячейка, в которую выписывают известные данные. В примере это код цвета.
А1 по В6 — диапазон ячеек. Ищем название цвета по коду.
2 — порядковый номер столбца для поиска. В нём указаны названия цвета.

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

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

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

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

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

=КОРЕНЬ(А1)

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

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

=ПРОПИСН(А1:С1)

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

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

=СТРОЧН(А2)

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

=СЧЕТ(А1:В5)

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

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

=СЖПРОБЕЛЫ(А1)

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

Сложные

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

=ПСТР(А1;9;5)

Введите =ПСТР.
Кликните на ячейку, где нужно выделить знаки.
Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 5.

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

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

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

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

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

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

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

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

=СУММЕСЛИ(В2:В5;">10")

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

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

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

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

=СУММЕСЛИМН(D2:D6;C2:C6;"сувениры";B2:B6;"ООО ХY")

D2:D6 — диапазон, из которого суммируем числа;
C2:C6 — диапазон ячеек для категории;
сувениры — условие, то есть числа другой категории учитываться не будут;
B2:B6 — диапазон ячеек для компании;
ООО XY — условие, то есть числа другой компании учитываться не будут.

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

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

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

=ЕСЛИ(СУММ(A1;B1)<65;СУММ(A1;B1)*1,5;(СУММ(A1;B1)*2))

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

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

Встроенные

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 с таблицами и формулами Excelka.ru

Работа в Excel с формулами и таблицами для чайников

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

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

Формулы в Excel для чайников

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

В Excel применяются стандартные математические операторы:

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

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

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

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

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

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

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

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

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

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

Как в формуле Excel обозначить постоянную ячейку

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

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

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

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

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

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

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

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

Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

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

Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

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

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

Как составить таблицу в Excel с формулами

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

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

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

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

Умные Таблицы Excel – секреты эффективной работы

В MS Excel есть много потрясающих инструментов, о которых большинство пользователей не подозревают или сильно недооценивает. К таковым относятся Таблицы Excel. Вы скажете, что весь Excel – это электронная таблица? Нет. Рабочая область листа – это только множество ячеек. Некоторые из них заполнены, некоторые пустые, но по своей сути и функциональности все они одинаковы.

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

Как создать Таблицу в Excel

В наличии имеется обычный диапазон данных о продажах.

Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

Есть горячая клавиша Ctrl+T.

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

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

Перед тем, как перейти к свойствам Таблицы, посмотрим вначале, как ее видит сам Excel. Многое сразу прояснится.

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

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

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

то она автоматически переделается в

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

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

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

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

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

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

5. Новые столбцы также автоматически включатся в Таблицу.

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

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

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

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

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

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

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

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

Работа в Excel c формулами и таблицами для чайников

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

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

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

В Excel применяются стандартные математические операторы:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Полные сведения о формулах в Excel

В этом курсе:

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

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Создание формулы, ссылающейся на значения в других ячейках

Введите знак равенства «=».

Примечание: Формулы в Excel начинаются со знака равенства.

Выберите ячейку или введите ее адрес в выделенной.

Введите оператор. Например, для вычитания введите знак «минус».

Выберите следующую ячейку или введите ее адрес в выделенной.

Нажмите клавишу ВВОД. В ячейке с формулой отобразится результат вычисления.

Просмотр формулы

При вводе в ячейку формула также отображается в строке формул.

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

Ввод формулы, содержащей встроенную функцию

Выделите пустую ячейку.

Введите знак равенства «=», а затем — функцию. Например, чтобы получить общий объем продаж, нужно ввести «=СУММ».

Введите открывающую круглую скобку «(«.

Выделите диапазон ячеек, а затем введите закрывающую круглую скобку «)».

Нажмите клавишу ВВОД, чтобы получить результат.

Скачивание книги «Учебник по формулам»

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

Подробные сведения о формулах

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

Формула также может содержать один или несколько таких элементов, как функции, ссылки, операторы и константы.

1. Функции. Функция ПИ() возвращает значение числа пи: 3,142.

2. Ссылки. A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2. (крышка) применяется для возведения числа в степень, а * (звездочка) — для умножения.

Константа представляет собой готовое (не вычисляемое) значение, которое всегда остается неизменным. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. выражение или его значение константами не являются. Если формула в ячейке содержит константы, а не ссылки на другие ячейки (например, имеет вид =30+70+110), значение в такой ячейке изменяется только после редактирования формулы. Обычно лучше помещать такие константы в отдельные ячейки, где их можно будет легко изменить при необходимости, а в формулах использовать ссылки на эти ячейки.

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

Стиль ссылок A1

По умолчанию Excel использует стиль ссылок A1, в котором столбцы обозначаются буквами (от A до XFD, не более 16 384 столбцов), а строки — номерами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Ячейка или диапазон

Ячейка на пересечении столбца A и строки 10

Диапазон ячеек: столбец А, строки 10-20.

Диапазон ячеек: строка 15, столбцы B-E

Все ячейки в строке 5

Все ячейки в строках с 5 по 10

Все ячейки в столбце H

Все ячейки в столбцах с H по J

Диапазон ячеек: столбцы А-E, строки 10-20

Создание ссылки на ячейку или диапазон ячеек с другого листа в той же книге

В приведенном ниже примере функция СРЗНАЧ вычисляет среднее значение в диапазоне B1:B10 на листе «Маркетинг» в той же книге.

1. Ссылка на лист «Маркетинг».

2. Ссылка на диапазон ячеек от B1 до B10

3. Восклицательный знак (!) отделяет ссылку на лист от ссылки на диапазон ячеек.

Примечание: Если название упоминаемого листа содержит пробелы или цифры, его нужно заключить в апострофы (‘), например так: ‘123’!A1 или =’Прибыль за январь’!A1.

Различия между абсолютными, относительными и смешанными ссылками

Относительные ссылки . Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2.

Скопированная формула с относительной ссылкой

Абсолютные ссылки . Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.

Скопированная формула с абсолютной ссылкой

Смешанные ссылки Смешанная ссылка содержит абсолютный столбец и относительную строку, а также абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет форму $A 1, $B 1 и т. д. Абсолютная ссылка на строку имеет форму $1, B $1 и т. д. При изменении положения ячейки, содержащей формулу, относительная ссылка будет изменена, а абсолютная ссылка не изменится. Если вы копируете или заполните формулу в строках или столбцах, относительная ссылка автоматически корректируется, а абсолютная ссылка не изменяется. Например, при копировании и заполнении смешанной ссылки из ячейки a2 в ячейку B3 она корректируется с = A $1 на = B $1.

Скопированная формула со смешанной ссылкой

Стиль трехмерных ссылок

Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. В Microsoft Excel используются все листы, указанные между начальным и конечным именами в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН. Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.

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

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

Что происходит при перемещении, копировании, вставке или удалении листов . Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

Вставка или копирование. Если вставить листы между листами 2 и 6, Microsoft Excel прибавит к сумме содержимое ячеек с A2 по A5 на новых листах.

Удаление . Если удалить листы между листами 2 и 6, Microsoft Excel не будет использовать их значения в вычислениях.

Перемещение . Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов.

Перемещение конечного листа . Если переместить лист 2 или 6 в другое место книги, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Удаление конечного листа . Если удалить лист 2 или 6, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Стиль ссылок R1C1

Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. При использовании стиля R1C1 в Microsoft Excel положение ячейки обозначается буквой R, за которой следует номер строки, и буквой C, за которой следует номер столбца.

10 формул Excel, которые пригодятся каждому

Не нужно тратить время на подсчёты в Microsoft Excel вручную, ведь существует множество формул, которые помогут быстро справиться с поставленными задачами и повысить точность ваших отчётов. Мы собрали 10 наиболее полезных формул, которые вы сможете выучить за один день.

Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

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

С помощью формулы вы можете:

посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

Англоязычный вариант: =COUNT(A1:A10)

Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

Англоязычный вариант: =COUNTA(A1:A10)

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

Англоязычный вариант: =LEN(A1)

Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.

Англоязычный вариант: =TRIM(A1)

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

Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.

ЛЕВСИМВ, ПСТР и ПРАВСИМВ

=ЛЕВСИМВ(адрес_ячейки; количество знаков)

=ПРАВСИМВ(адрес_ячейки; количество знаков)

=ПСТР(адрес_ячейки; начальное число; число знаков)

Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)

Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)

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

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

В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)

B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
A1:B13 = таблица, в которой находится искомое значение.
2 – номер столбца, где находится искомое значение.
ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.

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

Формула: =ЕСЛИ(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)

Англоязычный вариант: =IF(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)

Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

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

В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:

=ЕСЛИ(B1>C1; «Норма выполнена»; «Норма не выполнена»)

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

СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)

=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)

Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)

Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.

Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.

Используя функции на предыдущем примере, мы можем узнать:

СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.

СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.

СЧЁТЕСЛИ – количество продавцов, выполнивших норму.

Конкатенация

Формула: =(ячейка1&» «&ячейка2)

За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&» «&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между » «, чтобы между объединёнными данными появился пробел.

Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;» «; B1) или в англоязычном варианте =concatenate(A1;» «; B1).

Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

Эта статья является лишь верхушкой айсберга в изучении Excel. Для профессионального использования программы рекомендуем учится у профессионалов на курсах по Microsoft Excel.

Работа с Excel 2007: Формулы

Начало работы

Начните со знака равенства

Для расчёта в Excel используют формулы как обучающий пример рассмотрим следующее:

Два компакт-диска, купленные в феврале, стоили 12,99 и 16,99 грн.. Их суммарная стоимость равна расходам на компакт-диски в этом месяце.

Введите формулу в ячейку C6.
Нажмите клавишу ВВОД, чтобы отобразить результат вычислений по формуле.
Формула отображается в строке формул каждый раз при выборе ячейки C6.

Эти значения можно сложить в Excel, введя в ячейку C6 простую формулу.

Формулы в Microsoft Excel всегда начинаются со знака равенства (=). Чтобы сложить числа 12,99 и 16,99, введите в ячейку C6 формулу

=12,99+16,99

Знак сложения (+) — это математический оператор, обозначающий суммирование значений.

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

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

Для вычисления разности используйте знак «минус», для вычисления произведения — знак «звездочка» (*), для вычисления частного — знак «косая черта» (/). Обязательно начинайте формулу со знака равенства.

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

Суммирование значений по столбцу

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

На вкладке Главная в группе Правка нажмите кнопку Сумма.

Ячейки будут обведены цветовым индикатором, в ячейке B7 будет отображена формула.

Нажмите клавишу ВВОД, чтобы отобразить результат вычислений по формуле.

Щелкните в ячейке B7, чтобы отобразить формулу в строке формул.

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

Расходы за январь можно подсчитать, выбрав ячейку B7 и нажав кнопку Сумма в группе Правка на вкладке Главная. Таким образом будет введена функция «СУММ», складывающая значения по столбцу в интервале ячеек. Использование этой функции упрощает суммирование, если требуется сложить большое число значений, при этом нет необходимости набирать формулу.

По нажатии кнопки ВВОД в ячейке B7 будет отображен результат вычислений (95,94). Формула =CУММ(B3:B6) отображается в строке формул при каждом щелчке в ячейке B7.

Знаки «B3:B6» называются аргументом; аргументы сообщают функции «СУММ», какие значения требуется суммировать. Использование ссылки на ячейку (B3:B6) вместо самих значений в ячейках позволяет автоматически обновлять результат, если значение в ячейках изменится. Двоеточие (:) между B3 и B6 обозначает диапазон ячеек в столбце B, строки с третьей по шестую. Круглые скобки отделяют аргумент от функции.

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

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

Копирование формулы вместо создания новой

Иногда копирование формул существенно проще создания новых.

Для копирования выделите нужную ячейку, в которой расположена формула. Затем поместите указатель мыши в правом нижнем углу ячейки так, чтобы он принял форму черного крестика (+) далее зажмите левую кнопку мыши. Перетащите маркер заполнения на ячейку в которую нужно скопировать формулу. После того как кнопка мыши будет отпущена, в ячейке появится результат вычисленный по формуле. Формула отображается в строке формул в верхней части листа при каждом выделении ячейки.

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

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

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

Типы ссылок

Относительные. Относительные ссылки в формуле автоматически изменяются при копировании в строке или столбце. Это сделало возможным копирование формулы, относящейся к январю, для вычисления февральских расходов в первом уроке. Как показывает приведенный здесь пример, при копировании формулы =C4*$D$9 из строки в строку относительная ссылка на ячейку изменяется с C4 на C5 и C6.
Абсолютные. Абсолютные ссылки на ячейки имеют постоянное значение. Абсолютные ссылки не изменяются при копировании формулы из ячейки в ячейку. Абсолютные ссылки обозначаются знаком доллара ($), например $D$9. На рисунке показано, что при копировании со строки на строку формулы =C4*$D$9 абсолютная ссылка на ячейку $D$9 не изменяется.

Смешанные. Смешанная ссылка является абсолютной по столбцу и относительной по строке или относительной по столбцу и абсолютной по строке. Например, ссылка $A1 является абсолютной по столбцу A и относительной по строке 1. При копировании смешанной ссылки из ячейки в ячейку ее относительная ссылка меняется, а абсолютная остается неизменной.

Относительные ссылки на ячейки меняются от строки к строке.

Абсолютная ссылка на ячейку всегда ссылается на ячейку D9.

В ячейке D9 хранится значение скидки, равное 7 процентам.

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

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

Введите размер скидки 0,07 в свободную ячейку D9, в ячейку D4 введите формулу, начинающуюся с =C4*. Затем введите знак доллара ($) и букву D, чтобы указать абсолютную ссылку на столбец D, и $9, чтобы указать абсолютную ссылку на строку 9. Результатом вычислений по формуле будет произведение значений ячеек C4 и D9.

Затем с помощью маркера заполнения скопируйте формулу из ячейки D4 в ячейку D5. При копировании формулы относительная ссылка изменится с C4 на C5, а абсолютная ссылка на размер скидки в ячейке D9 останется неизменной ($D$9).

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

Нахождение среднего значения

Функцию «СРЗНАЧ» предназначена для вычисления среднего значения.

Excel введет формулу за вас. Щелкните по ячейке в которой вы хотите видеть результат. На вкладке Главная в группе Правка щелкните стрелку на кнопке Сумма и выберите в списке значение Среднее. В строке формул в верхней части листа отобразится формула =СРЗНАЧ(кординаты ячеек). после чего нажимаете Enter.

Формулу также можно ввести непосредственно в ячейку.

Примечание. Кнопка Сумма также находится на вкладке Формулы в группе Библиотека функций.

Непонятные знаки на листе

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

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

#ССЫЛКА! Ссылка на ячейку неверна. Ячейки могли быть удалены или вставлены поверх.

#ИМЯ? Опечатка в имени функции или использование имени, неизвестного программе Excel.

Использование других функций

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

Чтобы увидеть все другие функции, щелкните стрелку на кнопке Сумма в группе Правка на вкладке Главная и затем щелкните пункт Другие функции в списке. Откроется диалоговое окно Мастер функций — шаг 1 из 2, в котором можно искать функции. Это диалоговое окно является еще одним способом ввода формул в приложении Excel. Можно также отобразить другие функции, щелкнув вкладку Формулы.

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

Автоматически нумеровать строки

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

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

Что ты хочешь сделать?

Заполните столбец серией чисел
Используйте функцию ROW для нумерации строк
Показать или скрыть маркер заполнения

Заполнить столбец серией чисел

Выберите первую ячейку в диапазоне, который вы хотите заполнить.
Введите начальное значение серии.
Введите значение в следующую ячейку, чтобы установить шаблон.
Совет: Например, если вы хотите получить ряд 1, 2, 3, 4, 5…, введите 1 и 2 в первых двух ячейках. Если вам нужны серии 2, 4, 6, 8…, введите 2 и 4 .
Выберите ячейки, содержащие начальные значения.
Примечание. В Excel 2013 и более поздних версиях кнопка Быстрый анализ отображается по умолчанию, когда вы выбираете более одной ячейки, содержащей данные. Вы можете игнорировать кнопку, чтобы завершить эту процедуру.
Перетащите маркер заполнения через диапазон, который вы хотите заполнить.
Примечание. Когда вы перетаскиваете маркер заполнения по каждой ячейке, Excel отображает предварительный просмотр значения. Если вам нужен другой узор, перетащите маркер заполнения, удерживая нажатой правую кнопку, а затем выберите узор.
Чтобы заполнить в порядке возрастания, перетащите вниз или вправо. Чтобы заполнить в порядке убывания, перетащите вверх или влево.
Совет: Если вы не видите маркер заполнения, возможно, вам придется сначала отобразить его. Дополнительные сведения см. в разделе Отображение или скрытие маркера заполнения.
Примечание. Эти номера не обновляются автоматически при добавлении, перемещении или удалении строк. Вы можете вручную обновить последовательную нумерацию, выбрав два числа в правильной последовательности, а затем перетащив маркер заполнения в конец пронумерованного диапазона.

Используйте функцию ROW для нумерации строк

В первой ячейке диапазона, который вы хотите пронумеровать, введите =СТРОКА(A1) .
Функция ROW возвращает номер строки, на которую вы ссылаетесь. Например, =СТРОКА(A1) возвращает число 1 .
Перетащите маркер заполнения через диапазон, который вы хотите заполнить.
Совет: Если вы не видите маркер заполнения, возможно, вам придется сначала отобразить его. Дополнительные сведения см. в разделе Отображение или скрытие маркера заполнения.

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

Для ввода конкретных кодов порядковых номеров, таких как номера заказов на поставку, вы можете использовать ROW вместе с функцией TEXT . Например, чтобы начать нумерованный список с помощью 000-001 , введите формулу =ТЕКСТ(СТРОКА(A1);»000-000″) в первую ячейку диапазона, который вы хотите пронумеровать, и затем перетащите маркер заполнения в конец диапазона.

Показать или скрыть маркер заполнения

Маркер заполнения отображается по умолчанию, но вы можете включить или выключить его.

В Excel 2010 и более поздних версиях щелкните вкладку Файл и щелкните Параметры .
В Excel 2007 нажмите кнопку Microsoft Office , а затем щелкните Параметры Excel .
В категории Дополнительно в разделе Параметры редактирования установите или снимите флажок Включить маркер заполнения и перетаскивание ячеек , чтобы отобразить или скрыть маркер заполнения.

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

См. также

Обзор формул в Excel

Как избежать неработающих формул

Найдите и исправьте ошибки в формулах

Сочетания клавиш и функциональные клавиши Excel

Функции поиска и ссылки (ссылка)

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Использование операторов вычисления в формулах Excel

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно. .. Меньше

Операторы определяют тип вычисления, которое необходимо выполнить для элементов формулы. Excel следует общим математическим правилам для вычислений, а именно: Скобки , Экспоненты , Умножение и деление и Сложение и вычитание или аббревиатура PEMDAS (Пожалуйста, извините, моя дорогая тетя Салли). Использование круглых скобок позволяет изменить порядок вычислений.

Типы операторов. Существует четыре различных типа операторов вычисления: арифметический , сравнение , конкатенация текста и ссылка .

Арифметические операторы

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

Арифметический оператор	Значение	Пример
+ (плюс)	Дополнение	=3+3
– (знак минус)	Вычитание Отрицание	=3–3 =-3
* (звездочка)	Умножение	=3*3
/ (косая черта)	Отдел	= 3/3
% (знак процента)	Процент	30%

org/ListItem»>

Операторы сравнения

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

Оператор сравнения	Значение	Пример
= (знак равенства)	равно	=А1=В1
> (знак больше)	Больше	=А1>В1
< (знак меньше)	Менее	=А1<В1
>= (знак больше или равно)	Больше или равно	=А1>=В1
<= (знак меньше или равен)	Меньше или равно	=А1<=В1
<> (без знака равенства)	Не равно	=А1<>В1

org/ListItem»>

Оператор конкатенации текста

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

Текстовый оператор	Значение	Пример
и (амперсанд)	Соединяет или объединяет два значения для создания одного непрерывного текстового значения	=»Север»&»ветер» приводит к «Борей». Где A1 содержит «Фамилия», а B1 содержит «Имя», = A1 &», «& B1 приводит к «Фамилия, Имя».

Эталонные операторы

Объедините диапазоны ячеек для вычислений со следующими операторами.

Справочный оператор	Значение	Пример
: (двоеточие)	Оператор диапазона, который создает одну ссылку на все ячейки между двумя ссылками, включая две ссылки.	В5:В15
, (запятая)	Оператор объединения, объединяющий несколько ссылок в одну ссылку	=СУММ(B5:B15;D5:D15)
(пробел)	Оператор пересечения, который создает одну ссылку на ячейки, общие для двух ссылок	В7:Д7 С6:С8

Создать простую формулу в Excel

Excel

Формулы и функции

Формулы

Создать простую формулу в Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Больше. ..Меньше

Вы можете создать простую формулу для сложения, вычитания, умножения или деления значений на листе. Простые формулы всегда начинаются со знака равенства ( = ), за которым следуют константы, представляющие собой числовые значения, и операторы вычисления, такие как плюс ( + ), минус ( — ), звездочка ( * ) или косая черта. ( / ) знаки.

Возьмем пример простой формулы.

На рабочем листе щелкните ячейку, в которую вы хотите ввести формулу.
Введите = (знак равенства), а затем константы и операторы (до 8192 символов), которые вы хотите использовать в расчетах.
В нашем примере введите =1+1 .
Примечания:
- Вместо того, чтобы вводить константы в формулу, вы можете выбрать ячейки, содержащие значения, которые вы хотите использовать, и ввести операторы между выбранными ячейками.
- В соответствии со стандартным порядком математических операций умножение и деление выполняются перед сложением и вычитанием.
Нажмите Введите (Windows) или Верните (Mac).

Возьмем еще один вариант простой формулы. Введите =5+2*3 в другую ячейку и нажмите Введите или Верните . Excel умножает два последних числа и прибавляет к результату первое число.

Использовать автосумму

Вы можете использовать автосумму для быстрого суммирования столбца, строки или чисел. Выберите ячейку рядом с числами, которые вы хотите просуммировать, щелкните AutoSum на вкладке Home , нажмите Enter (Windows) или Return (Mac), и все!

Когда вы нажимаете Автосумма , Excel автоматически вводит формулу (которая использует функцию СУММ) для суммирования чисел.

Примечание: Вы также можете ввести ALT+= (Windows) или ALT++= (Mac) в ячейку, и Excel автоматически вставит функцию СУММ.

Вот пример. Чтобы добавить числа за январь в этот бюджет развлечений, выберите ячейку B7, ячейку непосредственно под столбцом чисел. Затем нажмите AutoSum . В ячейке B7 появляется формула, и Excel выделяет ячейки, которые вы суммируете.

Нажмите Enter, чтобы отобразить результат (95.94) в ячейке В7. Вы также можете увидеть формулу в строке формул в верхней части окна Excel.

Примечания:

Чтобы просуммировать столбец чисел, выберите ячейку непосредственно под последним числом в столбце. Чтобы просуммировать ряд чисел, выберите ячейку сразу справа.
Создав формулу, вы можете скопировать ее в другие ячейки вместо того, чтобы вводить ее снова и снова. Например, если вы скопируете формулу из ячейки B7 в ячейку C7, формула в ячейке C7 автоматически подстроится под новое местоположение и вычислит числа в ячейках C3:C6.
org/ListItem»>
Вы также можете использовать автосумму для нескольких ячеек одновременно. Например, вы можете выделить ячейки B7 и C7, щелкнуть Автосумма и одновременно подвести итоги по обоим столбцам.

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

Примечание. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите Введите (Windows) или Верните (Mac).

Данные
2
5
Формула	Описание	Результат
=А2+А3	Складывает значения в ячейках A1 и A2	=А2+А3
=А2-А3	Вычитает значение в ячейке A2 из значения в ячейке A1	=А2-А3
=А2/А3	Делит значение в ячейке A1 на значение в ячейке A2	=А2/А3
=А2*А3	Умножает значение в ячейке A1 на значение в ячейке A2 9А3
Формула	Описание	Результат
=5+2	Добавляет 5 и 2	=5+2
=5-2	Вычесть 2 из 5	=5-2
=5/2	Делит 5 на 2	=5/2

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Расширенные функции Excel [Единственное руководство по Excel на 2022 год]

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

Поиск цели

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

Давайте рассмотрим пример, чтобы лучше понять его.

Пример

В этом примере мы стремимся найти процентную ставку, если человек захочет заплатить

5000 долларов в месяц для погашения суммы кредита.

Функция

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

Давайте рассмотрим эту задачу пошагово, чтобы увидеть, как мы можем рассчитать процентную ставку, которая позволит погасить кредит в размере 400 000 долларов США при ежемесячном платеже в размере 5 000 долларов США.

Формула PMT теперь должна быть введена в ячейку, которая находится рядом с платежной ячейкой. В настоящее время в ячейке процентной ставки нет значения, Excel дает нам платеж в размере 3 333,33 доллара США, поскольку предполагается, что процентная ставка равна 0%. Игнорируй это.

Перейдите в раздел Данные > Анализ «что если» > Поиск цели

Нажмите OK. Вы увидите, что функция поиска цели автоматически дает процентную ставку, необходимую для выплаты суммы кредита.

Перейдите в раздел «Главная» > «Число» и измените значение на «Процент».

Ваш результат будет выглядеть следующим образом:

Анализ «что если» с помощью Solver

Анализ «что, если» — это метод изменения значений для опробования различных сценариев для формул в Advanced Excel.

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

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

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

Читайте также: Лучшее руководство по созданию информационной панели Excel

Активация надстройки Solver

На вкладке «Файл» щелкните «Параметры».
Перейдите в надстройки, выберите надстройку Solver и нажмите кнопку «Перейти».

Проверьте надстройку Solver и нажмите OK.

На вкладке «Данные» в группе «Анализ» вы можете увидеть добавление параметра «Решатель».

Как использовать решатель в Excel

В этом примере мы попытаемся найти решение простой задачи оптимизации.

Проблема: Предположим, вы владелец бизнеса и хотите, чтобы ваш доход составлял 8000 долларов.

Цель: Рассчитать количество продаваемых единиц и цену за единицу для достижения цели.

Например, мы создали следующую модель:

На вкладке Данные в группе Анализ нажмите кнопку Решатель.
В заданной цели выберите ячейку дохода и установите для нее значение 8000 долларов.
Чтобы изменить ячейку переменной, выберите ячейки C5, C6 и C10.

Нажмите «Решить».

Ваша модель данных изменится в соответствии с условиями.

Если-иначе

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

Синтаксис: =ЕСЛИ(тест, истинный результат, ложный результат)

Если-ошибка

Функция Excel ЕСЛИОШИБКА возвращает альтернативный результат, если формула выдает ошибку, и ожидаемый результат, если ошибки не обнаружено.

Синтаксис: =ЕСЛИОШИБКА (значение, значение_если_ошибка)

Например, Excel возвращает ошибку деления на ноль, когда формула пытается разделить число на 0.

С помощью функции ЕСЛИОШИБКА можно добавить сообщение, если формула дает ошибку.

Сводные таблицы

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

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

Пример данных

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

Вставка сводных таблиц

Чтобы вставить сводную таблицу на лист, выполните следующие действия:

Щелкните любую ячейку в наборе данных.
На вкладке Вставка в группе Таблицы щелкните Сводная таблица.

Появится диалоговое окно. Excel автоматически выберет ваш набор данных. Он также создаст новый рабочий лист для вашей сводной таблицы.

Нажмите «ОК». Затем он создаст рабочий лист сводной таблицы.

Поле для перетаскивания

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

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

Excel VBA

VBA расшифровывается как Visual Basic Analysis. Excel VBA — это управляемый событиями язык программирования Microsoft для приложений Office. Макросы — это то, что использует большинство людей, пишущих код VBA.

Включить параметр разработчика в Excel

Вкладка «Разработчик» по умолчанию скрыта на ленте, и для ее настройки необходимо выполнить следующие шаги, указанные ниже:

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

Перейдите в раздел «Настройка ленты» и установите флажок «Разработчик».

Интерфейс редактора VBA

Вы можете открыть интерфейс VBA с помощью сочетания клавиш ALT + F11 или перейти на вкладку «Разработчик» и щелкнуть Visual Basic.

Создание командной кнопки и назначение макроса командной кнопке

После включения вкладки разработчика и ознакомления с редактором VBA приступим к созданию макроса с помощью командной кнопки.

Чтобы разместить командную кнопку на рабочем листе, с которым вы работаете, вам необходимо выполнить шаги, указанные ниже:

Перейдите на вкладку «Разработчик» > «Вставка» > «Элементы управления ActiveX» > «Командная кнопка».

Перетащите командную кнопку на рабочий лист.

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

Щелкните правой кнопкой мыши командные кнопки и выберите «Просмотр кода».

Добавьте следующие строки кода, показанные ниже.

Закройте редактор VBA и нажмите кнопку команды на рабочем листе. Не забудьте отменить выбор режима дизайна.

Индекс

и соответствие

Это расширенная функция Excel. Функция ПОИСКПОЗ предназначена для возврата позиции значения в указанном диапазоне, а функция ИНДЕКС возвращает конкретное значение, присутствующее в одномерном диапазоне.

Функция ПОИСКПОЗ возвращает позицию искомого идентификатора. Функция ИНДЕКС вернет значение зарплаты, соответствующее должности.

Функция смещения

Функция СМЕЩ возвращает ссылку на диапазон ячеек, который представляет собой заданное количество строк и столбцов из ячейки или диапазона ячеек.

Синтаксис: OFFSET(ссылка, строки, столбцы, [высота], [ширина])

Пример:

Рассмотрим следующие данные:

Для ссылки на C4, начинающейся с A1, ссылка – это A1, строки – 3, а столбцы – 2:

Функция СУММ со смещением

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

Функция OFFSET возвращает диапазон 1×2, 8 строк ниже ячейки A2 и 1 столбец справа от ячейки A2. Затем функция СУММ вычисляет сумму этого диапазона.

Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью магистерской программы для бизнес-аналитиков. Зарегистрируйтесь сейчас!

Заключение

В этой статье вы узнали о некоторых дополнительных функциях Excel. Мы обсудили несколько функций, включая сводные таблицы, поиск цели и «что, если», а также решатель.

Ускорьте свою карьеру аналитика с помощью востребованных сегодня и мощных навыков Microsoft Excel, включая обучение использованию Power BI с курсом Simplilearn Business Analytics with Excel.

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

У вас есть к нам вопросы? Не стесняйтесь задавать их в разделе комментариев «Расширенного учебника по Excel», и наши специалисты оперативно ответят на них!

Как использовать Excel If Function: подробное руководство

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

Как использовать функцию Excel If?

Функция ЕСЛИ — это встроенная функция Excel, которая проверяет условие. Если условие выполнено, возвращается истинное значение; в противном случае возвращается ложное значение. Синтаксис следующий:

ЕСЛИ(условие, истинное_значение,ложное_значение)

Давайте рассмотрим пример, чтобы лучше понять это.

Здесь мы используем данные управления запасами, чтобы продемонстрировать использование функции ЕСЛИ.

Формула ЕСЛИ(G2=»В ожидании»,»ДА»,»НЕТ») проверяет, находится ли заказ в состоянии ожидания. Если это так, выполняется ИСТИННАЯ часть оператора (т. е. «ДА»), в противном случае выполняется значение ЛОЖЬ, отображающее «НЕТ».

Где еще можно использовать эту функцию?

Функцию ЕСЛИ можно использовать для выполнения вычислений на основе условия. Вот пара примеров формул:

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

ЕСЛИ(С2>20, С2*0,05, С2*0,07)

Формула проверяет, превышает ли значение в ячейке C2 20. Если значение TRUE, значение в ячейке C2 умножается на 0,05 (или пять процентов). Если FALSE, оно умножается на 0,07 (или семь процентов).

2. Условием внутри ЕСЛИ может быть любая функция. Например, ЕПУСТО, ЕЧИСЛО, СРЗНАЧ, СУММА и т. д.

Посмотрите на приведенную ниже формулу, чтобы проверить наличие пустых ячеек с помощью функции ЕПУСТО вместе с ЕСЛИ.

ЕСЛИ(ПУСТО(C2), «ИСТИНА», «ЛОЖЬ»)

Формула возвращает ИСТИНА, если ячейка C2 пуста, и ЛОЖЬ в противном случае.

Что такое функция IFNA?

Функция IFNA относится к группе функций проверки ошибок в Excel. Эта функция помогает обрабатывать ошибки #N/A.

Синтаксис следующий:

IFNA(формула, значение_если_ошибка)

Формула выполняется первой, и функция IFNA проверяет, является ли результат ошибкой #N/A. Это ошибка «нет доступного значения», которая возникает, когда значение не может быть найдено.

В этом случае возвращается значение_если_ошибка. Если ошибка #Н/Д не возникает, будет возвращен результат формулы.

Рассмотрим следующий пример:

Чтобы понять функцию IFNA, давайте сначала разберемся с функциями, которые генерируют ошибку #N/A. К таким функциям относятся ВПР, ГПР и т. д.
ВПР — это функция, помогающая искать значение в таблице по вертикали. Синтаксис этой функции: ВПР (значение, таблица, индекс_столбца, [диапазон_просмотра])

Как мы видим, функция ВПР генерирует ошибку #Н/Д, так как значение не найдено, поэтому попробуем использовать ее с IFNA для перехвата ошибки #Н/Д.

Мы будем использовать следующую формулу: IFNA(VLOOKUP(B9,A2:F5,6,TRUE),» NOT FOUND»)

Функция IFNA сначала выполняет функцию ВПР, которая генерирует ошибку #Н/Д, если значение не найдено. Поэтому мы будем использовать функцию IFNA вместе с VLOOKUP.
В случае ошибки #Н/Д выполняется и отображается альтернативное значение (значение_если_ошибка).

Поскольку значение в этом примере недоступно, мы получим сообщение «НЕ НАЙДЕНО».

Что такое функция ЕСЛИОШИБКА?

ЕСЛИОШИБКА также относится к группе функций проверки ошибок в Excel. Это разновидность функции ЕСЛИ. Функция ЕСЛИОШИБКА помогает распознавать ошибки и эффективно их обрабатывать.

Синтаксис следующий:

ЕСЛИОШИБКА(формула, значение_если_ошибка)

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

Демонстрация, показанная ниже, дает более четкое представление о функции ЕСЛИОШИБКА.

В этом примере используется формула ЕСЛИОШИБКА(C1/C2, «Ошибка в расчете»)

Сначала выполняется C1/C2. Если это приведет к какой-либо ошибке, будет отображено альтернативное значение. (т. е. «Ошибка в расчете»).

Как использовать вложенные ЕСЛИ в Excel

Вложенную функцию ЕСЛИ можно использовать для проверки нескольких условий. Он состоит из одной функции ЕСЛИ внутри другой функции ЕСЛИ. Синтаксис:

ЕСЛИ (условие1, истинное_значение1, ЕСЛИ (условие2, истинное_значение2, ложное_значение2))

Выполняется первая функция ЕСЛИ и оценивается условие1
Если условие1 истинно, то выполняется истинное_значение1.
Значение false_value1 встроено в следующую функцию ЕСЛИ. Следовательно, если условие1 ложно, следующая функция ЕСЛИ выполнит
Условие2 проверено, и если условие2 истинно, возвращается true_value2. В противном случае возвращается false_value2.

При необходимости можно продолжить вложение до семи функций ЕСЛИ.

В следующей демонстрации показано, как работает вложенный оператор IF:

IF(B4=»Красота»,»7%», IF(B4=»Электроника», IF(B4=»Спорт»,»4%»,»Не применимо»)))

В таблице справа показан процент налога для каждой линейки продуктов. Мы формулируем это с помощью вложенной функции ЕСЛИ.
Мы проверяем, является ли значение ячейки «Красота», которое возвращает «7%», если TRUE.
Если значение FALSE, значение ячейки проверяется на «Электроника», в котором отображается «5%», если TRUE.
В противном случае он снова проверит, является ли значение ячейки «Спорт». На этот раз отображается «4%», если ИСТИНА, и «Неприменимо», если ЛОЖЬ.

Функция IFS

Функция IFS устраняет сложность использования вложенных ЕСЛИ в Excel. Он использует более короткий и простой синтаксис, ориентированный на несколько критериев.

Эта функция доступна только в Excel 2019 и Excel для Office 365.

Синтаксис для IFS:

IFS(условие1, значение1, [условие2, значение2],…)

Функция IFS состоит из пары условие-значение. Он проверяет условия по порядку и возвращает значение, соответствующее первому результату TRUE. Эта функция может проверять до 127 условий.

Например:

=IFS(C2=»Бакалея»,»5%»,C2=»Электроника»,»12%»,C2=»Автомобили»,»28%»)

В этом примере проверяется значение C2.
Если C2 содержит значение «Бакалея», ставка налога будет отображаться как «5%».
В противном случае снова проверяется значение в C2, чтобы убедиться, что оно содержит «Электроника». Если TRUE, налоговая ставка будет «12%».
В противном случае значение C2 окончательно проверяется с помощью «Автомобилей». Если это условие выполнено, отображается «18%».
Если ни одно из условий не выполняется, эта функция вернет ошибку #Н/Д.

Подробнее об Excel IF

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

Вот несколько примеров, которые показывают, как комбинировать функцию ЕСЛИ Excel с другими:

Функция ЕСЛИ с И

Синтаксис для этого будет:

ЕСЛИ(И(условие1, условие2,…), истинное_значение, ложное_значение)

Эта функция проверит, выполнены ли все \условия (условие1, условие2 и т. д.), и только после этого будет выполнено значение true_value. Даже если одно условие не выполнено, false_value будет выполнено.

Например:

ЕСЛИ(И(B2>5,C2<5),"Отлично","Удовлетворительно")

В этом примере мы рассчитываем рейтинги в отношении доставки, стоимости и сроков доставки. Единственная комбинация, которая получит отличную оценку, — это стоимость доставки ниже пяти долларов, а также срок доставки менее пяти дней. В противном случае он получит только «удовлетворительную» оценку.

Функция ЕСЛИ с ИЛИ

Синтаксис для этого будет следующим:

ЕСЛИ(ИЛИ(условие1, условие2,…), истинное_значение, ложное_значение)

Если выполняется хотя бы одно условие из всех условий (условие1, условие2 и т. д.), будет выполнено значение true_value. Если все условия FALSE, то выполняется false_value.

Например:

ЕСЛИ(ИЛИ(B2=»Фрукты»,B2=»Овощи»),»Бакалея»,»Другое»)

В этом примере мы находим категорию продукта.
Мы используем функцию ЕСЛИ с оператором ИЛИ, чтобы проверить, относится ли продукт к категории фруктов или овощей.
Если да, то он относится к категории «Бакалея». В противном случае он отображается как «Другие».

Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью магистерской программы для бизнес-аналитиков. Зарегистрируйтесь сейчас!

Заключение

Мы надеемся, что эта статья помогла вам познакомиться с функцией ЕСЛИ в Excel. Вы можете улучшить свои навыки работы с Excel на практике, работая с большим количеством данных, и вы будете на пути к тому, чтобы стать профессионалом в Excel. Вы можете улучшить свои навыки работы с Excel, записавшись на бесплатный курс Business Analytics with Excel от Simplilearn.

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

Пошаговое руководство, обзор и примеры

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

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

Давайте продолжим и разберемся, что такое ВПР в Excel.

Что такое ВПР в Excel?

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

Приведенная ниже формула ВПР ищет название компании с идентификатором компании 3.

В следующем разделе вы поймете, как использовать функцию ВПР.

ВПР Формула

Мы можем использовать функцию ВПР с помощью простого синтаксиса. Синтаксис для ВПР:

ВПР(искомое_значение, массив_таблиц, номер_индекса_столбца,[диапазон_просмотра])

Где,

lookup_value: указывает значение, которое вы хотите найти в наших данных.
table_array: это место, где представлены значения в Excel.
col_index_number: указывает номер столбца, из которого нам нужно вернуть значение.
range_lookup: два варианта; если установлено значение FALSE, это означает, что мы ищем точное совпадение. Если значение TRUE, то ищем приблизительное совпадение.

Как найти точное совпадение с помощью функции ВПР?

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

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

Во втором аргументе указываем расположение таблицы. Как видите, расположение таблицы — A2:F11.
Третий аргумент указывает номер индекса столбца. Это говорит нам, какое значение должно быть возвращено из строки, которую мы ищем. В примере столбец продукта равен 3.
Последний аргумент — логическое выражение. Здесь для значения установлено значение FALSE, чтобы функция ВПР возвращала точное совпадение со значением. Если точное значение не найдено, отображается ошибка N/A.

С помощью ВПР в Excel мы можем искать и приблизительное совпадение. Вы узнаете об этом в следующем разделе.

Как найти приблизительное совпадение с помощью функции ВПР?

Approximate Match работает, находя следующее наибольшее значение, которое меньше значения поиска, которое мы указываем.

В приведенном ниже примере мы используем функцию ВПР, чтобы узнать, какой объем оперативной памяти имеет спецификация ноутбука стоимостью 1300 евро. Кроме того, мы знаем, что этого значения нет в таблице. Итак, давайте воспользуемся приблизительным совпадением, чтобы найти решение. Нам нужно отсортировать первый столбец в порядке возрастания. В противном случае функция ВПР вернет неверные значения.

Сначала скопируйте информацию о цене и оперативной памяти в новое место и укажите значение для поиска. Здесь искомое значение составляет 1300 долларов.

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

Отфильтровать информацию о ценах в порядке возрастания. Нажмите «ОК».

Теперь введите формулу ВПР. Первым аргументом будет значение ВПР. Второй аргумент указывает диапазон таблицы. В третьем аргументе мы указываем номер столбца, чтобы возвращались значения для этого столбца. Наконец, последний аргумент устанавливается в значение TRUE. Это позволит функции ВПР найти приблизительное совпадение значения.

После ввода формулы нажмите Enter. Возвращаемое значение в этом случае составляет 8 ГБ по цене 1300 долларов.

Давайте перейдем к следующему разделу и поймем, как использовать функцию ВПР для нескольких критериев.

Как использовать функцию ВПР для нескольких критериев?

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

Например, если нам нужно найти цену Apple MacBook Pro, где название компании и название продукта находятся в двух разных столбцах, мы используем вспомогательный столбец. В этом столбце будут храниться конкатенированные значения обоих столбцов.

Выполните следующие действия, чтобы выполнить ВПР с несколькими критериями.

Сначала щелкните правой кнопкой мыши заголовок столбца и выберите «Вставить». Это поможет вам вставить столбец слева от столбца «Компания». Назовите его «Компания и продукт».

При создании вспомогательного столбца введите формулу =C2&”-”&D2. Затем перетащите формулу в остальные ячейки столбца.

После успешного создания объединенного столбца мы можем искать значение. Здесь мы ищем цену Apple MacBook Pro.
Итак, вводим формулу =ВПР(B15&”-”&C15,B1:G11,5,ЛОЖЬ)

Первый параметр помогает объединить искомое значение, т. е. B15&»-» &C15. Мы также можем использовать формулу конкатенации, т. е. СЦЕПИТЬ (B15, «-«, C15). Второй параметр — это диапазон таблицы. Третий параметр указывает индекс столбца, который возвращает значение из этого столбца. Последний параметр равен FALSE, так как мы ищем точное совпадение. При нажатии на ввод цена будет возвращена следующим образом.

Советы по эффективному использованию функции ВПР

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

Если вы не укажете range_lookup в функции ВПР, она также будет оценивать неточные совпадения. Однако он будет использовать точное совпадение, если оно существует в ваших данных.
Если в столбце подстановки есть повторяющиеся значения, будет выбрано только первое значение.
Функция ВПР не чувствительна к регистру.
Добавление столбца после применения формулы ВПР покажет ошибку, поскольку жестко запрограммированные значения индекса не изменяются автоматически при добавлении или удалении столбцов.

Распространенные ошибки в функции ВПР

#Н/Д! ошибка — возникает, если функция ВПР не может найти совпадение с предоставленным искомым_значением.
#ССЫЛКА! ошибка — возникает, если аргумент col_index_num > количество столбцов в предоставленном table_array; или формула пытается ссылаться на несуществующие ячейки.
#ЗНАЧ! ошибка – возникает, если: аргумент col_index_num меньше 1 или не распознан как числовое значение; или Аргумент range_lookup не распознан как одно из логических значений TRUE или FALSE.