0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро подсчитать видимые или отфильтрованные ячейки только в Excel?

Эффективное копирование видимых / отфильтрованных строк в excel

Есть несколько очень больших наборов данных, с которыми я работаю (различные листы с 650K+ строк и множеством столбцов). Я пытаюсь написать код для быстрого копирования отфильтрованных данных с одного листа на новый пустой лист, но пока у меня ничего не получается.

Я могу включить код отдыха по требованию, но все, что он делает, это вычисляет диапазоны источника и назначения (srcRange и destRange). На их вычисление уходит очень мало времени. Эта строка занимает большую часть времени (4 минуты и 50 секунд, если быть точным):

Кроме того, я уже пробовала это:

Но он не работает должным образом, если есть фильтр.

Это медленная двухъядерная машина с 2 ГБ оперативной памяти, на которой работает excel 2010. Результаты, очевидно, будут отличаться на более быстрой машине.

2 ответа

  • Эффективное копирование векторов в C++11?

Эффективное копирование вектора в C++11? Это автоматически делается компилятором, когда вы говорите: v1 = v2; где v1 и v2 — std::vector. Или я должен использовать memcpy или что-то еще? Я говорю об огромных векторах, у нас около 10 000 элементов в каждом.

У меня есть Excel ws Monthly Data. Я хочу подсчитать строки, отфильтрованные автоматическим фильтром (xlFilterLastMonth), чтобы узнать, сколько строк было отфильтровано. With Sheets(Monthly Data) . Range(A1:BB & lastrow1). A Button имеет поле AutoFilter:=21, Criteria1:= xlFilterLeadMonth, Operator:= xlFilterDynamic MsgBox.

Попробуйте что-то подобное для работы с отфильтрованными диапазонами. Вы на правильном пути, метод . Это дорогой метод, и запись значений из диапазона в диапазон должна быть намного быстрее, но, как вы заметили, это не работает при фильтрации диапазона. Когда диапазон отфильтрован, вам нужно повторить . Области в . SpecialCells диапазон :

Читайте так же:
Как быстро преобразовать текстовый файл в файл Excel с разделителем?

Приведенные ниже данные были изменены для ваших целей, но не проверены:

Простое копирование (без использования фильтра)

Подробнее о моем комментарии

Если бы вы могли указать, сколько времени потребовалось, чтобы запустить систему, это было бы замечательно, мне очень интересно.

Я только что выполнил этот код в 53639 строк, и это заняло менее секунды

  • Копирование строк в Delphi в Excel

Приложение Delphi, работающее с таблицей Excel, находится в Токио. У меня есть исходный рабочий лист . называемый RawData. Мне нужно скопировать определенные строки (на основе фильтра) на другой лист в той же книге (называется ActivitySheet). Пока что я могу заставить это работать.

Можно ли заставить Excel выбирать заданное количество отфильтрованных строк на основе текущего столбца (возможно, с помощью VBA)? Текущие рабочие процессы регулярно требуют, чтобы мы выбирали 25 или 100 отфильтрованных строк активного столбца за раз. Макросы, с которыми я сталкивался до сих пор.

Похожие вопросы:

Я нахожу сумму отфильтрованных значений, игнорируя ошибки #DIV/0! Чтобы объединить формулы: =SUBTOTAL(9,$I$2:$I$349) и =SUMIF($I$2:$I$349), *>#DIV/0! Получить.

Существует метод для проверки количества строк в объекте listobject: loSättOmdömen.listRows. Count Но есть ли способ узнать, сколько строк видно в объекте listobject?

У меня есть пользовательская формула в MS Excel, которая выполняет определенные вычисления. Я хочу запустить эту формулу только для тех строк, которые видны после выполнения некоторых фильтров. Я читал об этом.

Эффективное копирование векторов в C++11? Это делается автоматически компилятором, когда вы говорите: v1 = v2; где v1 и v2 — std::vector. Или я должен использовать memcpy или что-то еще? Я говорю.

У меня есть Excel ws Monthly Data. Это автоматическая фильтрация (xlFilterLastMonth) и подсчет количества отфильтрованных строк. With Sheets(Monthly Data) . Range(A1:BB &.

Читайте так же:
Как автоматически расширить панель формул в Excel?

В Delphi Tokyo я разработал программу, которая интегрирует электронную таблицу с приложением Delphi. У меня есть исходный лист . под названием RawData. Требуется скопировать определенные строки (на основе).

Можно ли заставить Excel выбирать заданное количество отфильтрованных строк на основе текущего столбца (возможно, с помощью VBA)? В настоящее время мы регулярно выбираем 25 или 100 элементов.

Я хочу написать макрос для копирования форматирования только видимых (т.е. не скрытых, не отфильтрованных) ячеек выделения одного листа в только видимые ячейки выделения другого листа. Мой VBA.

Мне нужно скопировать огромное количество данных с помощью макроса VBA с одной вкладки на другую. При этом мне необходимо вставить значения и форматы. В данном случае копируются только значения.

Как посчитать количество видимых строк в Excel

С помощью функции СУММЕСЛИ можно подсчитать количество видимых элементов в отфильтрованном списке. Эта функция автоматически игнорирует все строки, скрытые фильтром.

Количество видимых строк в отфильтрованном списке

И ТОГИ. Эта функция может выполнять такие вычисления, как ИТОГО, СУММА, МАКС, МИН и многие другие.

Что такое ПРОМЕЖУТОЧНЫЕ. Особенно интересной и полезной особенностью TOTALS является то, что он игнорирует элементы, которые не видны в отфильтрованном списке или таблице. Помимо того, что он показывает количество видимых элементов в списке, его можно использовать для отображения промежуточных итогов видимых строк.

Например, чтобы подсчитать количество непустых строк, видимых при активном фильтре, воспользуйтесь:

Возможно ручное скрытие линий (например, правая кнопка мыши, Скрыть), вместо использования автоматического фильтра, используйте эту версию:

Только с критериями

=СУММПРОИЗВ((диапазон=критерий)*( ПРОМЕЖУТОЧНЫЕ.ИТОГИ (3; СМЕЩ (диапазон; ЧСТРОК;0;1))))

Чтобы вычислить видимые строки только с критериями, можно использовать довольно сложную формулу, основанную на СУММЕ, ИТОГАХ и СУММЕ.

T OTALS. TOTALS позволяет пользователям легко вычислять суммы и счетчики для скрытых и не скрытых строк. Однако он не способен работать с критериями (т.е. такими, как ИТОГИ или СУММЫ).

Читайте так же:
Как быстро перейти к первому / последнему / конкретному листу в Excel?

Количество видимых строк с критерием

S UMMPROIZE в сочетании с TOTALS является решением. Использование функции TOTALS (SMESH) и критериев. В соответствии с примером, показанным в C12:

=СУММПРОИЗВ ((C5:C8 = С10) * (ПРОМЕЖУТОЧНЫЕ.ИТОГИ (103;СМЕЩ(C5;СТРОКА(C5:C8) — МИН(СТРОКА(C5:C8));0))))

Суть этой формулы вычисление массива внутри СУММПРОИЗВ. Первый массив применяет критерии, а второй массив обрабатывает «проблему видимости».

Критерии применяются с частью формулы:

В результате массив формируется следующим образом:

Где ИСТИНА означает «отвечает критериям». Обратите внимание, что поскольку мы используем умножение (*) внутри первого (и только) массива, значения ИСТИНА/ЛОЖЬ будут автоматически преобразованы:

Для учета видимости используется фильтр PROMOTE.TOTALS

ПРОМЕЖУТОЧНЫЕ.ИТОГИ может исключить скрытые строки в различных вычислениях, поэтому мы можем использовать ее в этом случае, создав «фильтр», чтобы исключить скрытые строки внутри СУММПРОИЗВ. Проблема, однако, в том, что ПРОМЕЖУТОЧНЫЕ.ИТОГИ рассчитывает единственное число, в то время как нам нужен массив, чтобы использовать его успешно внутри СУММПРОИЗВ.

С МЕСЬ, которая подает ПРОМОУТЕР — вот в чем фокус. TOTALS по одной ссылке на строку, так что смещение будет вычислять один результат для каждой строки.

В дополнение к этому трюку нам нужно предоставить массив SMESH, содержащий номер для каждой строки, начиная с нуля. Мы делаем это с помощью:

Что создаст массив, подобный этому:

Таким образом, вторая матрица, которая касается видимости с PROMOTE.TOTALS, формируется следующим образом:

= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ (C5;СТРОКА(C5: C8) — МИН(СТРОКА(C5: C8)); 0))

Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Функция AML суммирует все ячейки в диапазоне, независимо от того, скрыты они или нет. Вы хотите суммировать только видимые строки.

Решение: Вместо функции TOTAL.TOTAL можно использовать функцию TOTAL. Формула будет немного отличаться в зависимости от того, как скрыты строки. После выделения строк, щелчка правой кнопкой мыши и выбора пункта скрыть из контекстного меню можно использовать команду: =Interval.TOTALS(109; диапазон) (рис. 1). Использование TOTALS.TOTALS для этой цели довольно необычно. В общем, эта функция нужна для того, чтобы Excel мог игнорировать другие промежуточные итоги в диапазоне.

Читайте так же:
Как быстро удалить каждую вторую строку в Excel?

Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

Рисунок 1. Серия из 100 в первом аргументе функции TOTALS.TOTALS используется для обработки видимых линий.

Скачайте заметку в формате Word или pdf и примеры в формате Excel.

ИТОГИ. TOTALS может выполнять 11 операций. Первый аргумент функции задает следующие операции: (1) DC VALUE, (2) COUNT, (3) COUNT, (4) MAX, (5) MIN, (6) PRODUCT, (7) STANDARD CLONE, (8) STANDARD CLONE, (9) SUM, (10) DISP, (11) DISP. Сложение сотен выполняет те же операции, но только над видимыми ячейками. Например, 104 найдет максимум среди видимых ячеек. Под видимым мы подразумеваем не видимый на экране (например, 120 строк не поместятся на экране), и не скрытый, с помощью команды Hide.

В ячейке E566 (см. рис. 1) используется формула =TOTALS. TOTALS(109;E2:E564). Excel суммирует только видимые (не скрытые) ячейки в диапазоне, а именно E2;E30;E72;E78;E564.

Функция СУММЕСЛИ может применяться к вертикальным наборам данных. Она не предназначена для горизонтальных наборов данных. Другими словами, если вы определяете промежуточные итоги для горизонтального набора данных, используя 101 или выше в качестве константы номера функции. TOTALS(109;C2:F2) Рисунок 2), скрытие столбца не повлияет на результат.

Рис. 2. Формула не игнорирует ячейки в скрытых столбцах

Рисунок 2 — Формула не учитывает ячейки в скрытых колонках

Дополнительная информация: В поведении функции TOTALS.TOTALS есть необычное исключение. Если строки были скрыты одной из команд фильтрации (расширенный фильтр, автофильтр или фильтр), Excel суммирует только видимые строки, даже в варианте TOTALS.TOTALS(9;диапазон). Нет необходимости использовать версию 109 (Рисунок 3). Здесь фильтр используется для поиска записей Chevron.

Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра

Рис. 3. Аргумент 9 является достаточным, если строки скрыты в результате применения фильтра

Почему я упоминаю об этой странности? Клавиатурная комбинация для суммирования строк, видимых после фильтрации, малоизвестна. Попробуйте выполнить следующие действия:

  1. Выберите любую ячейку в наборе данных.
  2. Перейдите в меню ДАТА -> Фильтр (или нажмите Alt+Y, а затем нажмите F, не отпуская Alt, или нажмите Ctrl+Shift+L). Excel добавляет фильтр (выпадающее меню) ко всем заголовкам столбцов.
  3. Откройте одно из выпадающих меню, например, Клиент. Снимите флажок Выбрать всех, затем выберите одного клиента. В нашем примере это компания Chevron.
  4. Выберите ячейки непосредственно под отфильтрованными данными. В нашем примере это ячейки E565:H565.
  5. Нажмите Alt+= или щелкните по значку Auto Total (меню DOM). Вместо функции СУММЕСЛИ Excel использует функцию TOTAL.TOTAL(9;диапазон), которая суммирует только строки, выбранные фильтром (см. Рисунок 3).
Читайте так же:
Динамическое выделение точки данных на диаграмме Excel

Подробнее см. раздел «Сравнение массивов и селектов по одному или нескольким условиям»; раздел «Функция AGREGATE». Сравнение массивов и выборок по нескольким условиям; раздел Функция AGREGATE). Кроме того, у нее больше вариантов того, что исключать и какие строки обрабатывать в своем «репертуаре». Ее главное преимущество — способность обрабатывать ошибки (например, #DEL/0!). Эта функция также не применима для суммирования видимых столбцов.

Резюме: Вы можете использовать функцию СУММЕСЛИ. Чтобы игнорировать скрытые строки, используйте функцию СУММЕСЛИ.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector