Как заполнить большой ряд (более 10 000 строк) в Microsoft Excel без перетаскивания или выбора ячеек?

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

обычно я просто выбираю ячейки с помощью клавиатуры, а затем нажимаю Ctrl+D заполнить ячейки. Однако теперь мне приходится делать это неоднократно, и трудно продолжать выбирать 5000 строк в каждом файле. Есть ли более простой способ, когда я можно просто указать диапазон с помощью клавиатуры? Возможно, даже есть макрос, так как все файлы имеют фиксированную длину

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

5
задан phuclv
источник

3 ответов

предполагая, что данные в колонке A и формула в столбце B:

  1. введите формулу в B1
  2. копировать ячейку B1
  3. перейдите с помощью клавиш со стрелками в любую ячейку в столбце A
  4. пресс Ctrl+Стрелка Вниз
  5. пресс Стрелка Вправо (теперь вы должны быть в пустой ячейке в нижней части столбца B)
  6. пресс Ctrl+Shift+Стрелка Вверх
  7. вставить (Ctrl+V)
1
отвечен cybernetic.nomad 2018-11-09 09:46:01
источник

продублирую:

однако вам даже не нужно заполнять формулу для всех ячеек. Было бы лучше использовать формулу массива с несколькими результатами. Просто введите формулу, как обычно, но заменяет ячейки диапазона (например,B1 С B1:B10000. Это немного упрощено, но это идея, читайте ниже для более подробной информации), а затем пресс Ctrl+Shift+введите. Формула после этого будет применена сразу ко всем клетки в таблице, очень мало нажатий требуется

зачем использовать формулы массива?

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

  • подсчитать количество символов, содержащихся в диапазоне ячеек.
  • суммировать только числа, удовлетворяющие определенным условиям, например наименьшие значения в диапазоне или числа, находящиеся между верхней и нижней границей.
  • просуммировать каждое N-е значение в диапазоне значений.

Формулы массива также предлагают эти преимущества:

  • последовательность: если щелкнуть любую ячейку из E2 вниз, отобразится та же формула. Эта последовательность может помочь обеспечить большую точность.
  • безопасность: нельзя перезаписать компонент формулы массива из нескольких ячеек. Например, щелкните ячейку E3 и нажмите клавишу Delete. Необходимо либо выбрать весь диапазон ячеек (от E2 до E11) и изменить формулу для всего массива, либо оставить массив как есть. В качестве дополнительной меры безопасности, вы должны нажать Ctrl+Shift+введите для подтверждения изменения формулы.
  • меньше размеры файлов: Часто вместо нескольких промежуточных формул можно использовать одну формулу массива. Например, книга использует одну формулу массива для вычисления результатов в столбце E. Если бы вы использовали стандартные формулы (например, =C2*D2, C3*D3, C4*D4...), вы бы использовали 11 различных формул для вычисления одних и тех же результатов.

рекомендации и примеры формул массива

Формула массива также быстрее, так как шаблон доступа уже называемый. Теперь вместо того, чтобы делать 11 различных вычислений по отдельности, их можно векторизовать и выполнять параллельно, используя несколько ядер и SIMD-блок в CPU

например, если вы хотите, чтобы ячейки в столбце D содержали произведение ячеек в B и C, а столбец E содержал сумму B и C, вместо использования D1 = B1*C1 и E1 = B1 + C1 и перетащите вниз вы просто положить ниже формулы в D1 и E1 соответственно и нажмите Ctrl+Shift+введите

=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, который вычисляет продукт в каждом столбце

Multi-result array formula

Формула массива является очень мощным инструментом. Однако используйте его с осторожностью. Каждый раз, когда вам нужно отредактировать его, вы не должны забывать нажимать Ctrl+Shift+введите

подробнее создать формулу массива

1
отвечен phuclv 2018-11-08 07:27:36
источник

пресс ALT+F11, чтобы открыть экран VBA. Этот код должен скопировать формулу, просто измените диапазон на правильные ячейки

Range("B1:B10000").FillDown

Edit

Или просто добавьте формулу с VBA

ActiveSheet.Range("B1:B10000").formula = "=A2+2"
0
отвечен M.Johnson 2018-11-07 02:56:51
источник