Использование констант массива в формулах массива
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
При вводе формулы массивачаще всего используется диапазон ячеек на вашем компьютере, но это не нужно. Вы также можете использовать константымассива , значения, которые вы просто вводите в панели формул внутри скобок: <>. После этого вы сможете назвать константу, чтобы использовать ее еще раз.
Константы можно использовать как в формулах массива, так и в самих себе.
- В формуле массива введите открываемую скобку, нужные значения и закрываюю скобку. Пример: =СУММ(A1:E1*) Константа находится внутри скобок (<)>, и да, вы действительно введите эти скобки вручную.
- Введите оставшуюся часть формулы и нажмите CTRL+SHIFT+ВВОД. Формула будет выглядеть так: <=СУММ(A1:E1*)>и результаты будут выглядеть так:
Формула умножает A1 на 1, B1 на 2 и т. д., что не нужно помещает 1,2,3,4,5 в ячейки на этом сайте.
Ввод значений в столбец с помощью константы
Чтобы ввести значения в одном столбце, например 3 ячейки в столбце C, необходимо:
- Выделите нужные ячейки.
- Введите знак равно и константу. Значения в константах разделяются запятой, а не запятой. Если вы вводите текст, заведите его двойными кавычками. Например: =
- Нажмите клавиши CTRL+SHIFT+ВВОД. Константа выглядит так: В данном случае это одномерная вертикальная константа.
Ввод значений в строке с помощью константы
Чтобы быстро ввести значения в одной строке, например ячейки F1, G1 и H1, вы можете:
- Выделите нужные ячейки.
- Введите знак равно и константы, но в этот раз значения разделяйте запятой, а не запятой. Например: =
- Нажмите CTRL+SHIFT+ВВОД, и константа будет выглядеть так: Это одномерная горизонтальная константа.
Использование константы для ввода значений в нескольких столбцах и строках
- Вы выберите нужные ячейки. Убедитесь, что количество строк и столбцов, которые вы выбрали, соответствует количеству значений в константе. Например, если константа будет записывать данные в четыре столбца и три строки, выберите столько столбцов и строк.
- Введите знак равно и константу. В этом случае разделять значения в каждой строке запятой и использовать запятую в конце каждой строки. Например: =
- Нажмите CTRL+SHIFT+ВВОД и: Это двумерная константа, которая заполняет столбцы и строки. Если вам интересно, вы не можете создать трехмерную константу, то есть не сможете вложенность константы в другую.
Использование константы в формуле
Теперь, когда вы знакомы с константами массива, вот рабочий пример.
- В любой пустой ячейке введите (или скопируйте и введите) эту формулу и нажмите CTRL+SHIFT+ВВОД: =СУММ(A1:E1*) В ячейке A3 появится значение 85. Что произошло? Вы перемножили значение в ячейке A1 на 1, значение в ячейке B2 на 2 и так далее, после чего функция СУММ добавила эти результаты. Можно также ввести формулу =СУММ(A1*1;B1*2;C1*3;D1*4;E1*5)
При этом можно ввести оба набора значений в качестве констант массива:
Чтобы сделать это, скопируйте формулу, выйдите из пустой ячейки и введите формулу в формулу и нажмите CTRL+SHIFT+ВВОД. Вы увидите такой же результат.
Примечания: Если константы не работают, наймите их.
- Убедитесь, что значения следует разделять с помощью соответствующих символов. Если опустить запятую или запятую либо поместить ее в неправильное место, константа массива может выглядеть неправильно или вы увидите предупреждение.
- Возможно, вы выбрали диапазон ячеек, не совпадающие с числом элементов в константе. Например, если выбрать столбец из шести ячеек для использования с константой из пяти ячеек, в пустой ячейке появится #N/Д. Если не выбрано достаточно ячеек, Excel опустить значения, не соответствующие ячейке.
- Дополнительные информацию о формулах массива:
- Ввод формулы массива
- Расширение диапазона формулы массива
- Удаление формулы массива
- Правила изменения формул массива
Присвоение имени константе массива
При использовании константы массива в формулемассива вы можете дать ей имя, а затем использовать ее повторно.
- Щелкните Формулы >имя.
- В поле Имя введите имя константы.
- В поле Ссылки введите константу. Например, можно использовать =. Диалоговое окно должно выглядеть так:
- Нажмите кнопку ОК.
- Выберите на нем ячейки, которые будут содержать константы.
- Введите в панели формул знак равно и имя константы, например =Квартал1.
- Нажмите ввод, если у вас есть текущая Microsoft 365 подписка. В противном случае нажмите CTRL+SHIFT+ВВОД.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о константах массива в формулах массива см. в разделе Именовка констант массива в статье Рекомендации и примеры формул массива.
Вот как динамический массив будет выглядеть в Microsoft 365:
- При использовании именованной константы в качестве формулы массива не забудьте ввести знак равенства. Если это не так, Excel массив интерпретируется как строка текста, и вы увидите сообщение об ошибке.
- В константах можно использовать числа, текст, логические значения (например, ИСТИНА и ЛОЖЬ) и значения ошибок (например, #N/A). Кроме того, можно использовать числа в десятичных, десятичных и научных форматах. При включаемом тексте его необходимо окружить двойными кавычками (««).
- Константы массива не могут содержать другие массивы, формулы и функции. Другими словами, они могут содержать только текст, числа или символы, разделенные запятой или запятой. Excel при вводе константы, например или . Кроме того, числа не могут содержать знаки процента, знаки доллара, запятые или скобки.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
22. Округление данных в Excel
При компиляции данных для отчета или презентации PowerPoint округление сумм в Excel часто вызывает проблемы. Во многих случаях требуется, чтобы округленные итоговые значения совпадали с итоговыми значениями округленных слагаемых, но этого сложно достичь. Для примера рассмотрим следующую таблицу:
Если значения округляются до целых чисел с помощью функции форматирования ячеек Excel, результатом будет следующая таблица. Итоговые значения, вычисленные «неправильно», выделены полужирным шрифтом:
Аналогично при использовании стандартных функций округления итоговые значения округленных значений вычисляются правильно, однако ошибки округления накапливаются, из-за чего результаты часто существенно отличаются от фактических сумм исходных значений. В следующей таблице показан результат =ROUND(x,0) примера выше. Суммы, которые отличаются от исходного значения на 1 и более, выделены полужирным шрифтом:
Используя округление think-cell, можно получить согласованные округленные суммы с минимальными изменениями. Хотя большинство значений округляются до ближайшего целого числа, некоторые значения округляются в противоположном направлении, что гарантирует правильное вычисления без накопления ошибки округления. Так как существует много способов получения правильных округленных сумм за счет изменения значений, think-cell выбирает решение, для которого требуется изменить минимальное число значений и минимальное отклонение от точных значений. Например, округление 10,5 до 10 будет предпочтительнее, чем округление 3,7 до 3. В следующей таблице показано оптимальное решение для примера выше, при этом измененные значения выделены полужирным шрифтом:
Чтобы получить такой же результат в собственных вычислениях, просто выберите соответствующий диапазон ячеек Excel. Затем нажмите кнопку
на вкладке Формулы и, при необходимости, измените точность округления, используя раскрывающийся список на панели инструментов.
22.1 Использование округления think-cell
Округление think-cell полностью интегрируется с Microsoft Excel и предоставляет набор функций, которые похожи на стандартные функции округления Excel. Вы легко можете применять эти функции к собственным данным, используя кнопки на панели инструментов на вкладке Формулы в think-cell.
22.1.1 Параметры округления
Как и функции Excel, функции округления think-cell принимают два параметра.
x Значение, которое необходимо округлить. Это может быть константа, формула или ссылка на другую ячейку. n Точность округления. Значение этого параметра зависит от используемой функции. Параметры функций think-cell эквивалентны параметрам соответствующих функций Excel. Примеры см. в таблице ниже.
Округление think-cell позволяет округлять значения не только до целых чисел, но и до любых кратных чисел. Например, если вы хотите представить данные в последовательности 5-10-15. просто округляйте значения до чисел, кратных пяти. Используя раскрывающееся меню на панели инструментов think-cell, просто введите или выберите нужную точность округления. Затем think-cell выберет требуемые функцию и параметры. В следующей таблице представлен ряд примеров округления определенных значений x с использованием панели инструментов вместе с соответствующим параметром n .
Если значения отображаются не так, как вы ожидаете, убедитесь, что в параметрах формата ячейки Excel выбрано значение Общий, а столбцы достаточно широкие для отображения всех знаков после запятой.
Для получения оптимальных результатов и минимального отклонения от базовых значений, следует использовать TCROUND , когда это возможно. Используйте более ограничительные функции TCROUNDDOWN , TCROUNDUP или TCROUNDNEAR , только если это необходимо.
Внимание! Не следует использовать недетерминированные функции, такие как RAND() , с какими-либо формулами TCROUND . Если функции возвращают разные значения при каждом вычислении, округление think-cell будет совершать ошибки при вычислении значений.
22.1.2 Макет вычисления
Прямоугольный макет примера выше приведен исключительно для демонстрации. Вы можете использовать функции TCROUND , чтобы настроить отображение произвольных сумм на листе Excel. Трехмерные ссылки Excel на другие листы и ссылки на другие файлы также работают.
22.1.3 Размещение функций TCROUND
Так как функции TCROUND предназначены для управления выходными данными ячейки, они должны быть крайними функциями:
Неправильно: = TCROUND (A1, 1)+ TCROUND ( SUM (B1:E1), 1 ) Правильно: = TCROUND ( A1+ SUM (B1:E1), 1 ) Неправильно: =3* TCROUNDDOWN (A1, 1) Правильно: = TCROUNDDOWN (3*A1, 1) Если вы введете что-то в строках неправильных примеров, think-cell уведомит вас о значении ошибки Excel #VALUE! .
22.2 Ограничения округления think-cell
Округление think-cell всегда ищет решение для произвольных сумм с промежуточными итогами и итоговыми значениями. Округление think-cell также предоставляет подходящие решения для других вычислений, использующих умножение и числовые функции. Однако в математических целях существование согласованного округляемого решения не может гарантироваться, если используются операторы, отличные от +, — и SUM .
22.2.1 Умножение на константу
Во многих случаях округление think-cell дает хорошие результаты, если используется умножение на константу, то есть если хотя бы один из коэффициентов основан на результате другой функции TCROUND . Рассмотрим следующий пример:
Точный результат вычисления для ячейки C1: 3×1,3+1,4=5,3. Чтобы получить этот результат, можно округлить значение 1,4 до 2:
Однако округление think-cell может только изменить значение, округлив его до большего или меньшего значения. Большее отклонение от исходных значений не поддерживается. Поэтому для определенных сочетаний входных значений невозможно найти согласованное решение для округления. В этом случае функция TCROUND предоставляет значение ошибки Excel #NUM! . В следующем примере показана нерешаемая задача:
Точный результат вычисления для ячейки C1: 6×1,3+1,4=9,2. Округление ячеек A1 и B1 даст следующий результат: 6×1+2=8 или 6×2+1=13. Фактический результат нельзя округлить до 8 или 13, а выходные данные округления think-cell будут выглядеть следующим образом:
Примечание. Функция AVERAGE Excel интерпретируется округлением think-cell как комбинация суммирования и умножения на константу. Кроме того, сумма, в которой одно слагаемое используется несколько раз, математически эквивалентно умножению на константу, и существование решения не гарантируется.
22.2.2 Общее умножение и другие функции
Если функции TCROUND используются для соответствующих ячеек и промежуточные результаты связаны только операторами +, -, SUM и AVERAGE , слагаемые и (промежуточные) итоговые значения объединяются в одной задаче округления. В этих случаях округление think-cell найдет решение, которое обеспечивает согласованность для всех связанных ячеек, если такое решение существует.
Так как TCROUND — обычная функция Excel, ее можно объединить с любыми функциями и операторами. Но если вы используете функции, отличные от указанных выше, для соединения результатов операторов TCROUND , округление think-cell не может объединить компоненты в одной общей задаче. Вместо этого компоненты формулы будут считаться отдельными задачами, каждая из которых будет решаться независимо друг от друга. Затем результаты будут использоваться как входные данные для других формул.
Во многих случаях результаты округления think-cell по-прежнему будут рациональными. Однако в некоторых случаях использование операторов, отличных от +, -, SUM и AVERAGE , приводит к получению округленных результатов, которые существенно отличаются от вычисления без округления. Рассмотрим следующий пример:
Точный результат вычисления для ячейки C1 в этом случае: 8,6×1,7=14,62. Так как ячейки A1 и B1 связаны умножением, округление think-cell не сможет объединить формулы из этих ячеек в общую задачу. Вместо этого после обнаружения ячейки A1 как допустимых входных данных, ячейка B1 будет вычислена независимо, а результат будет считаться константой для оставшейся задачи. Так как других ограничений нет, значение 1,7 из ячейки B1 округляется до ближайшего целого числа (2).
«Точный» результат вычисления для ячейки C1 в этом случае: 8,6×2=17,2. Теперь эту задачу попытается решить функция округления think-cell. Существует согласованное решение, для которого необходимо округлить 17,2 до 18. Результат будет выглядеть следующим образом:
Обратите внимание, что округленное значение в ячейке C1, которое равно 18, сильно отличается от исходного значения 14,62.
22.3 Устранение неполадок с формулами TCROUND
При использовании округления think-cell вы можете столкнуться с двумя ошибками: #VALUE! и #NUM! .
22.3.1 #VALUE!
Ошибка #VALUE! указывает на синтаксические проблемы, такие как неправильно введенные формулы или недопустимые параметры. Например, второй параметр TCROUND должен быть целым числом. Кроме того, уделите внимание правильному использованию разделителей. Например, в международной версии Excel формула выглядит так: =TCROUND(1.7, 0) , а в немецкой версии Excel ее следует записать следующим образом: =TCROUND(1,7; 0) .
Еще одна ошибка, связанная с округлением think-cell, — это размещение вызова функции TCROUND : нельзя использовать функцию TCROUND с другой формулой. Убедитесь, что TCROUND — это крайняя функция в формуле ячейки. (См. раздел Размещение функций TCROUND.)
22.3.2 #NUM!
Ошибка #NUM! возникает из-за числовых проблем. Если результат функции TCROUND равен #NUM! , это значит, что задача, определенная данным набором формул, не имеет математического решения. (См. раздел Ограничения округления в think-cell.)
Если формулы, окруженные функциями TCROUND , содержат только операторы +, — и SUM и для всех операторов TCROUND используется одинаковая точность (второй параметр), решение будет гарантированно существовать и будет найдено округлением think-cell. Однако в следующих случаях существование согласованного округленного решения не гарантируется.
- Формулы содержат другие операции, такие как умножение или числовые функции. Кроме того, суммы, в которых одно слагаемое используется несколько раз, математически эквивалентны умножению.
- Вы должны использовать разные точности во втором параметре функции TCROUND .
- Вы часто используете функции TCROUNDDOWN , TCROUNDUP и TCROUNDNEAR .
Вы можете заново сформулировать задачу, чтобы получить согласованное решение. Попробуйте следующее:
- Используйте более высокую точность для некоторых или всех операторов TCROUND .
- Не используйте TCROUND с умножением или числовыми функциями, отличными от +, — и SUM .
- Используйте одинаковую точность (второй параметр) для всех операторов TCROUND .
- Используйте TCROUND вместо функций TCROUNDDOWN , TCROUNDUP и TCROUNDNEAR , где это возможно.
Ищем оптимальное решение задачи с неизвестными параметрами в Excel
«Поиск решений» — функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил. Рассказываем, как освоить функцию поиска решений.
Основные параметры поиска решений
Найти решение задачи можно тремя способами. Во-первых, вручную перебирать параметры, пока не найдется оптимальное соотношение. Во-вторых, составить уравнение с большим количеством неизвестных. В-третьих, вбить данные в Excel и использовать «Поиск решений». Последний способ самый быстрый и покажет максимально точное решение, если знать, как использовать функцию.
Итак, мы решаем задачу с помощью поиска решений в Excel и начинаем с математической модели. В ней четыре типа данных: константы, изменяемые ячейки, целевая функция и ограничения. К поиску решения вернемся чуть позже, а сейчас разберемся, что входит в каждый из этих типов:
Константы — исходная информация. К ней относится удельная маржинальная прибыль, стоимость каждой перевозки, нормы расхода товарно-материальных ценностей. В нашем случае — производительность работников, их оплата и норма в 1000 изделий. Также константа отражает ограничения и условия математической модели: например, только неотрицательные или целые значения. Мы вносим константы в таблицу цифрами или с помощью элементарных формул (СУММ, СРЗНАЧ).
Изменяемые ячейки — переменные, которые в итоге нужно найти. В задаче это распределение 1000 изделий между работниками с минимальными затратами. В разных случаях бывает одна изменяемая ячейка или диапазон. При заполнении функции «Поиск решений» важно оставить ячейки пустыми — программа сама найдет значения.
Целевая функция — результирующий показатель, для которого Excel подбирает наилучшие показатели. Чтобы программа понимала, какие данные наилучшие, мы задаем функцию в виде формулы. Эту формулу мы отображаем в отдельной ячейке. Результирующий показатель может принимать максимальное или минимальное значения, а также быть конкретным числом.
Ограничения — условия, которые необходимо учесть при оптимизации функции, называющейся целевой. К ним относятся размеры инвестирования, срок реализации проекта или объем покупательского спроса. В нашем случае — количество дней и число работников.
Пример использования поиска решений
Теперь перейдем к самой функции.
1) Чтобы включить «Поиск решений», выполните следующие шаги:
- нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
- в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
- в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.
2) Теперь упорядочим данные в виде таблицы, отражающей связи между ячейками. Советуем использовать цветовые обозначения: на примере красным выделена целевая функция, бежевым — ограничения, а желтым — изменяемые ячейки.
Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата труда за 1 изделие» умножить на «Число заготовок, передаваемых в работу». Для того, чтобы узнать «Время на выполнение заказа», нужно «Число заготовок, передаваемых в работу» разделить на «Производительность».
3) Выделите целевую ячейку, которая должна показать максимум, минимум или определенное значение при заданных условиях. Для этого на панели нажмите «Данные» и выберете функцию «Поиск решений» (обычно она в верхнем правом углу).
4) Заполните параметры «Поиска решений» и нажмите «Найти решение».
Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.
В нашем примере следующие ограничения:
- общее количество изделий 1000 штук ($D$13 = $D$3);
- число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0);
- количество дней меньше либо равно 30 ($F$9:$F$12 < = $D$6, либо как в примере в ячейке F13 задать функцию МАКС(F9:F12) и поставить ограничение $F$13 < = $D$6).
5) В конце проверьте полученные данные на соответствие заданному целевому значению. Если что-то не сходится — нужно пересмотреть исходные данные, введенные формулы и ограничения.
Хотите научиться решать задачи в Excel, как это делают в компаниях-лидерах? Приходите на наш онлайн-курс, на котором вы освоите этот инструмент на уровне профи. Вашими преподавателями будут эксперты-практики, а после обучения вы сможете дополнить резюме весомой строчкой. Регистрируйтесь!
Теги
Получите карьерную поддержку
Если вы не знаете, с чего начать карьеру, зашли в тупик или считаете, что совершили какие-то ошибки, спросите совета у специалистов. Заполните заявку и консультанты Changellenge >> окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.
Подписаться на карьерную рассылку
Подписывайтесь на рассылку и получайте карьерные советы — от выбора индустрии и компании до лайфхаков по самоорганизации и развитию коммуникативных навыков.
Коммуникационный дизайнер
Разработчик технологий
Стажер в отдел права
Стажер-маркетолог
Стажер группы ценообразования
Менеджер по повышению эффективности
Стажер в команду по охране труда
Стажер продюсер