Excel COUNTIF Медленный

Question

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

1. Создать столбец с = COUNTIF(A:A, A2)
2. Скопируйте формулу вниз по столбцу
3. Сохранить рабочий лист (зависает / требуется много лет, чтобы закончить) или скопировать столбец и вставить значения (зависает / требуется много лет, чтобы закончить)

Я сделал это в Excel 2010 и 2013 с одинаковыми результатами на двух разных ПК. На листе всего 172000 строк. Формула прекрасно работает, чтобы получить значения, но зависает, когда я пытаюсь сохранить или скопировать и вставить значения столбца. Без этого столбца рабочий лист ведет себя нормально. Я никогда не сталкивался с этим раньше.

Заранее спасибо!

Answer 1

Попробуйте следующее:

1. Отключить автоматические расчеты. (Вкладка "Лента формул", группа расчетов, раскрывающийся список "Параметры расчета", "Вручную")
2. Отключите "Пересчитать книгу перед сохранением" (Параметры, вкладка Формулы, раздел Параметры расчета)
3. Измените ссылку на столбец A на ссылку на динамический именованный диапазон.

Answer 2

Почему бы просто не применить фильтр вместо использования countif()?

1. Выберите ваши данные, перейдите в Data - Sort & Filter - Advanced чтобы разместить расширенный фильтр.
2. Фильтровать список, на месте
3. Выберите всю таблицу в качестве List range
4. Выберите столбец A в качестве Criteria range
5. Установите флажок только для уникальных записей

Answer 3

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

1. Выделите столбец A. Нажмите Вставить, Сводная таблица.
2. Выберите место назначения вашей сводной таблицы, затем нажмите OK.
3. В диалоговом окне "Поля сводной таблицы" (точно не знаю, как это выглядит в 2010 году; я использую 2013 год) перетащите заголовок столбца А в поле "Строки" и "Значения".
4. В моем экземпляре Excel значения для заголовка по умолчанию для подсчета. Если это не для вас, щелкните левой кнопкой мыши строку в заголовке окна «сумма / количество / среднее /??... заголовка», а затем нажмите "Настройки поля значения". Затем в разделе "Суммировать поле значения по" выберите "Количество".

Вы можете включить автоматический расчет. Сводные таблицы должны быть обновлены явно. Попробуйте и посмотрите, работает ли он для вас.

Answer 4

Я столкнулся с той же самой проблемой и нашел ее в этой теме. Моя формула в столбце B была следующей:= COUNTIF($ A1 $:A, A2)

так что счетчик для каждой новой скопированной строки всегда будет начинаться с начала набора данных.

Счетчик занимает некоторое время (в моем листе +100 тыс. Строк), но он сделал свою работу. Мой Excel вылетает, если я хочу скопировать как значения, и у меня быстрый компьютер с 16 ГБ ОЗУ.

Тогда я просто обнаружил самое простое решение! Добавьте столбец рядом со столбцом формулы (например, столбец C) и создайте простую формулу, ссылающуюся на столбец B, например = B1 * 1 или = VALUE (B1), и скопируйте ее до конца. После этого вы просто выбираете этот столбец простой формулы и копируете в качестве значений.

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

Answer 5

1. Сводная таблица со столбцом, в котором вы пытаетесь найти дубликаты.

2. Посчитайте, сколько раз ваше значение появляется.

3. Скопируйте что-нибудь с количеством> 1 на другой лист

4. Используйте этот лист в качестве таблицы VLOOKUP, чтобы отметить дубликаты на исходном листе.

5. Скопируйте / вставьте значения из VLOOKUP (поскольку формула нестабильна, и вы не хотите, чтобы Excel пересчитывал каждый раз, когда вносятся изменения).

6. Фильтр для дубликатов.

Операция Vlookup по-прежнему занимает немного времени, но она значительно быстрее, чем COUNTIF.

Answer 6

У меня возникла та же проблема с формулой CountIf в таблице с несколькими тысячами строк. Следуя этим шагам, я смог сократить время до половины (с ~ 96 секунд до менее 40 секунд):

1. Создан дополнительный столбец с последовательной нумерацией (просто для возможности обратной сортировки).
2. При необходимости сортировал столбец, используемый в CountIf (по возрастанию или по убыванию).
3. Подсчитал количество
4. Преобразовал результаты CountIf из формул в значения.
5. Сортировал данные обратно, используя столбец последовательной нумерации (упомянутый на шаге 1).

В моем случае я использовал CountIf для получения номера строки значения в списке (например, SQL row_number ()). Список, который я использовал для выполнения шагов, имел дублированные значения.

Answer 7

Я обнаружил, что моя формула выражения = COUNTIF($ E $ 2:$ E $ 111111, E2) работает очень медленно, поэтому сделали следующее:

Поверните свои данные

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

Вернитесь к своим данным, вставьте столбец и создайте индекс, который сопоставит столбец идентификатора в ваших данных со столбцом сводной таблицы с идентификатором в строках.

Затем индексируйте счетчик столбца id на вашем оригинале сводки: = COUNTIF($ E $ 2: $ E $ 111111, E2)

используя индекс и соответствие

= INDEX(Pivot!B:B, MATCH(данные!E:E, Pivot!А: А, 0))

Это ускорило вычисления

Спасибо

Answer 8

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

Answer 9

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

1. Если ваша формула столбца b, то: выберите B2: B172000
2. Тип =COUNTIF($A$1:$A$172000,A1:A172000)
3. Нажмите Ctrl-Shift-Enter