Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему0

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

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

Проблема: формулы динамического массива не работают в таблицах Excel

Формула динамического массива — это формула, результат которой распространяется из одной ячейки в другие ячейки. Более того, если результат формулы увеличивается или уменьшается в размере, массив делает то же самое.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему1

Например, введите:

= УНИКАЛЬНЫЙ (A2: A21)

в ячейку B2 и нажатие Enter возвращает все уникальные значения из ячеек A2–A21 в отдельных ячейках столбца B. Обратите внимание, что при выборе любой из ячеек, содержащих одно из этих уникальных значений, синяя линия напоминает вам, что это расширенный массив.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему2

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

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему3

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему4

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

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

Решение 1: Преобразовать таблицу в неформатированный диапазон

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

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему5

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

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему6

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

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

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

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему7

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

Решение 2: Используйте альтернативные функции

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

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

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

Создание динамического нумерованного списка

Функция динамического массива	ПОСЛЕДОВАТЕЛЬНОСТЬ (с СЧЁТЧИКОМ)
Что делает эта функция	Возвращает последовательность чисел
Альтернативная функция	РЯД

В этом примере введите:

=ПОСЛЕДОВАТЕЛЬНОСТЬ(СЧЁТA(B:B)-1,1,1)

в ячейку A2 подсчитывает количество непустых ячеек в столбце B, вычитает единицу для учета строки заголовка и создает один столбец последовательных чисел, начиная с 1. Более того, если данные добавляются или удаляются из столбца B, количество в столбце A автоматически обновляется.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему8

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему9

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

=СТРОКА()-СТРОКА(ТаблицаX[[#Заголовки],[Имя]])

Замените ТаблицаX с правильным названием таблицы и Имя с правильным названием столбца.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему10

Создание массива случайных чисел

Функция динамического массива	РАНДАРРАЙ
Что делает эта функция	Возвращает массив случайных чисел
Альтернативная функция	RANDBETWEEN

В этом неформатированном диапазоне введите:

= СЛУЧАЙНЫЙ РЕЖИМ (20,1,50,100; ИСТИНА)

в ячейку A2 возвращает случайный список целых чисел от 50 до 100 в ячейках A2–A21.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему11

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему12

Чтобы сделать то же самое в отформатированной таблице, в ячейке A2 введите:

= СЛУЧМЕЖДУ (50,100)

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

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему13

RANDARRAY и RANDBETWEEN — это изменчивые функции, то есть они пересчитываются каждый раз при внесении изменений в рабочий лист. Чтобы исправить случайные числа после их генерации, выберите все данные (Ctrl+A), скопируйте их (Ctrl+C) и нажмите Crl+Shift+V, чтобы вставить числа в качестве значений.

Разделение ячейки на отдельные столбцы

Функция динамического массива	ТЕКСПЛИТ
Что делает эта функция	Разбивает текст на строки или столбцы с использованием разделителей.
Альтернативные функции	ТЕКСТПЕРЕД и ТЕКСТПОСЛЕ

В этом примере TEXTSPLIT берет имя в ячейке A2 и разливает разделенную версию каждого имени по ячейкам B2 и C2, причем строка запятая-пробел выступает в качестве разделителя. Затем, выбрав ячейку B2 и дважды щелкнув маркер заполнения, можно применить динамическую формулу массива к оставшимся ячейкам в диапазоне.

=ТЕКСТРАЗДЕЛИТЬ(A2,», «)

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему14

При работе с таблицами Excel вместо использования этой динамической формулы массива можно использовать ТЕКСТПЕРЕД и ТЕКСТПОСЛЕ.

В ячейке B2 введите:

=ТЕКСТПЕРЕД([@Имя],»,»)

для извлечения текста перед запятой из столбца Имя.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему15

Затем в ячейке C2 введите:

=ТЕКСТПОСЛЕ([@Имя],» «)

для извлечения текста после пробела.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему16

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему17

Извлечение уникальных значений

Функция динамического массива	УНИКАЛЬНЫЙ
Что делает эта функция	Возвращает уникальные значения из диапазона
Альтернативные функции	ИНДЕКС, УНИКАЛЬНЫЙ и СТРОКА

В этом регулярном диапазоне эта УНИКАЛЬНАЯ формула перечисляет все уникальные значения в ячейках A2–A50.

=УНИКАЛЬНЫЙ(A2:.A50)

Обратите внимание на точку после двоеточия. Этот символ, известный в этом контексте как оператор trim ref, сообщает Excel о необходимости обрезать все пустые строки в конце результата, что предотвращает появление нуля в списке.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему18

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему19

Чтобы добиться аналогичного результата в таблице Excel, в ячейке B2 введите:

=ИНДЕКС(УНИКАЛЬНЫЙ($A$1:$A$50),СТРОКА(A2))

в котором

ПОКАЗАТЕЛЬ() возвращает значение,
УНИКАЛЬНЫЙ($A$1:$A$50), который является первым аргументом формулы ИНДЕКС, находит уникальные значения в ячейках A1–A50 и
СТРОКА(A2), который является вторым аргументом формулы ИНДЕКС, возвращает nуникальное значение, где n — номер строки активной ячейки.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему20

Однако, хотя это успешно находит все уникальные значения из исходных данных, таблица не расширяется и не сжимается автоматически. Чтобы учесть это, на ленте Table Design отметьте «Total Row» и разверните полученный раскрывающийся список внизу таблицы, чтобы изменить тип агрегации на «Count». Это подсчитывает уникальные значения в столбце таблицы.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему21

Затем в отдельной ячейке введите:

=СЧЁТA(УНИКАЛЬНЫЙ($A$2:.$A$50))

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

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему22

Теперь, если количество в итоговой строке таблицы не совпадает с количеством в ячейке COUNTA-UNIQUE, вы знаете, что вам нужно соответственно расширить или уменьшить размер таблицы.

Мне нравится использовать таблицы Excel, но хотелось бы, чтобы Microsoft исправила одну серьезную проблему23

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