VBA.Excel

Получил вот такой вопрос:

И хочу предложить один из вариантов решения этой задачи с помощью стандартных инструментов Excel (в следующих постах также рассмотрим решение отдельно с помощью Power Query и отдельно с помощью VBA).

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

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

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

Теперь я создам этот самый упомянутый в постановке задачи лист записи. То есть рандомно заполняю столбец ФИО лицами из нашей исходной таблички (добавляю около трёхсот строк), и в случае пяти строк преднамеренно вношу неправильный номер паспорта, симулируя ошибку.

Если также хочешь потренироваться  в решении этой задачи, то вот ссылка на скачивание файла, показанного выше:

https://drive.google.com/file/d/1-y1erQDwHdAMId-juqpJ0KGMtVF...

Итак, теперь собственно процесс поиска внесённых ошибок (естественно, представляем, что не знаем их).

В первую очередь статично пронумеруем все строки, кому-как удобно (формулой и вставкой в виде значений или автозаполнением):

Теперь будем удалять дубликаты по первым двум столбцам. Для этого выбираем одну из ячеек таблицы и идём во вкладку «Данные», где затем щелкаем по «Удалить дубликаты».

Здесь убираем галочку напротив столбца «Номер», так как он нам иначе помешает удалить правильные дубликаты записей и подтверждаем операцию. Итог будет следующий:

Уже у нас осталось 35 уникальных значений, что на 5 больше, чем в исходной таблице. Поэтому теперь делаем следующее.

Добавляем столбец «Подсчёт», и прописываем в нём функцию СЧЁТЕСЛИ. В первом аргументе указываем столбец со всеми ФИО, а во втором аргументе ссылаемся на ФИО текущей строки таблицы:

При этом я здесь работаю с умной таблицей, поэтому у меня были вставлены так называемые структурные ссылки ([ФИО], [@ФИО] и т.д.). Если тебе приходится проделывать эту операцию с обычным диапазоном на рабочем листе, то процесс имеет мельчайшее отличие (просто важно не забывать закреплять ссылки).

Вот что в итоге выходит в столбце «Подсчёт». Функция СЧЁТЕСЛИ считает, сколько раз встречается имя каждой текущей строки во всей таблице. Если имя встречается всего один раз, то из этого следует, что все записи с этим именем в таблице имели одинаковый номер паспорта. Там же где мы видим любое другое число нам становится понятно, сколько вариантов номера паспорта была внесено для одного и того же человека.

Так что всё что остаётся — это профильтровать значения. Убираю в фильтре столбца «Подсчёт» единицы, и мы получаем следующий результат:

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

Ну и конечно же можно еще сделать итоговый результат попривлекательнее (условное форматирование, сортировка и так далее):

Вот такое решение поставленной задачи с помощью стандартных инструментов Excel. Если хочешь более подробно узнать о всём, что было проделано в этом примере, предлагаю посмотреть следующее видео (в нём, помимо прочего, подробно рассказываю о примененном условном форматировании, о удалении дубликатов нескольких столбцов и т.д.):

Ну а в одном из следующих столбцов мы разберем более интересное и более оптимальное решение этой же задачи – с помощью Power Query!

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

Итак, пустой рабочий лист:

Первое с чего начнём, это внесём статичное значение 8 в ячейку A1. Просто выбираем её и, нажав на клавишу 8 на клавиатуре вписываем это значение в ячейку:

Под статичным значением понимается именно такое – внесенное с помощью клавиатуры, которое не изменяется.

Теперь в соседней ячейке B1 впишем уже динамически вычисляемое значение – то есть формулу. Выбрав ячейку, вписываем в ней следующее:

Когда Excel видит знак равенства, то понимает, что прописанное далее выражение нужно вычислить. Поэтому, когда мы подтверждаем ввод, по итогу видим всё тоже значение восемь, хоть и помним, что на самом деле внутри ячейки вписана вычисляемая формула:

Это первый пункт, который нужно понять. Ячейки в Excel – это своего рода контейнеры, в которые помещаются либо формулы, либо статичные значения. По итогу, после «просчёта» всех контейнеров, на поверхности ячейки Excel показывает нам либо внесённое статичное значение, либо значение, вычисленное формулой. Формула же при этом всё также остаётся внутри «контейнера».

Кроме того, в формулах могут быть ссылки на другие ячейки (они выделяются другим цветом). Вот пример:

Во время расчёта Excel подставит на место выделенных цветом ссылок итоговые отображаемые значения из соответствующих ячеек выше. В итоге расчёта получаем следующие значения:

Конечно же, ссылки и статичные значения можно комбинировать:

Получается мы уже рассмотрели два уровня отображения ячеек. Первый уровень – это то, что мы видим, находясь непосредственно «внутри» наших ячеек, то есть формулы и статичные значения. Второй уровень – это результат вычисления – то есть всё тоже статичное значение или результат вычисления формулы.

И нам остаётся рассмотреть лишь последний уровень, своеобразную линзу – уровень форматирования.

Выберем ячейки из первого столбца и поменяем их формат на денежный (Главная -> Число -> Денежный):

Теперь, как видишь, в названных столбцах также отображаются: разделитель десятичной части (запятая), десятичная часть числа (00 копеек), а также символ валюты (Рубль).

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

То есть таким образом форматирование — это дополнительная линза, наложенная поверх рабочего листа, которая меняет для нас лишь отображение значения.

В этом можно кстати еще раз убедиться вот так: скопировав область значений, попробуем вставить их ниже. Щелчок ПКМ по А4 выводит следующее контекстное окно:

1 – Вставка в качестве значений: форматирование теряется, и все значения вставляются в статичном виде (то есть формулы заменяются результатами вычисления этих формул):

2 – Вставка функций: форматирование теряется, формулы вставляются формулами (следим за сдвигом ссылок! Поможет здесь F4), а статичные значения – всё также статичными значениями:

3 – Копирование форматирования: копируется лишь эта итоговая «линза» форматирования (здесь значения оставлены с предыдущей вставки):

Вот такое небольшое резюме и визуализация того, как работают ячейки в Excel.

В видео ниже я рассказал об описанном выше принципе работы ячеек в виде 3D-визуализации, а также перечислил и показал все самые важные особенности и фишки профессиональной работы с формулами и функциями в Excel, так что предлагаю также посмотреть его:

Основным инструментом для отбора уникальных значений в VBA являются Dictionaries. Тем не менее, зачастую намного проще и быстрее получить список уникальных значений можно с помощью коллекций (простейший банальный плюс – в случае коллекций не надо включать отдельные библиотеки для того, чтобы работала функция Intellisense). Тем не менее, в подходе с коллекциями есть свои особенности, так что хотелось бы коротко представить этот подход.

Итак, в качестве примера предположим, что нам нужно из столбца «Страна» следующей таблицы извлечь список уникальных значений с помощь коллекции:

Для начала объявим все требуемые переменные:

Следующей строкой кода определим, где заканчиваются данные столбца С (используем для этого прыжок от последней ячейки к следующей заполненной сверху – эквивалент CTRL +↑):

Теперь, определив и сохранив номер последней строки в переменную lngLastRow, мы можем привязывать переменную rgDataColumn к нужному диапазону:

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

Теперь пропишем команду добавления элемента в коллекцию. Значение каждой ячейки будем вносить как в качестве значения элемента коллекции, так и в качестве его Key. Обрати при этом внимание на то, что Key всегда должен быть типа данных String, поэтому «оборачиваем» значение ячейки в функцию CStr, которая конвертирует значения в текстовый тип данных:

Теперь важный момент. Данная процедура, в её текущем состоянии, сразу же выдаст ошибку в случае встречи первого дубликата, так как ключи, эти самые Keys, должны быть обязательно уникальными. Поэтому перед циклом «выключаем» выведение ошибок с помощью команды On Error Resume Next, а после цикла, снова включаем с помощью On Error GoTo 0:

Вот и всё! Поставив Break-Point, прогоняю процедуру и проверяем, что у нас было внесено в коллекцию:

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

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

Приятного просмотра и с наступающим! 😊