Извлечение уникальных значений из диапазона ячеек или массива
Во вложении — пример использования этой функции в макросе (вывод уникальных записей в другой столбец листа), и пользовательская функция Уникальные — для использования в формулах листа Excel.
Макрос и дополнительная функция из файла во вложении:
Комментарии
Это потому что вы в другой файл перенесли только часть кода (а код функции UniqueValuesFromArray забыли скопировать)
Добрый день!
На родном файле макрос работает отлично, на другом, с тем же расширением не работает, выдает ошибку Sub or Function not defined на UniqueValuesFromArray. В чем может быть причина?
Расширения обоих файлов поменял на .xlsm
Спасибо!
Видимо, 36-е значение (которое «первое уникальное») — только с виду похоже на первое
(например, есть лишний пробел, или одна русская буква заменена похожей английской)
Сравнить посимвольно 2 ячейки можно этой надстройкой:
http://excelvba.ru/tools/CharCodes
Добрый день.
Сразу скажу, что в макросах не че не понимаю.
Мне очень понравилась ваша функция, но появилась проблема.
Функция выбирает 35 уникальных значений, далее возвращает первое уникальное.
В чем проблема?
пользовательская функция — для использования в качестве формулы массива
выделяете НЕСКОЛЬКО ячеек, вводите эту формулу, и завершаете ввод нажатием Ctrl + Shift + Enter (вместо обычного Enter)
Тогда будет выводиться несколько уникальных значений
А вообще, уникальные можно и формулами вывести, без макросов.
Добрый вечер. Разбираю макрос, который здесь представлен. С VBA не работала. Можно ли усложнить функцию вывода уникальных: вывод за период временной с исключением каких-то уникальных значений, т.е. возможно это значение встречается в этот период несколько раз, но выводить его не надо, так как не интересно. Буду очень благодарна за помощь
Добрый день. Разбираю сейчас Ваш макрос. Не сильна в них. Прошу помочь. В целом все работает отлично, но не могу понять как поменять его, чтобы новый массив уникальных вставал на другой (заданный) лист и не столбцом, а строкой. Буду очень признательна за помощь.
Владимир, ваш вопрос не имеет никакого отношения к теме статьи
В вашем случае, макросы не нужны, — достаточно одной формулы
Обратитесь на любой форум по Excel, прикрепив пример файла, — вам помогут с формулой.
Вопрос не актуален.
Прочитал ниже, что мне надо использовать http://excelvba.ru/code/JoinedArray
В дополнительном столбце, при помощи формулы типа =СЦЕПИТЬ(A1;»//»;B1), объедините значения этих 2 столбцов,
и потом по этому доп.столбцу отбирайте уникальные
Подскажите пожалуйста, как изменить ваши процедуры, чтобы можно было выбрать уникальные значения не одного столбца, а нескольких. Например в Стобце A значения а,в,а,в. В столбце B в соседних ячейках значения с,д,с,е. Нужно чтобы макрос вдавал массив: В первом столбце: а,в,в. В соседнем с,д,е.
Спасибо за функцию.
Работает отлично.
Это уже вторая Ваша функция в течение двух дней, которую я применил в своей работе.
Завтра еще третью буду пробовать — поиск в массиве 
Так сделать можно, но.
одно дело — выбрать уникальные значения (тут всё ясно, вариантов особо нет),
и совсем другое — выбрать строки с уникальными значениями в каком-то столбце.
К примеру, есть у нас 3 строки с одинаковыми значениями в 1-м столбце.
Какую из этих трёх строк выводить в результат? Первую, третью, вторую?
Вообще, у меня есть уже такая функция (даже с большей функциональностью, чем вам требуется):
http://excelvba.ru/code/JoinedArray
Сейчас выборка уникальный происходит только по столбцу «A».
Возможно ли сделать выборку уникальных по столбцу «A», но с условием, чтобы в диапазон фильтрования попадали также столбцы B и C ?
Т.е. чтобы был аналог функционалу Excel 2007: Выделяю столбцы A,B,C => Данные => Удалить дубликаты => В качестве столбца, по которому будет происходить удаление дубликатов выбираю только столбец A.
Как результат: уникальные значения будут отобраны по столбцу «А», но соответствующие записи из столбцов B и C будут также сохранены.
Вроде разобрался:
Dim ПервыйСтолбец As Range: Set ПервыйСтолбец = Range(Sheets(«данные»).Range(«A1»), Sheets(«данные»).Range(«A» & Rows.Count).End(xlUp))
Подскажите, пожалуйста. Сейчас макрос срабатывает в случае если активен лист с массивом.
Как правильно добавить название листа в запись, чтобы активация листа с массивом не была обязательной?
Dim ПервыйСтолбец As Range: Set ПервыйСтолбец = Sheets(«данные»).Range([A1], Sheets(«данные»).Range(«A» & Rows.Count).End(xlUp))
все прекрасно, только не совсем понятно, что происходит.. вот был бы файлик с работающим примером, там хоть по результату можно было б вычислить, что-куда
Отличный пример использования коллекций для создания массива уникальных записей. Давно использую коллекции, но до этого сам не додумался. И работает быстро ))
Как сравнить два листа в Excel (на предмет различий)
Пример 1. Есть две одинаковые (на первый взгляд) таблицы данных, которые содержат наименования продукции. Одну из них предположительно редактировал уволенный работник. Необходимо быстро сравнить имеющиеся данные и выявить несоответствия.
Диапазон Excel
Начнем с простого – присвоим имя ячейке. Для этого просто выделяем ее (1) и в поле имени (2) вместо адреса ячейки указываем произвольное название, которое легко запомнить.
Длина имени ограничена 255 символами, что более чем достаточно. Также в имени не должно быть пробелов, поэтому если оно состоит из нескольких слов, то их можно разделять знаком подчеркивания.
Если теперь на других листах книги нам нужно будет вывести данное значение или использовать его в дальнейших расчетах, то не обязательно переключаться на первый лист и указывать ячейку вручную. Достаточно просто ввести имя ячейки и ее значение будет подставлено.
Ячейки, строки, столбцы
Примеры диапазона
Диапазон представляет собой набор из двух и более ячеек.
Заполнение диапазона
Именованный диапазон
Аналогичным образом можно задать имя и для диапазона ячеек, то есть выделим диапазон (1) и в поле имени укажем его название (2):
Далее это название можно использовать в формулах, например, при вычислении суммы:
Также создать именованный диапазон можно с помощью вкладки Формулы, выбрав инструмент Задать имя.
Появится диалоговое окно, в котором нужно указать имя диапазона, выбрать область, на которую имя будет распространяться (то есть на всю книгу целиком или на отдельные ее листы), при необходимости заполнить примечание, а далее выбрать соответствующий диапазон на листе.
Для работы с существующими диапазонами на вкладке Формулы есть Диспетчер имен.
С его помощью можно удалять, изменять или добавлять новые имена ячейкам или диапазонам.
При этом важно понимать, что если вы используете именованные диапазоны в формулах, то удаление имени такого диапазона приведет к ошибкам.
Задача
Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):
Необходимо найти сумму продаж товаров в определенном месяце. Пользователь должен иметь возможность выбрать нужный ему месяц и получить итоговую сумму продаж. Выбор месяца пользователь должен осуществлять с помощью Выпадающего списка .
Для решения задачи нам потребуется сформировать два динамических диапазона : один для Выпадающего списка , содержащего месяцы; другой для диапазона суммирования.
Для формирования динамических диапазонов будем использовать функцию СМЕЩ() , которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Можно задавать высоту и ширину диапазона, а также смещение по строкам и столбцам.
Создадим динамический диапазон для Выпадающего списка , содержащего месяцы. С одной стороны нужно учитывать тот факт, что пользователь может добавлять продажи за следующие после апреля месяцы (май, июнь…), с другой стороны Выпадающий список не должен содержать пустые строки. Динамический диапазон как раз и служит для решения такой задачи.
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Месяц ;
- в поле Область выберите лист Книга ;
- в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
- нажмите ОК.
При заполнении таблицы данными о продажах за май , июнь и т.д., формула СЧЁТЗ(лист1!$B$5:$I$5) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .
ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков.
Теперь создадим еще один динамический диапазон для суммирования продаж.
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Продажи_за_месяц ;
- в поле Диапазон введите формулу = СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)
- нажмите ОК.
И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.
Примечание: Вместо формулы с функцией СМЕЩ() для подсчета заполненных месяцев можно использовать формулу с функцией ИНДЕКС() : = $B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))
Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Выделить текущий динамический диапазон можно с помощью Условного форматирования . В файле примера для ячеек диапазона B6:I14 применено правило Условного форматирования с формулой: = СТОЛБЕЦ(B6)=СТОЛБЕЦ(Продажи_за_месяц)
Условное форматирование автоматически выделяет серым цветом продажи текущего месяца , выбранного с помощью Выпадающего списка .
Как посчитать количество ссылок на столбцы таблицы Excel
Пример 2. Определить количество столбцов в таблице и записать это значение в ячейку A16.
Используем формулу ОБЛАСТИ, поочередно выделяя каждый столбец ячейки в качестве параметра. Перед выбором последующего столбца нажимаем и удерживаем кнопку Ctrl. Если добавить символ «)» и нажать Enter, появится диалоговое окно с сообщением о том, что было введено слишком много аргументов. Добавим дополнительные открывающую и закрывающую скобки.
Определение принадлежности ячейки к диапазону таблицы
Пример 3. Определить, принадлежит ли ячейка заданному диапазону ячеек.
Рассматриваемая функция также позволяет определить, принадлежит ли ячейка выделенной области. Выполним следующие действия:
Данная ошибка означает, что ячейка не принадлежит выделенной области.
Если выделить несколько ячеек внутри диапазона, функция ОБЛАСТИ вернет количество выделенных ячеек:
Описанные особенности работы данной функции могут быть полезны при работе с большим количеством таблиц данных.
Перемещение и копирование ячеек и их содержимого
The_Prist копировать выделенный диапазон что выделяем для описанные выше действияCtrl+Space. Только имейте в чем хотелось бы. вкладке Главная илиЧтобы переместить ячейки, нажмите. параметры вставки, которые которые не нужно другой лист или Мы стараемся как можно
grablik заданными параметрами дат.Юрий М: В примере все по одной ячейке.
копирования не Range(“7:7″ с формой.(Пробел). Таким способом виду, что здесь На самом деле, нажмите Ctrl+V на кнопкуСочетание клавиш
будут выделены только существует несколько особенностей, это один из
Вырезанные ячейки переместятся на. клавиш CTRL+V.При копировании значения последовательно ячеек с данными, листа или выберите
[expert_bq id=»1570″]В этом примере мы сравниваем каждую ячейку на одном листе с соответствующей ячейкой на другом листе выполняется с помощью оператора не равно в формуле. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Скопируйте и вставьте эту формулу для диапазона, чтобы охватить весь набор данных на обоих листах. Поскольку у меня небольшой набор данных, я скопирую и вставлю эту формулу только в диапазон A1: B10.
Excel сравнение двух таблиц из разных файлов — Вэб-шпаргалка для интернет предпринимателей!
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Месяц ;
- в поле Область выберите лист Книга ;
- в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
- нажмите ОК.
При заполнении таблицы данными о продажах за май , июнь и т.д., формула СЧЁТЗ(лист1!$B$5:$I$5) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .
10 наиболее полезных функций при анализе данных в Excel
Excel содержит огромное количество самых разнообразных функций, однако не все они нужны при анализе данных. В этой статье вы узнаете о 10 наиболее популярных функций, которые будут нужны при работе с информацией. Эти функции позволяют выполнить большинство задач, которые появляются при анализе данных.
ВПР
Эта функция является одной из самых популярных и часто используемых в Excel. Если вам необходимо найти данные в одном столбце в таблице и получить значение из другого столбца таблицы, то эта функция вам поможет. Ее синтаксис:
— Искомое значение — это то значение, которое мы будем искать в таблице с данными
— Таблица — диапазон данных, в первом столбце которого мы будем искать искомое значение
— Номер столбца — этот параметр обозначает, на какое количество столбцов надо сдвинуться вправо в таблице для получения результата
— Интервальный просмотр — Может принимать параметр 0 или ЛОЖЬ, что обозначает что совпадение между искомым значением и значением в первом столбце таблицы должен быть точным; либо 1 или ИСТИНА, соответственно совпадение должно быть неточным. Настоятельно рекомендую использовать только параметр ЛОЖЬ, иначе можно получать непредсказуемые результаты.
Если хотите изучить более подробно, как работает функция ВПР, прочитайте нашу статью «Функция ВПР в Excel».
ГПР
Функция ГПР выполняет туже задачу, что и ВПР, только она просматривает первую строку в поиске искомого значения и для получения результата сдвигается на указанное количество строк вниз.
— Таблица- диапазон данных на листе, где в первой строке мы ищем искомое значение и сдвигаемся на необходимое количество строк.
— Интервальный просмотр — ставьте всегда 0, тогда Эксель будет искать точное совпадение, что нам и нужно в большинстве случаев.
Если вы хотите более подробно изучить, как пользоваться функцией ГПР — прочитайте статью на нашем сайте «Функция ГПР в Excel».
ЕСЛИ
Функция ЕСЛИ является очень популярной в Excel. Она позволяет автоматически выполнять какое-либо действие, в зависимости от поставленного условия.
Функция ЕСЛИ выполняет проверку логического выражения и если выражение истинно, то поставляется одно значение и альтернативное, если ложь. Синтаксис следующий:
— Логическое выражение — выражение, которое по итогу своего вычисления должно вырнуться значение ИСТИНА или ЛОЖЬ.
— Значение, если истина — устанавливаем указанное значение, если логическое выражение вернуло ИСТИНА
— Значение, если ложь — устанавливает указанное значение, если логическое выражение вернуло ЛОЖЬ.
В примере выше мы хотим определить, получили ли мы за месяц выручку больше 500 рублей или нет. В формуле ЕСЛИ(B2>500;»Да»;»Нет») первый параметр (B2>500) проверяет, выручка за месяц больше 500 рублей или нет; второй параметр («Да») — функция вернет Да, если выручка больше 500 рублей и соответственно Нет (третий параметр), если выручка меньше.
Обратите внимание, что значения при истине или лжи могут быть не только текстовые, числовые, но также и функции(в том числе и ЕСЛИ), что позволяет реализовать достаточно сложные логические конструкции.
ЕСЛИОШИБКА
При работе с формулами в Excel, можно время от времени сталкиваться с различными ошибками. Так в примере ниже функция ВПР вернула ошибку #Н/Д из-за того, что в базе данных по ФИО нет искомой нами фамилии (более подробно об ошибке #Н/Д вы можете прочитать в этой статье: «Как исправить ошибку #Н/Д в Excel»)
Для обработки таких ситуаций отлично подойдет функция ЕСЛИОШИБКА. Ее синтаксис следующий:
— Значение, результат которого проверяется на ошибку.
— Значение, если ошибка — В случае, если в результате работы функции получаем ошибку, то выводится не ошибка, а данное значение.
В случае с нашим примером выше, мы можем предположить, что фамилия может быть некорректной, соответственно ЕСЛИОШИБКА вернет нам предупреждение, что бы мы проверили написание фамилии.
В примере выше, мы проверяем результат работы функции ВПР(E2;A1:C6;2;0) и в случае, если вернется ошибка, то выдаем сообщение «Проверьте фамилию!».
СУММЕСЛИМН
Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.
— Диапазон суммирования — область листа Эксель, из которой мы суммируем данные
— Диапазон условия 1 — Диапазон ячеек, которые мы проверяем на соответствие условию
— Условие 1 — Условие, которое проверяется на соответствие в Диапазоне 1.
Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно.
Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город.
Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».
СЧЁТЕСЛИМН
СЧЁТЕСЛИМН очень похожа на функцию СУММЕСЛИМН, только в отличии от нее, она не суммируется значения, а только считает количество ячеек, которые соответствуют определенным условиям. Как и в случае с СУММЕСЛИМН, у СЧЁТЕСЛИМН есть упрощенная форма СЧЁТЕСЛИ, который считает количество ячеек только по одному критерию, но лучше используйте более общий вариант.
— Диапазон условия 1 — Диапазон ячеек, которые проверяются на соответствие определенному условию.
— Условие 1 — Условие, которое определяет какие ячейки надо учитывать при подсчете.
Обратите внимания, что диапазонов условий и соответственно условий может быть несколько.
СЖПРОБЕЛЫ
— Текст — тот текст, из которого надо убрать лишние пробелы.
ЛЕВСИМВ и ПРАВСИМВ
Функции ЛЕВСИМВ и ПРАВСИМВ возвращают определенное количество знаков с начала (ЛЕВСИМВ) либо с конца (ПРАВСИМВ) строки. Эти функции нужны для получения части строки. Синтаксис у функций однотипный:
— Текст — то строковое выражение, из которого мы хотим получить часть.
— Количество знаков — число символов, которое мы хотим получить.
В примере выше мы из текста «Пример текста» извлекаем 6 символов слева и получаем текст «Пример».
СЦЕПИТЬ
Функция СПЕПИТЬ позволяет объединить значения из нескольких ячеек. Синтаксис у функции достаточно простой:
— Текст 1 — Текст, который надо соединить в одну строку
— Текст 2 — Текст, который надо соединить в одну строку
Обратите внимание, что вы можете объединить до 255 текстовых значений.
В примере выше мы объединяем фамилию и имя. В функции СЦЕПИТЬ(A2;» «;B2), первый параметр(А2) — ссылка на ячейку с фамилией; второй параметр (» «) — пробел, что бы итоговый текст смотрелся нормально; третий параметр(В2) — ссылка на ячейку с именем.
10.ЗНАЧЕН
Часто данные, которые мы получаем из внешних источников, имеют текстовый формат и мы не можем производить с ними математических действий (складывать, вычитать и т.п.). Нам требуется сначала преобразовать текст в число, для этого используйте функцию ЗНАЧЕН. Синтаксис у функции следующий:
Как видно в примере выше, у нас есть число 12522, которое представлено в виде текста, при помощи функции ЗНАЧЕН мы преобразовали его в число 12 522, с которым в дальнейшем можем работать, как с любыми другими числами.
Спасибо, что дочитали статью. Я постарался выбрать 10 наиболее полезных функций в Excel, которые нужны при анализе данных. Жду ваши комментарии.
10 наиболее полезных функций при анализе данных в Excel — ExcelGuide: Про Excel и не только
На этом этапе, если вы прокрутите один из листов вниз, другой останется как есть. Вы можете изменить это так, чтобы при прокрутке одного листа одновременно прокручивалась и другая. Это упрощает сравнение строк за строками и выявление различий.
