Функции МАКС и МАКСА в Excel для анализа максимальных значений

В самом названии функции МИН содержится ответ на вопрос, что эта функция делает. Ну конечно, функция МИН возвращает минимальное значение диапазона или введенных чисел. Если мы напишем, к примеру, в ячейке А1 такую функцию =МИН(3; 4; -7; 23; 0; 73; 12), то получим в ячейке минимальное число среди перечисленных, а именно -7.

Функция МИН Excel

Обратите внимание на ограничение — в скобках мы можем указать не более 255 чисел.

Таким же образом мы можем указывать в качестве аргумента не конкретные числа, а диапазон ячеек в котором ищем наименьшее значение. К примеру, если мы хотим найти минимальное значение в диапазоне A1:A7 и поместить результат в ячейку B2, то должны в ячейке B2 написать =МИН(A1:A7)

Сравнение

Создадим условие:
Проверяется ячейка А1 и сравнивается с числом двадцать. Это выражение. Если число больше отобразится надпись «больше 20». Иначе — меньше.

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

Пример 2

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

Диаграмма сравнения роста показателей в Excel

Как в Excel показать сравнить рост двух различных показателей? Можно «просто» сравнить данные. Правда, если они не сопоставимы, то особо ничего не увидишь. Например, можно построить гистограмму с двумя столбиками.

Рейтинг в excel как сделать

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

Рейтинг в excel как сделать

Но есть еще один вариант — пожалуй, самый наглядный: методом базисной подстановки, который любит журнал The Economist. Как это сделать в Excel, смотрите в инструкции.

Рейтинг в excel как сделать

А теперь по порядку:

  1. Исходные данные — суммы розничных продаж и продаж на рынках за несколько лет.
  1. Готовим данные для диаграммы.
  • Рассчитываем рост продаж в процентах по отношению к первому году. А в столбце с первым годом (в таблице – 2008 год) проставляем 100%.

Рейтинг в excel как сделать

  • Добавляем строку, которая будет показана на графике вместо оси Х. Назовем эту ось «псевдоось». Указываем значения в строке – 100% для всех ячеек и добавляем один «лишний» столбец – чтобы на графике появилось место для подписей.

Рейтинг в excel как сделать

  • Следующие строки – маркеры для последнего года и их подписи.

В первом столбце укажите названия направлений и сумму продаж за последний год: «розница 21,4трлн.р.», «рынки 2,3трлн.р.»
Значения всех столбцов периодов, кроме последнего — #Н/Д. В последнем столбце – процент фактического роста.

Рейтинг в excel как сделать

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

Рейтинг в excel как сделать
Рейтинг в excel как сделать

  1. Строим график: выделите исходные данные и нажмите Вставка -> График.

Рейтинг в excel как сделать Рейтинг в excel как сделать

  • Измените тип диаграммы: Конструктор -> Изменить тип диаграммы.

Выберите комбинированную диаграмму и настройте типы диаграмм, как на рисунке.

  • Выделите столбцы гистограммы правой кнопкой мышки, перейдите к Формату ряда данных. Задайте боковой зазор 0%.
  • Щелкните по подписи оси Х правой кнопкой мышки, перейдите к Формату оси. В параметрах оси укажите ее положение – на делениях. Чтобы на оси легче читалось название последнего года, введите формулу в названии последнего столбца =» «&2012
  • Настройте график «псевдооси» — выделите линию, задайте для нее темно-серый цвет и ширину 0,75 пт.
  • Добавьте подписи для маркеров – выделите маркеры, перейдите в меню Конструктор -> Добавить элемент диаграммы -> Метки данных -> Справа.
  • Настройте отображение меток данных. Щелкните по метке мышкой, перейдите к формату подписей данных и включите в подписи только имя ряда.
  1. Настройте цвета на диаграмме, её название и отображение осей. Диаграмма готова.

Больше интересных диаграмм в статье:

В этой статье мы собрали 12 необычных диаграммам Excel со ссылками на краткие инструкции по их построению.

Сумма если больше или равно, Сумма если меньше или равно. СУММЕСЛИ.

Знаки, которые используются: >=, <=.

Сумма если больше или равно.

Формула в ячейке, как пример, выглядит вот так:

Рассмотрим на примере. Нам нужно посчитать сумму значений в столбце (E3:E27). Но суммировать нужно только те числа, которые равны или больше значения числа 500. По стандартному алгоритму вызываем функцию СУММЕСЛИ.

В строке Диапазон выбираем диапазон ячеек, которые нужно просуммировать (E3:E27).

В Критерий пишем:>=500.

Диапазон_суммирования не заполняем.

Нажимаем ОК. Получаем сумму значений равною числу 500, плюс все, что больше этой цифры в заданном диапазоне (E3:E27) (Рис. 13).

Сумма если больше или равно.

Сумма если меньше или равно

Формула в ячейке, как пример, выглядит вот так:

Алгоритм точно такой же, как и в предыдущем примере. Только вместо знак больше, используем знак меньше и равно <=. Получаем сумму всех чисел равную или меньше числу 500, которые находятся в заданном диапазоне (E3:E27).

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

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функция имеет следующий синтаксис.

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

лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

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

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

Примеры использования функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ в Excel

  • Выделить диапазон: столбец или ячейки с числами, которые нужно сложить.
  • Выбрать Вставка → Функции → СУММ или ∑ → СУММ.
  • Выбрать первую пустую ячейку в конце диапазона.
  • Выбрать ∑ → СУММ.
  • Вписать в свободную ячейку значения нужных диапазонов.

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать ∑ → СРЗНАЧ.
  • Вписать в свободную ячейку значения диапазонов.

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать подходящую функцию из списка: ∑ → МИН.
  • Вписать в свободную ячейку значения нужных диапазонов.

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать функцию ∑ → МАКС.
  • Вписать в свободную ячейку значения нужных диапазонов.

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать функцию ∑ → СЧЕТ.
  • Вписать в свободную ячейку значения нужных диапазонов.

Excel + Google-таблицы
с 0 до PRO

Подробнее

Пример использования функции

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

Добавим столбец «Фамилия» в таблицу с итогами. Используем комбинацию функций ИНДЕКС и ПОИСКПОЗ, чтобы найти фамилии участников, зная их время.

победители марафона

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

Обратите внимание, мы легко и быстро определили победителей, используя всего две формулы. Нам не пришлось делать ничего вручную: сортировать список по времени, копировать и вставлять в результаты в итоговую табличку. А если исходные данные изменятся, формулы всё пересчитают сами.

Есть в Excel функция, которая ищет наибольшее n-e значение, почитать о ней можно в этой статье!

Примеры работы в Excel с функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ

Пример 1. В конструкторском отделе предприятия работают 8 инженеров. Необходимо определить четвертую наибольшую и наименьшую зарплаты соответственно.

Внесем данные в таблицу:

Для определения наименьшей 4-й зарплаты в отделе введем следующую формулу в ячейку C2:

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

  1. B3:B10 – массив значений заработной платы для всех сотрудников;
  2. 4 – порядок искомого наименьшего значения в массиве.

Чтобы определить наибольшую 4-ю зарплату вводим формулу в ячейке D2:

Аргументы этой функции соответствуют тем, которые принимала функция НАИМЕНЬШИЙ в рамках данного примера.

Получаем следующие результаты:

То есть, наименьшая и наибольшая четвертые зарплаты в отделе равны 3200 и 4000 денежных единиц соответственно.

#2. Решаем задачи в Excel. Поиск наибольшего значения

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

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

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

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

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

Читайте также:  Трехмерные ссылки в формулах Excel

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

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

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

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

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

Итак, давайте решим задачу.

Как и в прошлый раз , воспользуемся функцией ИНДЕКС , которая возвращает значение ячейки, заданного номером строки и номером столбца.

Первый ее аргумент — массив значений. Выбираем все ячейки с именами сотрудников.

Далее мы должны указать второй аргумент — номер строки, из которой будет возвращено значение. Нам нужно выводить значение из столбца A той строки, в которой значение в столбце B равно вычисленному значению максимального возраста (находится в ячейке F1), и тут идеально подойдет функция ЕСЛИ — если вычисленное значение из ячейки F1 равно одному из значений диапазона B2:B6, то нам нужно вывести номер этой строки. Определить номер строки довольно просто и в этом нам поможет функция СТРОКА , которая выводит фактический номер строки, определяемый адресом ячейки.

Так, например, пустая функция СТРОКА() выведет номер строки Экселя, в которой находится сама эта формула. Мы же в формуле должны вывести номер строки из диапазона значений и у нас в первой строке Экселя находится заголовок таблицы, учитывать который не нужно. Поэтому из полученного номера строки вычтем единицу, чтобы исключить строку заголовка.

Ну а в случае когда условие функции ЕСЛИ не выполняется выведем пустую строку, для этого укажем две кавычки. Итоговая формула будет выглядеть так:

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

Думаю, что проще будет объяснять уже на готовой формуле. Отличаться она будет лишь дополнительной функцией НАИМЕНЬШИЙ , которой мы «обернем» наше условие.

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

Для его вычисления снова воспользуемся функцией СТРОКА , но перед этим давайте разберемся с функцией НАИМЕНЬШИЙ , так как она не самая простая для понимания.

Рассмотрим ее на таком примере — введем диапазон значений 1, 2, 3, 4, 5 и укажем формулу:

То есть нам нужно вывести третье наименьшее значение в диапазоне.

Логично, что в диапазоне чисел третье наименьшее равно 3. Если же мы изменим в диапазоне 3 на 1, то и результат также изменится — на 2.

То есть функция проходит весь диапазон значений и, выстраивает их по возрастанию, а затем отсчитывает k -ое (в нашем примере третье) значение по списку.

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

Надеюсь, работа этой функции прояснилась.

Теперь возвращаемся к нашей формуле. Выглядеть она будет так:

Здесь формула СТРОКА()-1 вычисляет аргумент k для функции НАИМЕНЬШИЙ .

Как я уже упоминал, пустая функция СТРОКА возвращает номер строки с формулой. Так как у нас формула находится во второй строке Экселя, то мы можем использовать это значение для вычисления соответствующего аргумента k . Чуть дальше мы рассмотрим работу формулы и вам станет понятен принцип вычисления.

Чтобы сделать формулу формулой массива необходимо нажать не Enter , а сочетание клавиш Ctrl+Shift+Enter .

На то, что формула стала формулой массива указывают фигурные скобки, в которые она была заключена.

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

Вы видите, что в ячейке F2 появился правильный результат.

Давайте проанализируем формулу с помощью соответствующего инструмента со вкладки Формулы .

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

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

В первую очередь проверяется условие в функции ЕСЛИ (текущее вычисление подчеркивается).

Мы видим, что условие выдало пять ответов, то есть массив значений, равный по размеру диапазону ячеек с именами сотрудников. Из пяти ответов только последний ИСТИНА , что мы можем также подтвердить, взглянув на первоначальный диапазон значений, в котором пока только у одного сотрудника возраст равен максимальному.

Далее на базе полученных значений функция СТРОКА выдает массив с номерами строк.

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

Переходим к функции НАИМЕНЬШИЙ , которая должна вернуть нам первое наименьшее значение. И как раз для того, чтобы автоматически вычислять это значение и была вставлена формула СТРОКА()-1 . Мы изначально вставили формулу во вторую строку Экселя, поэтому она выдаст единицу, а значит функция НАИМЕНЬШИЙ выдаст первое наименьшее значение, которое будет равно 5.

Соответственно, функция ИНДЕКС выдаст значение, находящееся в пятой строке столбца А, а это и есть имя сотрудника с максимальным возрастом:

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

Во-первых, в изначальной формуле использовались относительные ссылки и при автозаполнении это проявилось, то есть диапазоны «съехали».

Во-вторых, функция выводит сообщение #ЧИСЛО! из-за особенностей вычислений. Давайте разберемся в этой ситуации.

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

Не забудем в конце нажать сочетание Ctrl+Shift+Enter , чтобы сделать формулу формулой массива.

Теперь снова автозаполнением протянем формулу на весь диапазон и проанализируем вычисление во второй строке. На определенном этапе в функции НАИМЕНЬШИЙ мы будем искать второе наименьшее значение в диапазоне:

Но в диапазоне есть только одна цифра и именно поэтому появляется ошибка #ЧИСЛО! , поскольку формула возвращает недопустимое значение.

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

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

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

С моей точки зрения, наиболее сложный для понимания элемент формулы — это функция НАИМЕНЬШИЙ и вычисление аргумента k . Поэтому предлагаю посмотреть видео на эту тему, в котором более детально изложен материал:

Функция ЕСЛИ с условием ИЛИ

В качестве примера рассмотрим, как начислить в Экселе премию в размере 40% всем сотрудникам, которые являются бухгалтерами или директорами. То есть произведем выборку по двум условиям:

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

  • Редактируем аргументы функции. Логическое выражение будет представлять собой: ИЛИ(D4=«бухгалтер»;D4=«директор»). В «Значение_если_истина» пишем 40, а в «Значение_если_ложь» — 0. Кликаем «Ок».

  • Копируем формулу, растягивая ее на остальные ячейки. Смотрим результат — премия 40% начислена директору и двум бухгалтерам.

Пример записи функции МИН

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

    Запись функции «МИН», как обычно, начинается с ее объявления. Для этого в выбранной ячейке напишите =МИН() . Переместите курсор между круглыми скобками, чтобы начать запись аргументов.

Объявление функции МИН в новой ячейке при ее написании в Excel

Выборка диапазона данных для функции МИН в Excel после ее объявления

Просмотр результата вывода функции МИН в Excel при использовании одного аргумента

Добавление разделителя для аргументов при записи функции МИН в Excel

Добавление второго и последующих аргументов при записи функции МИН в Excel

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

Применение математических функций

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

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

Перемещение в Мастер фнкуций в Microsoft Excel

Также можно запустить Мастер функций, перейдя во вкладку «Формулы». Там нужно нажать на кнопку «Вставить функцию», расположенную на самом левом краю ленты в блоке инструментов «Библиотека функций».

Вставить функцию в Microsoft Excel

Существует и третий способ активации Мастера функций. Он осуществляется с помощью нажатия комбинации клавиш на клавиатуре Shift+F3.

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

Переход к выбору функций в Microsoft Excel

Открывается выпадающий список. Выбираем в нем позицию «Математические».

Выбор функций в Microsoft Excel

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

Переход к аргументу математической функции в Microsoft Excel

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

Читайте также:  Специальная вставка в Excel для массового сложения значений

Выбор математических функций в Microsoft Excel

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

Переход к другим функциям в Microsoft Excel

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

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

Функция СУММ в Microsoft Excel

СУММЕСЛИ

Оператор СУММЕСЛИ также подсчитывает общую сумму чисел в ячейках. Но, в отличие от предыдущей функции, в данном операторе можно задать условие, которое будет определять, какие именно значения участвуют в расчете, а какие нет. При указании условия можно использовать знаки «>» («больше»), «<» («меньше»), «< >» («не равно»). То есть, число, которое не соответствует заданному условию, во втором аргументе при подсчете суммы в расчет не берется. Кроме того, существует дополнительный аргумент «Диапазон суммирования», но он не является обязательным. Данная операция имеет следующий синтаксис:

Функция СУММЕСЛИ в Microsoft Excel

ОКРУГЛ

Как можно понять из названия функции ОКРУГЛ, служит она для округления чисел. Первым аргументом данного оператора является число или ссылка на ячейку, в которой содержится числовой элемент. В отличие от большинства других функций, у этой диапазон значением выступать не может. Вторым аргументом является количество десятичных знаков, до которых нужно произвести округление. Округления проводится по общематематическим правилам, то есть, к ближайшему по модулю числу. Синтаксис у этой формулы такой:

Кроме того, в Экселе существуют такие функции, как ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ, которые соответственно округляют числа до ближайшего большего и меньшего по модулю.

Функция ОКРУГЛ в Microsoft Excel

ПРОИЗВЕД

Задачей оператора ПРИЗВЕД является умножение отдельных чисел или тех, которые расположены в ячейках листа. Аргументами этой функции являются ссылки на ячейки, в которых содержатся данные для перемножения. Всего может быть использовано до 255 таких ссылок. Результат умножения выводится в отдельную ячейку. Синтаксис данного оператора выглядит так:

Функция ПРОИЗВЕД в Microsoft Excel

С помощью математической формулы ABS производится расчет числа по модулю. У этого оператора один аргумент – «Число», то есть, ссылка на ячейку, содержащую числовые данные. Диапазон в роли аргумента выступать не может. Синтаксис имеет следующий вид:

Функция ABS в Microsoft Excel

СТЕПЕНЬ

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

Функция СТЕПЕНЬ в Microsoft Excel

КОРЕНЬ

Задачей функции КОРЕНЬ является извлечение квадратного корня. Данный оператор имеет только один аргумент – «Число». В его роли может выступать ссылка на ячейку, содержащую данные. Синтаксис принимает такую форму:

Функция КОРЕНЬ в Microsoft Excel

СЛУЧМЕЖДУ

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

Функция СЛУЧМЕЖДУ в Microsoft Excel

ЧАСТНОЕ

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

Функция ЧАСТНОЕ в Microsoft Excel

РИМСКОЕ

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

Функция РИМСКОЕ в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12021 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Функция СУММЕСЛИ в Excel с примерами

В предыдущей статье мы рассмотрели синтаксис функции СУММЕСЛИ в Excel, теперь давайте закрепим знания на практике при помощи ряда примеров формулы СУММЕСЛИ:

СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)

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

Примечание . Обратите внимание, что в формулах Excel СУММЕСЛИ оператор сравнения, за которым следует число или текст, всегда должен быть заключен в двойные кавычки (“”).

Пример формулы СУММЕСЛИ

Сумма, если больше

Суммирует значения больше 5 в ячейках A2:A10.

Сумма, если меньше

=СУММЕСЛИ(A2:A10; “<10”; B2:B10)

Суммирует значения в ячейках B2:B10, если соответствующее значение в столбце A меньше 10.

Сумма, если равно

(можно не указывать)

Суммирует значения в ячейках B2:B10, если соответствующая ячейка в столбце A не равна значению в ячейке D1.

Сумма если больше или равно

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

Сумма если меньше или равно

=СУММЕСЛИ(A2:A10; “<=10”; B2:B10)

Суммирует значения в ячейках B2:B10, если соответствующее значение в столбце A меньше либо равно 10.

СУММЕСЛИ в Excel примеры с текстовым критерием

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

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

Пример формулы СУММЕСЛИ

Сумма, если равно

=СУММЕСЛИ(A2:A8; “бананы”; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит точное слово «бананы» и никакие другие слова или символы. Ячейки, содержащие «зеленые бананы», «бананы зеленые» или «бананы!» не будут считаться.

=СУММЕСЛИ(A2:A8; “*бананы*”; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит слово «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «зеленые бананы» или «бананы зеленые», будут учитываться для суммирования.

Сумма, если не равно

=СУММЕСЛИ(A2:A8; “<>бананы”; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит любое значение, отличное от слова «бананы». Если ячейка содержит «бананы» вместе с некоторыми другими словами или символами, такими как «желтые бананы» или «бананы желтые», такие ячейки будут учитываться для суммирования.

=СУММЕСЛИ(A2:A8; “<>*бананы*”; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A не содержит слова «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «желтые бананы» или «бананы желтые», не суммируются.

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

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

=СУММЕСЛИ(A2:A8; “<>Банан Дамский пальчик”; C2:C8)

3-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с проверкой на неравенство

Примечание . Как и большинство других функций Excel, СУММЕСЛИ нечувствительна к регистру, что означает, что «<> бананы», «<> Бананы» и «<> БАНАНЫ» будут давать точно такой же результат.

СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки

Если вы хотите получить более универсальную формулу Excel СУММЕСЛИ, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:

В этом случае вам не придется менять формулу СУММЕСЛИ, основанную на другом критерии – вы просто вводите новое значение в ссылочной ячейке.

4-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1

Примечание. Когда вы используете логическое выражение с ссылкой на ячейку, вы должны использовать двойные кавычки (“”), чтобы начать текстовую строку и амперсанд (&), чтобы объединить и завершить строку, например «<>» и F1.

Оператор «равенства» (=) можно не использовать до ссылки на ячейку, поэтому обе приведенные ниже формулы эквивалентны и правильны:

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

Доступны следующие подстановочные знаки:

Звездочка (*) – представляет любое количество символов

Знак вопроса (?) – представляет один символ в определенном месте

Пример 1. Суммирование значений, основанные на частичном совпадении

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

=СУММЕСЛИ(A2:A8; “*бананы*”;C2:C8) – критерий включает текст, заключенный в звездочки (*).

=СУММЕСЛИ(A2:A8; “*”&F1&”*”; C2:C8) – критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для конкатенации строки.

5-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с подстановочными знаками для суммирования по частичному совпадению

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

=СУММЕСЛИ(A2:A8; “бананы*”; C2:C8) – значения суммы в C2:C8, если соответствующая ячейка в столбце A начинается со слова «бананы».

=СУММЕСЛИ(A2:A8; “*бананы”; C2:C8) – значения суммы в C2:C8, если соответствующая ячейка в столбце A заканчивается словом «бананы».

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

6-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример использования функции СУММЕСЛИ с текстовым условием

Пример 2. Суммирование по заданному количеству символов

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

7-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с условием суммирования, если длина текстовой строки в шесть букв

Пример 3. Сумма ячеек, соответствующих текстовым значениям

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

=СУММЕСЛИ(A2:A8; “?*”; C2:C8) – суммирует значения из ячеек C2:C8, если соответствующая ячейка в столбце A содержит не менее 1 символа.

=СУММЕСЛИ(A2:A8; “*”; C2:C8) – учитывает пустые ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например =””.

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

Пример 4. Использование * или ? как обычные символы

Если вы хотите использовать либо *, либо ? для обработки в функции СУММЕСЛИ как литерала, а не подстановочного знака, то используйте перед этим знаком тильду (

). Например, следующая формула СУММЕСЛИ просуммирует значения в ячейках C2:C8, если ячейка в столбце A в той же строке содержит знак вопроса:

8-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с суммированием значений, соответствующие знаку вопроса в другом столбце

СУММЕСЛИ в Excel примеры с датами

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

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

Пример формулы СУММЕСЛИ

Сумма по определенной дате

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B равна 29.10.2017.

Сумма, если дата больше либо равна заданной в формуле дате

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше или равна 29.10.2017.

Сумма, если дата больше даты, указанной в ячейке

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше даты, указанной в ячейке F1.

Если вы хотите суммировать значения на основе текущей даты, вам необходимо использовать СУММЕСЛИ в сочетании с функцией СЕГОДНЯ(), как показано ниже:

Пример формулы СУММЕСЛИ

Суммирование значений, за текущую дату

=СУММЕСЛИ(B2:B9; СЕГОДНЯ (); C2:C9)

Суммирование значений, меньше текущей даты, то есть до сегодняшнего дня.

=СУММЕСЛИ(B2:B9; “<“&СЕГОДНЯ(); C2:C9)

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

=СУММЕСЛИ(B2:B9; “>”& СЕГОДНЯ(); C2:C9)

Суммирование значений за неделю от текущей даты. (т.е. сегодня + 7 дней).

=СУММЕСЛИ(B2:B9; ” Пример функции СУММЕСЛИ с суммированием количества продуктов, которые будут отправлены через неделю – Функция СУММЕСЛИ в Excel” src=”https://naprimerax.org/images/751/primer-funktcii-summesli-s-summirovaniem-kolichestva-produktov-kotorye-budut-otpravleny-cherez-nedeliu-funktciia-summesli-v-excel.png” alt=”9-funktciia-summesli-v-excel” />

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с суммированием количества продуктов, которые будут отправлены через неделю

СУММЕСЛИ в заданном диапазоне дат

Если вам необходимо суммировать значения между двумя датами, то необходимо использовать комбинацию, а точнее разницу двух функций СУММЕСЛИ. В версиях старше Excel 2007 вы можете использовать функцию СУММЕСЛИМН, которая позволяет использовать несколько условий. Эту функцию мы рассмотрим в следующей статье. А так как данная статья посвящена функции СУММЕСЛИ, то приведем пример использования СУММЕСЛИ в диапазоне дат:

=СУММЕСЛИ(B2:B9; “>=01.11.2017”; C2:C9) – СУММЕСЛИ(B2:B9; “>=01.12.2017”; C2:C9)

Эта формула суммирует значения в ячейках C2:C9, если дата в столбце B находится между 1 ноября 2017 года и 30 ноября 2017, включительно.

10-funktciia-summesli-v-excel

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ дата в диапазоне

Эта формула может показаться немного сложной с первого взгляда, но при более близком рассмотрении это выглядит довольно просто. Первая функция СУММЕСЛИ объединяет все ячейки в C2:C9, где соответствующая ячейка в столбце B больше или равна дате начала (в данном примере 1 ноября). Затем вам просто нужно вычесть значения, которые попадают после даты окончания (30 ноября), с помощью второй функции СУММЕСЛИ.

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

Примеры использования функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ в Excel

= Мир MS Excel/Статьи об Excel

  • 1
  • 2
  • 3
Приёмы работы с книгами, листами, диапазонами, ячейками [6]
Приёмы работы с формулами [13]
Настройки Excel [3]
Инструменты Excel [4]
Интеграция Excel с другими приложениями [4]
Форматирование [1]
Выпадающие списки [2]
Примечания [1]
Сводные таблицы [1]
Гиперссылки [1]
Excel и интернет [1]
Excel для Windows и Excel для Mac OS [2]

Исходные данные
Исходными данными может быть любой числовой набор данных, таких как горизонтальный или вертикальный диапазон, двумерный массив или даже не смежные диапазоны или отдельно заполненные ячейки. Для удобства я взял одномерный вертикальный массив А1:А5

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

1. Решение с дополнительным столбцом

В В1 напишите формулу

и скопируйте её на диапазон В2:В5. Эта промежуточная формула в дополнительном столбце будет “убирать” из исходных данных нули, заменяя их на пустую строку “”

В С1 напишите формулу

Она и вернёт искомый результат – число 3

Если в диапазоне есть отрицательные числа, то вместо формулы

Как это работает: Вот эта часть формулы A1:A5<>0 формирует массив , после чего применяя функцию ЕСЛИ() мы преобразуем массив в следующий: . Так как функция МИН() игнорирует текстовые и логические значения, то в итоге получаем искомое – число 3

Если в диапазоне есть отрицательные числа, то вместо формулы

Как это работает: С помощью функции СЧЁТЕСЛИ мы подсчитываем количество нулей в диапазоне и прибавив к этому количеству единицу мы возвращаем первое наименьшее число более нуля с помощью функции НАИМЕНЬШИЙ

Пример формулы с ВПР и ПОИСКПОЗ

Пример таблицы табель премии изображен ниже на рисунке:

Назначением данной таблицы является поиск соответственных значений премии в диапазоне B5:K11 на основе определенной сумы выручки и магазинов с пределами минимальных или максимальных размеров выплаты премии. Сложность возникает при автоматическом определении размера премии, на которую может рассчитывать сотрудник при преодолении определенной границы выручки. Так как нет четко определенной одной суммы выплаты премии для каждого вероятного размера выручки. Есть только пределы нижних и верхних границ сумм премий для каждого магазина.

Например, нам нужно чтобы программа автоматически определила какая возможная минимальная премия для продавца из 3-тего магазина, выручка которого преодолела уровень в 370 000.

  1. В ячейку B14 введите размер выручки: 370 000.
  2. В ячейке B15 укажите номер магазина: 3.
  3. В ячейке B16 введите следующую формулу:

В результате определена нижняя граница премии для магазина №3 при выручке больше >370 000, но меньше


Поиск ближайшего значения Excel формулой ВПР и ПОИСКПОЗ:

В первом аргументе функции ВПР указываем ссылку на ячейку с критерием поискового запроса (исходная сумма выручки), который содержится в ячейке B14. Область поиска в просматриваемом диапазоне A5:K11 указывается во втором аргументе функции ВПР. А в третьем аргументе должен быть указан номер столбца, но он пока неизвестен. Из второго критерия поискового запроса известно только что исходный номер столбца таблицы относится к 3-тьему магазину (ячейка B15).

Чтобы определить номер столбца, который содержит заголовок «Магазин 3» следует использовать функцию ПОИСКПОЗ. Как само название функции говорит о том, что ее задачей является поиск позиции где находится значение внутри определенного диапазона ячеек. В нашем случаи мы ищем значение: «Магазин 3», которое следует еще определить используя конструкцию сложения амперсандом текстовой строки «Магазин » и критерий из ячейки B15. Поэтому в первому аргументе функции указываем «Магазин »&B15. Во втором аргументе функции ПОИСКПОЗ указывается ссылка на просматриваемый диапазон A3:J3 где нужно искать исходное значение (указанное в первом аргументе). Третий аргумент содержит значение 0 – это значит, что функция возвратит результат, как только найдет первое совпадение значений. В нашем примере значение «Магазин 3» находится на позиции номер 6 в диапазоне A3:J3, а значит функция ПОИСКПОЗ возвращает число 6 которое будет использовано в качестве значения для третьего критерия функции ВПР. Есть еще и четвертый аргумент в функции ВПР который определяет точность совпадения найденного значения с критерием (0-точное совпадение; 1 или пусто – приближенное совпадение), но в формуле он опущен по следующей причине. Получив все аргументы функция ВПР не находит значения 370 000 и так как не указан последний аргумент выполняет поиск ближайшего значения в Excel – 350 000.

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

Легко заметить, что эта формула отличается от предыдущей только номером столбца указанном в третьем аргументе функции ВПР. А, следовательно, нам достаточно лишь к значению, полученному через функцию ПОИСКПОЗ добавить +1, так как сумма максимально возможной премии находиться в следующем столбце после минимальной суммы соответствующий критериям поискового запроса.

Полезные советы для формул с функциями ВПР, ИНДЕКС и ПОИСКПОЗ:

Чтобы пошагово проанализировать формулу Excel любой сложности, рационально воспользоваться встроенными инструментами в разделе: «ФОРМУЛЫ»-«Зависимости формул». Например, особенно полезный инструмент для пошагового анализа вычислительного цикла – это «Вычислить формулу».

Функция ВПР ищет значения в диапазоне слева на право. То есть анализирует ячейки только в столбцах, расположенных с правой стороны относительно от первого столбца исходного диапазона, указанного в первом аргументе функции. Если структура расположения данных в таблице не позволяет функции ВПР по этой причине охватить для просмотра все столбцы, тогда лучше воспользоваться формулой из комбинации функций ИНДЕКС и ПОИСКПОЗ.

Ссылка на основную публикацию
Adblock
detector