Главная » База

Excel на максималках: формула сама заполнит таблицу — забудьте навсегда о протяжке!

На чтение 5 мин Просмотров 3 Опубликовано Обновлено

An illustration featuring the Excel logo, function symbols, and a formula bar showing '=function()' against a green and blue abstract background.

Представьте: я ввожу одну формулу в ячейку A1 — и Excel сам в тот же миг строит сетку из сотни ячеек! Никакой протяжки, никаких танцев с $ и путаницы в формулах. Это — MAKEARRAY. Одна короткая формула, и у вас за секунду готов любой сложный динамический набор данных. Просто дайте Excel работать за вас!

Функция MAKEARRAY доступна в Excel для Microsoft 365, в браузерной версии и в новых мобильных приложениях.

В этой статье:
  1. Что такое функция MAKEARRAY и как она работает
  2. Всё проще, чем вы думаете
  3. Формула и параметры
  4. Пример: быстро строим таблицу умножения 10×10
  5. Пример 1: моментальный генератор случайных партий для контроля качества
  6. Автоматически заполняем таблицы случайными значениями
  7. Пример 2: создаём ценовую сетку для разных зон
  8. Используем внешние ссылки для супергибких сценариев
  9. Пример 3: модель прогноза роста на основе матрицы
  10. Прогнозируем развитие сложных бизнес-стратегий
  11. Подпишитесь на рассылку и получайте лайфхаки по функциям MAKEARRAY первыми!
  12. SEQUENCE или MAKEARRAY — что подойдёт именно вам?
  13. Как выбрать подходящую функцию для своей задачи

Что такое функция MAKEARRAY и как она работает

Всё проще, чем вы думаете

С MAKEARRAY вы просто задаёте размер будущей сетки и формулируете правило для вычисления значения в каждой ячейке (зависит от её позиции — строки и столбца). Excel сам повторит ваш алгоритм для каждой точки этой сетки.

Формула и параметры

Синтаксис функции MAKEARRAY включает следующие элементы:

где:

The lambda icon on the left, an Excel formula in the center, and a laptop with a blurred spreadsheet in the background.

Как создавать свои функции с помощью LAMBDA в Excel

Сократите громоздкие формулы и используйте их много раз.

Пример: быстро строим таблицу умножения 10×10

Оценить мощь MAKEARRAY проще всего на примере классической таблицы умножения:

A MAKEARRAY formula in Excel that produces a 10x10 calculation grid.

Excel сам расставляет значения: от строки 1, столбца 1 (1×1) до строки 10, столбца 10 (10×10). Так как MAKEARRAY — это динамический массив, результат “рассыпается” по всем ячейкам рядом, а формула выделяется синей рамкой при выборе.

Excel позволяет создавать сетки до 16 384 столбцов и 1 048 576 строк, но если вы строите огромный массив с тяжёлыми расчётами — программа может подтормаживать.

Пример 1: моментальный генератор случайных партий для контроля качества

Автоматически заполняем таблицы случайными значениями

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

Ситуация: Вы — руководитель проекта и регулярно готовите разные таблицы для проверки качества (оценки A, B или C). Забудьте о ручном вводе — теперь каждый раз при пересчёте листа получаете новую случайную партию!

Вот пример формулы:

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

A MAKEARRAY formula in Excel that creates a test grid of randomized quality control grades (A, B, or C).

Как работает эта схема:

Illustration of an Excel file with a formula and some alerts around it.

Почему в формулы Excel лучше не подставлять значения напрямую

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

Пример 2: создаём ценовую сетку для разных зон

Используем внешние ссылки для супергибких сценариев

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

Ситуация А: Вы проектируете рассадку гостей на мероприятии: чем ближе к сцене — тем дороже место. Задайте параметры: B1 — число рядов, B2 — мест в ряду, B3 — сколько первых рядов относятся к премиум-зоне, B4 — цена премиум (например, 12 000₽), B5 — обычная цена (например, 9 500₽).

В D1 впишите формулу:

Если номер ряда ≤ B3, появится премиум-цена (B4). Для остальных рядов — обычная стоимость (B5).

Ситуация Б: Захотели добавить ещё одну, самую дешёвую зону для последних рядов? Просто обновите параметры в колонке B — формула всё просчитает автоматически.

Для трёх ценовых уровней — используйте вложенные IF. Формула в D1 станет:

Так вы сможете быстро просчитывать любые комбинации цен.

Красиво выделить границы цен поможет условное форматирование (Цветовые шкалы). Но помните: Excel пока не позволяет использовать # для массивов в этом инструменте, поэтому сразу захватите нужный диапазон руками и примените форматирование.

Пример 3: модель прогноза роста на основе матрицы

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


Подпишитесь на рассылку и получайте лайфхаки по функциям MAKEARRAY первыми!

Ситуация: Я рассчитываю, как будут меняться 100 рублей на протяжении 12 месяцев по четырём стратегиям: Стандарт, Сложные проценты, Ускоренный рост и Максимальный рост, если базовая месячная ставка — 5%.

Названия месяцев и стратегий вписываются вручную — их формула не строит автоматически.

На пересечении месяца и стратегии (например B2) — вставляю формулу MAKEARRAY:

Вот как работает этот расчёт:

SEQUENCE или MAKEARRAY — что подойдёт именно вам?

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

SEQUENCE и MAKEARRAY делают разные вещи. Если нужен просто список чисел или дат — берите SEQUENCE. Если результат ячейки зависит от её координат в сетке — ваш выбор MAKEARRAY.

Что сравниваем:

SEQUENCE

MAKEARRAY

Принцип

Просто список (1, 2, 3…)

Любой расчёт по координатам

Размерность

Обычно линейно или простая таблица

Любой размер: строки × столбцы

Гибкость

Только шаг между числами

Любая логика на LAMBDA

Лучшее применение

Счётчики, даты

Карты цен, имитации, аналитику роста

An Excel spreadsheet in the background with the Excel logo in front.

6 функций, которые полностью взорвали Excel-подход

Динамические массивы — революция в работе с таблицами.

Теперь вы забыли про нудную протяжку: одна формула строит структуру сразу. Но MAKEARRAY — это только начало эпохи умных таблиц! Осваивайте и другие пять функций-итераторов и аккумуляторов, чтобы анализировать и обрабатывать данные на новом, профессиональном уровне. В комплексе эти LAMBDA-инструменты превращают Excel чуть ли не в язык программирования — и знания их реально отличают вас от большинства пользователей.

Microsoft 365 Персональный

В подписке Microsoft 365 вы получаете Word, Excel, PowerPoint сразу на 5 устройствах, целый терабайт OneDrive и ещё много фишек.

Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!

Премиум подписка — это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!

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

Также подписывайтесь на нас в:

Алекс Бежбакин
Оцените автора
Добавить комментарий