У меня есть более 10000 строк данных, мне нужно сделать некоторые расчеты для одного столбца данных. Я сделал расчет в одной ячейке, и теперь нужно заполнить формулу для расчета и все ячейки.
Обычно я просто выбираю ячейки с помощью клавиатуры, а затем нажимаю Ctrl+, Dчтобы заполнить ячейки. Тем не менее, теперь я должен делать это неоднократно, и трудно продолжать выделять 5000 строк в каждом файле. Есть ли более простой способ, где я могу просто указать диапазон с помощью клавиатуры? Возможно, даже есть макрос, так как все файлы имеют фиксированную длину
Кроме того, поскольку я испытываю трудности при использовании мыши по медицинским причинам, и поэтому я не тестировал это конкретное решение, которое включает нажатие на очень маленькую ручку заполнения. Может быть, есть параллельное решение для этого с помощью клавиатуры или, может быть, я могу создать макрос, который будет автоматически нажимать на маркер заполнения выбранной ячейки - пожалуйста, сообщите, если есть способ.
Предполагая, что данные в столбце Aи формула в столбце B:
B1B1AB)Дубликаты:
Однако вам даже не нужно заполнять формулу для всех ячеек. Было бы лучше использовать формулу с несколькими результирующими массивами. Просто введите формулу, как обычно, но замените ячейки диапазоном (например, B1с помощью B1:B10000. Это немного упрощено, но это идея, читайте ниже для более подробной информации), а затем нажмите Ctrl+ Shift+Enter . Формула будет применена сразу ко всем ячейкам таблицы, требуется очень мало нажатий клавиш.
Зачем использовать формулы массива?
Если у вас есть опыт использования формул в Excel, вы знаете, что можете выполнять довольно сложные операции. Например, вы можете рассчитать общую стоимость кредита за любое заданное количество лет. Вы можете использовать формулы массива для выполнения сложных задач, таких как:
- Подсчитайте количество символов, которые содержатся в диапазоне ячеек.
- Суммируйте только числа, которые удовлетворяют определенным условиям, например, самые низкие значения в диапазоне или числа, которые находятся между верхней и нижней границами.
- Суммируйте каждое n-е значение в диапазоне значений.
Формулы массива также предлагают следующие преимущества:
- Согласованность: если вы щелкнете по любой ячейке от E2 вниз, вы увидите ту же формулу. Эта последовательность может помочь обеспечить большую точность.
- Безопасность: нельзя перезаписать компонент формулы массива из нескольких ячеек. Например, щелкните ячейку E3 и нажмите «Удалить». Вы должны либо выбрать весь диапазон ячеек (от E2 до E11) и изменить формулу для всего массива, либо оставить массив как есть. В качестве дополнительной меры безопасности вы должны нажать Ctrl+ Shift+, Enterчтобы подтвердить изменение формулы.
- Меньшие размеры файлов: часто вы можете использовать формулу одного массива вместо нескольких промежуточных формул. Например, рабочая книга использует одну формулу массива для вычисления результатов в столбце E. Если бы вы использовали стандартные формулы (такие как = C2 * D2, C3 * D3, C4 * D4 ...), вы бы использовали 11 различных формул для вычисления те же результаты.
Формула массива также быстрее, поскольку схема доступа уже известна. Теперь вместо 11 различных вычислений по отдельности их можно векторизовать и выполнять параллельно, используя несколько ядер и SIMD-модуль в ЦП.
Например, если вы хотите, чтобы ячейки в столбце D содержали произведение ячеек в B и C, а столбец E содержал сумму B и C, вместо использования D1 = B1*C1и E1 = B1 + C1и перетаскивания вы просто помещаете приведенные ниже формулы в D1 и E1 соответственно и нажимаете Ctrl+ Shift+Enter
=B:B*C:C =B:B + C:C В листе Google вам нужно использовать ARRAYFORMULAфункцию вместо ярлыка, как в Excel
=ARRAYFORMULA(B:B*C:C) =ARRAYFORMULA(B:B + C:C) После этого формула будет автоматически применена сразу ко всему столбцу, не перетаскивая ничего. X:Xотносится к столбцу X. Если вы хотите включить только от строки 3 до конечного использования X3:X. Или, если вы хотите рассчитать результат только для ячеек от X3 до X10003, используйте X3:X10003. Тот же самый синтаксис может использоваться для строк, то есть 1: 1 для строки 1. Вот другой пример из документации Excel, которая вычисляет продукт в каждом столбце
Формула массива является очень мощным инструментом. Однако используйте это с осторожностью. Каждый раз, когда вам нужно отредактировать его, вы не должны забывать нажимать Ctrl+ Shift+Enter
Для получения дополнительной информации читайте Создание формулы массива
Нажмите ALT+, F11чтобы открыть экран VBA. Этот код должен скопировать формулу, просто измените Range на правильные ячейки
Range("B1:B10000").FillDown Редактировать
Или просто добавить формулу с VBA
ActiveSheet.Range("B1:B10000").formula = "=A2+2"