Как вывести в формулу в excel: Отображение и печать формул — Служба поддержки Майкрософт

Содержание

Поиск минимального или максимального значения по условию

59375
31.03.2016
Скачать пример

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF), СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?

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

Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки — столбец с ценами.

Для будущего удобства, конвертируем исходный диапазон с ценами в «умную таблицу». Для этого выделите его и выберите на вкладке Главная — Форматировать как таблицу (Home — Format as Table) или нажмите Ctrl+T. Наша «поумневшая» таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1[Товар] или Таблица1[Цена]. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design), которая появляется, если щелкнуть в любую ячейку нашей «умной» таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу — это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS). Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS):

=МИНЕСЛИ(Диапазон_чисел; Диапазон_проверки1; Условие1; Диапазон_проверки2; Условие2 … )

где

Диапазон_чисел — диапазон с числами, из которых выбирается минимальное или максимальное

Диапазон_проверки — диапазон, который проверяется на выполнение условия

Условие — критерий отбора

Например, в нашем случае:

Просто, красиво, изящно. Одна проблема — функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.

Способ 2. Формула массива

В английской версии это будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена]))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE).

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т. к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

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

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

База_данных — вся наша таблица вместе с заголовками.

Поле — название столбца из шапки таблицы, из которого выбирается минимальное значение.

Критерий — таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM), ДМАКС (DMAX), БСЧЁТ (DCOUNT), которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

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

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка — Сводная таблица (Insert — Pivot Table). В появившемся окне нажмите ОК:

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по — Минимум:

Вытаскивать данные из сводной в дальнейшие расчеты теперь можно с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA), которую мы подробно разбирали ранее:

Ссылки по теме

Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

Что такое «умные таблицы» в Excel и как с ними работать

Как найти среднее, минимальное и максимальное значение в Excel

В Microsoft Office Excel можно работать с цифрами и узнавать любое числовое значение. Этот табличный процессор справится практически со всеми расчётами. Он идеально подходит для бухгалтерского учёта. Для вычислений существуют специальные инструменты — формулы. Их можно применять к диапазону или к отдельным ячейкам. Чтобы узнать минимальную или максимальную цифру в группе клеток, необязательно искать их самостоятельно. Лучше воспользоваться предназначенными для этого опциями. Также полезно будет разобраться, как посчитать среднее значение в Excel.

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

В этой статье мы расскажем, как вычислить различные значения в Эксель

Максимальное и минимальное

Вот как найти максимальное значение в Excel:

Поставьте курсор-ячейку в любое место.
Перейдите в меню «Формулы».
Нажмите «Вставить функцию».
В списке выберите «МАКС». Или напишите это слово в поле «Поиск» и нажмите «Найти».
В окне «Аргументы» введите адреса диапазона, максимальное значение которого вам нужно узнать. В Excel имена клеток состоят из буквы и цифры («B1», «F15», «W34»). А название диапазона — это первая и последняя ячейки, которые в него входят.
Вместо адреса можно написать несколько чисел. Тогда система покажет самое большее из них.
Нажмите «OK». В клетке, в которой стоял курсор, появится результат.

Следующий шаг — укажите диапазон значений

Теперь будет легче разобраться, как найти минимальное значение в Excel. Алгоритм действий полностью идентичен. Просто вместо «МАКС» выберите «МИН».

Среднее

Среднее арифметическое вычисляется так: сложить все цифры из множества и поделить на их количество. В Экселе можно посчитать суммы, узнать, сколько ячеек в строке и так далее. Но это слишком сложно и долго. Придётся использовать много разных функций. Держать в голове информацию. Или даже что-то записывать на листочек. Но можно упростить алгоритм.

Вот как найти среднее значение в Excel:

Поставьте ячейку курсор в любое свободное место таблицы.
Перейдите на вкладку «Формулы».
Нажмите на «Вставить функцию».
Выберите «СРЗНАЧ».
Если этого пункта нет в списке, откройте его с помощью опции «Найти».
В области «Число1» введите адрес диапазона. Или напишите несколько цифр в разных полях «Число2», «Число3».
Нажмите «OK». В ячейке появится нужное значение.

Нажмите ОК для подсчета

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

Другие способы

Максимальное, минимальное и среднее можно узнать и другими способами.

Найдите панель функций с обозначением «Fx». Она над основной рабочей областью таблицы.
Поставьте курсор в любую ячейку.
Введите в поле «Fx» аргумент. Он начинается со знака равенства. Потом идёт формула и адрес диапазона/клетки.
Должно получиться что-то вроде «=МАКС(B8:B11)» (максимальное), «=МИН(F7:V11)» (минимальное), «=СРЗНАЧ(D14:W15)» (среднее).
Кликните на «галочку» рядом с полем функций. Или просто нажмите Enter. В выделенной ячейке появится нужное значение.
Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.

Впишите диапазон и нажмите Enter

Найти и вычислить поможет Excel-инструмент «Автофункции».

Поставьте курсор в ячейку.
Перейдите в раздел «Формулы».
Найдите кнопку, название которой начинается на «Авто». Это зависит от выбранной в Excel опции по умолчанию («Автосумма», «Авточисло», «Автосмещ», «Автоиндекс»).
Нажмите на чёрную стрелочку под ней.
Выберите «МИН» (минимальное значение), «МАКС» (максимальное) или «СРЗНАЧ» (среднее).
В отмеченной клетке появится формула. Кликните на любую другую ячейку — она будет добавлена в функцию. «Растяните» рамку вокруг неё, чтобы охватить диапазон. Или щёлкайте по сетке с зажатой клавишей Ctrl, чтобы выделять по одному элементу.
Когда закончите, нажмите Enter. Результат отобразится в клетке.

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

Преобразование ячеек сводной таблицы в формулы рабочего листа

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

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

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

Узнайте о распространенных сценариях преобразования сводных таблиц в формулы листа

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

Изменение порядка и удаление ячеек

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

Вставка строк и столбцов

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

Использовать несколько источников данных

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

Используйте ссылки на ячейки для изменения пользовательского ввода

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

Создать неоднородный макет строки или столбца (также называемый асимметричной отчетностью)

Допустим, вам нужно создать отчет, содержащий столбец «Фактические продажи за 2008 год», столбец «Фактические продажи за 2009 год». столбец под названием «Прогнозируемые продажи», но вам не нужны никакие другие столбцы. Вы можете создать отчет, содержащий только эти столбцы, в отличие от сводной таблицы, для которой требуется симметричная отчетность.

Создание собственных формул куба и выражений MDX

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

Примечание. Вы можете преобразовать сводную таблицу интерактивной аналитической обработки (OLAP) только с помощью этой процедуры.

Чтобы сохранить сводную таблицу для будущего использования, рекомендуется сделать копию книги перед преобразованием сводной таблицы, нажав Файл > Сохранить как . Дополнительные сведения см. в разделе Сохранение файла.
Подготовьте сводную таблицу, чтобы минимизировать перестановку ячеек после преобразования, выполнив следующие действия:
- Измените макет на макет, наиболее похожий на нужный.
- Взаимодействуйте с отчетом, например фильтруйте, сортируйте и изменяйте структуру отчета, чтобы получить нужные результаты.
Щелкните сводную таблицу.
На вкладке Параметры в группе Инструменты щелкните Инструменты OLAP , а затем щелкните Преобразовать в формулы .
Если фильтры отчета отсутствуют, операция преобразования завершается. Если есть один или несколько фильтров отчета, то Convert to Formulas 9Отображается диалоговое окно 0008.
Решите, как вы хотите преобразовать сводную таблицу:
Преобразование всей сводной таблицы
- org/ListItem»>
  Установите флажок Преобразовать фильтры отчетов .
  Это преобразует все ячейки в формулы рабочего листа и удаляет всю сводную таблицу.
  Преобразование только меток строк сводной таблицы, меток столбцов и области значений, но сохранение фильтров отчетов
- Убедитесь, что флажок Convert Report Filters снят. (Это значение по умолчанию.)
  Это преобразует все метки строк, метки столбцов и ячейки области значений в формулы рабочего листа и сохраняет исходную сводную таблицу, но только с фильтрами отчета, чтобы вы могли продолжить фильтрацию с помощью фильтров отчета.
  Примечание. Если формат сводной таблицы версии 2000–2003 или более ранней, вы можете преобразовать только всю сводную таблицу.
Щелкните Преобразовать .
Операция преобразования сначала обновляет сводную таблицу, чтобы обеспечить использование актуальных данных.
Во время выполнения операции преобразования в строке состояния отображается сообщение. Если операция занимает много времени и вы предпочитаете конвертировать в другое время, нажмите ESC, чтобы отменить операцию.
Примечания:
- Нельзя преобразовать ячейки с фильтрами, примененными к скрытым уровням.
- Вы не можете преобразовать ячейки, в полях которых есть настраиваемые вычисления, созданные на вкладке Показать значения как диалогового окна Параметры поля значений . (На вкладке Options в 9Группа 0007 Активное поле , щелкните Активное поле , а затем щелкните Параметры поля значений .)
- Для преобразованных ячеек форматирование ячеек сохраняется, но стили сводных таблиц удаляются, поскольку эти стили могут применяться только к сводным таблицам.

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

org/ListItem»>
Убедитесь, что включена команда Generate GETPIVOTDATA в группе PivotTable на вкладке Параметры .
Примечание. Команда Создать GETPIVOTDATA устанавливает или снимает параметр Использовать функции GETPIVOTTABLE для ссылок сводной таблицы в категории Формулы раздела Работа с формулами в диалоговом окне Параметры Excel .
В сводной таблице убедитесь, что ячейка, которую вы хотите использовать в каждой формуле, видна.
В ячейке листа за пределами сводной таблицы введите нужную формулу до точки, в которую вы хотите включить данные из отчета.
org/ListItem»>
Щелкните ячейку сводной таблицы, которую вы хотите использовать в формуле сводной таблицы. В формулу добавляется функция рабочего листа GETPIVOTDATA, которая извлекает данные из сводной таблицы. Эта функция продолжает извлекать правильные данные при изменении макета отчета или при обновлении данных.
Закончите ввод формулы и нажмите клавишу ВВОД.

Примечание. Если удалить из отчета любую из ячеек, на которые есть ссылки в формуле ПОЛУЧИТЬСВОДНЫЕДАННЫЕ, формула вернет #ССЫЛКА!.

Как использовать функцию ПРЕОБРАЗОВАТЬ (WS)

В этом учебнике Excel объясняется, как использовать функцию ПРЕОБРАЗОВАТЬ Excel с синтаксисом и примерами.

Описание

Функция ПРЕОБРАЗОВАТЬ Microsoft Excel преобразует число из одной единицы измерения в другую единицу измерения.

Функция ПРЕОБРАЗОВАТЬ — это встроенная функция Excel, относящаяся к категории Инженерная функция . Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию ПРЕОБРАЗОВАТЬ можно ввести как часть формулы в ячейку рабочего листа.

Синтаксис

Синтаксис функции ПРЕОБРАЗОВАТЬ в Microsoft Excel:

 ПРЕОБРАЗОВАТЬ(число, из_единиц, в_единицы)

Параметры или Аргументы

число

Преобразуемое значение (число записывается в from_units ).

от_юнитов

Исходные единицы измерения для номер . Это может быть одно из следующих значений:

по Фаренгейту

Значение	Блок измерения	Тип измерения
«м»	Счетчик	Длина/расстояние
«ми»	Миля	Длина/расстояние
«Нми»	Морская миля	Длина/Расстояние
«в»	Дюйм	Длина/Расстояние
«фут»	Ножка	Длина/Расстояние
«ярд»	Двор	Длина/Расстояние
«анг»	Ангстрем	Длина/Расстояние
«пика»	Пика	Длина/Расстояние
«г»	Грамм	Вес/масса
«сг»	Слизень	Вес/масса
«фунт»	Фунт	Вес/масса
«у»	Атомный блок	Вес/масса
«озм»	Унция	Вес/масса
«год»	Год	Время
«день»	День	Время
«час»	Час	Время
«мин»	Минута	Время
«сек»	Второй	Время
«С» или «чел»	по Цельсию	Температура
«F» или «fah»		Температура
«К» или «кель»	Кельвин	Температура
«чайная ложка»	Чайная ложка	Объем жидкости
«ТБС»	Столовая ложка	Объем жидкости
«унция»	Жидкая унция	Объем жидкости
«чашка»	Чашка	Объем жидкости
«pt» или «us_pt»	Пинта США	Объем жидкости
«uk_pt»	Пинта Великобритании	Объем жидкости
«кварта»	Кварта	Объем жидкости
«галлон»	Галлон	Объем жидкости
«л» или «л»	Литр	Объем жидкости
«Н»	Ньютон	Сила
«дин» или «ди»	Дайн	Сила
«фунт-сила»	Фунт силы	Сила
«Па» или «п»	Паскаль	Давление
«атм»	Атмосфера	Давление
«мм рт. ст.»	мм Меркурий	Давление
«HP» или «ч»	Лошадиная сила	Мощность
«W» или «W»	Вт	Мощность
«Дж»	Джоуль	Энергия
«е»	Эрг	Энергия
«с»	Термодинамическая калория	Энергия
«кал»	IT калорий	Энергия
«эВ» или «ев»	Электрон-вольт	Энергия
«HPh» или «hh»	Лошадиная сила-час	Энергия
«белый» или «белый»	Ватт-час	Энергия
«флб»	Фунт-фунт	Энергия
«БТЕ» или «БТЕ»	БТЕ	Энергия
«Т»	Тесла	Магнетизм
«га»	Гаусс	Магнетизм

до_единиц

Единицы измерения, в которые вы хотите преобразовать число .