Как составить формулу в Excel: подробный гид по Excel таблицам для чайников

Добрый день.

Когда-то, написать самостоятельно формулу в Excel — для меня было чем-то невероятным. И даже, несмотря на то, что часто приходилось работать в этой программе, ничего кроме текста не набивал…

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

И так, начнем…

• 2. Сложение значений в строках (формула СУММ и СУММЕСЛИМН)
  2.1. Сложение с условием (с условиями)

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

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

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

• плюс (+) — сложение;
• минус (-) — вычитание;
• косая полоска (/) — деление;
• звезда (*) — умножение;
• символ крыши «домика» (^) — степень.

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

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

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

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

С учетом сказанного можно подвести итог, как правильно составить формулу. Для этого поставьте в нужную ячейку знак равно, а после этого укажите необходимые действия, к примеру, В1+В2 или А1*А2.

Thank you!

We will contact you soon.

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

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

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

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

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

Правила создания формул

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

Приведем пример простой формулы по сложению двух цифр. Алгоритм такой:

1. Выделите ячейку, в которой должен быть итоговый результат (С3).
2. Поставьте знак равно. Учтите, что он появляется не только в ячейке, но и в верхней строке.
3. Укажите адрес секции, которая должна быть первой, к примеру, С1.
4. Поставьте знак, который интересует в конкретной формуле, например, плюс (+).
5. Введите адрес графы для второго числа, к примеру, С2.
6. Проверьте, что у вас получилась надпись = С1+С2, жмите на Ввод.

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

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

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

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

Выделение с помощью мышки

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

1. Выделите секцию в таблице, где будет находиться формула (В3).
2. Поставьте знак равно (=).
3. Жмите на ячейку, которая должна стоять первой в расчетах. Для текущего примера В1. Цифра будет обведена, а номер секции появится в итоговой графе.
4. Укажите действие, которое необходимо сделать. Как вариант, жмите на умножение (*).
5. Выделите ту ячейку, цифра в которой должна стоять второй в формуле (В2). Обратите внимание на то, что необходимый участок будет обведен пунктирной линией.
6. Жмите на Ввод, чтобы система выполнила расчет

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

Редактирование

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

Для внесения правок сделайте следующее:

• Укажите секцию, где необходимо внести изменения. В нашем случае это С3.
• Перейдите в строку формулы вверху, чтобы внести коррективы. Как вариант, можно нажать прямо на ячейку, чтобы глянуть и внести изменения в формулу.
• Внесите новые данные и обратите внимание, какие ячейки при этом подсвечиваются программой. На этом этапе будьте внимательны, чтобы избежать ошибок.
• Жмите Ввод и подтвердите внесенные изменения. К примеру, если раньше формула имела вид С1+С2, ее можно поменять на С1-С2 или любой другой вариант. Главное, чтобы в указанных ячейках стояли цифры.

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

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

Полезные ссылки: Сanvas онлайн редактор, ТОП программ для записи экрана, Paint рисовать онлайн без скачивания.

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

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

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

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

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

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

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

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

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

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

Сложные формулы

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

Здесь необходимо учесть общие правила для математических действий. Они просты.

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

Приведем вариант формулы для ячейки С4. В ней можно указать следующие данные (В2+В3)*0,3.

Это означает, что формула сначала суммирует два показателя, а полученное значение рассчитывается с процентами (30% записывается в виде 0,3).

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

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

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

Функции программы

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

Синтаксис

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

К примеру, вам надо сложить значения в ячейках В1, В2, В3, В4. СУММ – функция добавляющая значения. При этом формат записи имеет следующий вид.

Сначала ставится знак равно (=). После него идет функция СУММ, а за ней диапазон ячеек (В1:В4).

В программе имеются опции, в которых вообще не указываются аргументы. Если написать СЕГОДНЯ (), приложение вернет день с учетом времени в ОС компьютера.

Основные функции

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

Кратко рассмотрим их названия и особенности:

1. СУММ. С помощью опции можно вычислить сумму двух и больше чисел. К примеру, если записать в качестве адреса (А1:А6), программа просуммирует все цифры в секциях, начиная с А1 по А6. Если указать опцию в формате (А1; А6), расчет будет выполнен только применительно к двум указанным секциям.
2. СЧЕТ. Задача формулы в том, чтобы рассчитать число ячеек с числовыми обозначениями в одном ряду. К примеру, для получения информации о числе ячеек с цифрами между В1 и В20, пропишите такую формулу Excel — = СЧЕТ (В1:В20).
3. СЧЕТ3. В отличие от прошлой опции, здесь учитываются все секции с внесенными данными (не только с цифрами). Плюс в том, что СЧЕТ3 можно использовать для разных типов информации, в том числе указанной в буквенном отображении.
4. ДЛСТР. Задача опции состоит в расчете числа знаков в секции. Но учтите, что система считает все действия, в том числе сделанные пробелы.
5. СЖПРОБЕЛЫ. Цель опции в удалении лишних пробелов. Это полезно, когда информация переносится с других источников, где уже имеется много ненужных пробелов.
6. ВПР. Используется, если нужно найти элементы в таблице или диапазоне по строкам.
7. ЕСЛИ. Опция применяется, если расчет осуществляется с условием «ЕСЛИ» и большим объемом данных с различными сценариями. Применение функции позволяет сравнить значения. Если результат правдивый, программа выполняет какое-то еще действие.
8. МАКС и МИН — определяют наибольший и наименьший параметр из перечня.

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

Правила использования

Для лучшего понимания рассмотрим, как правильно добавлять функцию в Excel. Используем параметр СРЗНАЧ.

Алгоритм действий такой:

1. Жмите на ячейку, где необходимо установить формулу — В11.
2. Пропишите знак равно =, а после укажите название нужной опции СРЗНАЧ.
3. Укажите диапазон секций в круглых скобках (В3:В10).
4. Кликните на Ввод.

После указания этих параметров программа суммирует данные в ячейках с В3 по В10, а после этого вычисляет их среднее значение.

Применение Автосуммы

Для удобства почти любую опцию можно вставить с помощью Автосуммы. Сделайте следующее:

• Выберите и жмите на секцию, в которую необходимо вбить формулу (С 11).
• В группе Редактирования в разделе Главная отыщите и нажмите на стрелку возле надписи Автосумма.

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

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

Комбинированные формулы

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

Рассмотрим ситуацию, когда необходимо просуммировать три числа и умножить их на коэффициент 1,5 или 1,6 в зависимости от того, какое получилось число (больше или меньше 100).

В таком случае запись имеет следующий вид: =ЕСЛИ(СУММ(А2:С2)<100;СУММ(А2:С2)*1,5;СУММ(А2:С2)*1,6).

В приведенной выше формуле используется две опции — ЕСЛИ и СУММА. В первом случае учитывается три результата — условие, правильно или неправильно.

Здесь действуют такие условия:

1. Эксель суммирует числа в ячейках с А2 по С2.
2. Если полученное число меньше 100, тогда параметр умножается на 1,5.
3. Если итоговая цифра превышает 100, в таком случае результат умножается на 1,6.

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

Полезные лайфхаки: Как установить онлайн консультант на сайт, Как работать в Трелло, Как сохранить файл в формате pdf.

Отличие в версиях MS Excel

Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

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

1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».

1. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».

1. Указываем данные в нужном диапазоне. Для того чтобы отобразить результат, нужно нажать на «OK».

1. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.

Виды ссылок и их особенности

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

Относительная

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

К примеру, при копировании формулы =В2+С2 из второй строки в третью, запись поменяется на =В3+С3. Это полезно, когда необходимо скопировать вычисление одновременно для разных столбцов.

Алгоритм действий при использовании:

1. Выберите и нажмите на ячейку (секцию), где планируется запись формулы (В3).
2. Пропишите данные для расчета интересующего параметра — =С3*D3.
3. Жмите на ввод. Программа делает расчеты, а итог показывается в выбранной секции.
4. Найдите маркер автоматического заполнения в В3.
5. Нажмите на него и, удерживая левую кнопку мышки, перемести маркер по нужным ячейкам, например, с В3 до В10.
6. После отпускания кнопок формула копируется во все указанные секции. Одновременно с этим выполняются расчеты.

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

Абсолютные ссылки

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

Для обозначения абсолютной ссылки в Excel применяется символ доллара ($). В зависимости от варианта расстановки меняется и указание пользователя.

Рассмотрим несколько вариантов:

• $А$3 — строка/столбец не меняется;
• А$3 — срока остается неизменной;
• $А3 — столбец не меняется.

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

Для примера сделаем расчет для ставки налога, введенного в секцию D1. Так как параметр во всех случаях идентичен, внедрим формулу $D$1 в расчет.

Для этого:

1. Укажите ячейку, где будет находиться формула, к примеру, C3.
2. Пропишите вычисление в виде (А3*В3)* $D$1.
3. Нажмите на кнопку Ввод.
4. Найдите маркер автоматического заполнения в С3 (точка справа внизу).
5. Нажмите на него, кликните на левую кнопку мышки и потяните маркер вниз, захватив диапазоны с С4:С14.
6. Отпустите кнопку мышки, после чего формула копируется в ячейки со ссылкой. Во всех случаях делаются автоматические расчеты.

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

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

С уважением, Александр Петренко специально для проекта proudalenku.ru

Транскрибация онлайн

Лучшие CRM системы

Фотостоки

Zoom конференция

Визажист с нуля

Профессия Бухгалтер

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

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

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

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

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