Сравнение ячейки с диапазоном excel
Обновлено: 03.07.2024
Добрый день. Потребовалось сравнить значения.
Допустим в столбце А записываются по порядку цифры с 1 и до какого-нибудь значения. В разных книгах их количество различно.
В столбце AS также записываются цифры в произвольном порядке, но их либо всегда меньше, то есть какое-нибудь число пропущено, либо одинаковое количество.
Задача такая, что бы макрос искал в столбце AS число, которое находится в ячейке A1 (это будет число 1) и если находит его, то в другую ячейку в этой же строке, но в другом столбце, записывалось значение, а если нет, то ставится пустота. И так по каждой ячейке.
Я написал код, но вылезает ошибка 13
Отвечу на любой вопрос по пояснениям. __________________
Помощь в написании контрольных, курсовых и дипломных работ здесь
Сравнение значения в Лэйбле со значением в ячейке базы данных
Существует база данных. В ячейке записано значение(числовое). А также существует Лэйбл с числовым.
Сравнение значения переменной со значением из txt-файла
Ребят мне надо чтобы программа проверяла данную из .txt и сверяла её с данными из переменной i Там.
Поиск минимального значения в 1 таблице и сравнение с минимальным значением 2 таблицы
Всем привет! Не могу решить казалось бы простую задачу. На одном листе есть 2 таблицы, в них по 2.
Давайте уточним вашу задачу:
1) В столбце "А" записаны числа ( не цифры) без пробелов и по порядку. ( допустим от 1 до 15 )
2) В столбце "AS" записаны числа ( не цифры) в произвольном порядке , причем их <= чем в столбце "А"
Необходимо уточнение:
а) Есть ли повторяющиеся числа в столбце "AS"?
3) Нужно пройтись по столбцу "AS" и если число в строке x столбца " AS" совпадает с числом в строке у столбца "А" то нужно
( напишите в какой столбец и в какую строку какие данные занести ). Например:
В столбец справа от "AS" на строчку х занести данные столбца "А" строки у
Narimanych,
1) повторяющихся чисел в столбце AS не будет.
Пример:
Допустим в столбце А у нас числа от 1 до 10. В столбце AS числа от 2 до 7 в произвольном порядке.
В столбце B у нас определенные данные(не имеет значения какие).
Начинаем с ячейки A1 там занесено число 1
Если в столбце AS у нас нет числа 1, тогда в ячейку C1 никакие данные не заносятся.
Далее переходим в ячейку A2, если в столбце AS есть число 2, то в ячейку C2 копируются данные из ячейки В2.
И так далее
Добавлено через 4 минуты
ji2n,
По идее же переменная e должна быть значением, которое будет найдено или не найдено в диапазоне AS, почему ее нельзя задать как range? И если нельзя, то какой тип ей дать тогда?
Табличный процессор Эксель – одна из самых популярных программ для работы с электронными таблицами. И нередко у пользователя возникает вопрос – можно ли сравнить в Excel несколько столбцов на наличие совпадений. Особенно это важно для тех, кто работает с огромными объемами информации и, соответственно, большими таблицами.
Колонки сравнивают для того, чтобы, например, в отчетах не было дубликатов. Или, наоборот, для проверки правильности заполнения — с поиском непохожих значений. И проще всего выполнять сравнение двух столбцов на совпадение в Excel — для этого есть 6 способов.
1 Сравнение с помощью простого поиска
При наличии небольшой по размеру таблицы заниматься сравнением можно практически вручную. Для этого достаточно выполнить несколько простых действий.
- Перейти на главную вкладку табличного процессора.
- В группе «Редактирование» выбрать пункт поиска.
- Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
- Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.
Если значение обнаружено, результатом станет выделение нужной ячейки. Однако с помощью такого способа можно работать только с небольшими столбцами. И, если это просто цифры, так можно сделать и без поиска — определяя совпадения визуально. Впрочем, если в колонках записаны большие объемы текста, даже такая простая методика позволит упростить поиск точного совпадения.
2 Операторы ЕСЛИ и СЧЕТЕСЛИ
Еще один способ сравнения значений в двух столбцах Excel подходит для таблиц практически неограниченного размера. Он основан на применении условного оператора ЕСЛИ и отличается от других методик тем, что для анализа совпадений берется только указанная в формуле часть, а не все значения массива. Порядок действий при использовании методики тоже не слишком сложный и подойдет даже для начинающего пользователя Excel.
- Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
- В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
- Протянуть формулу до конца столбца.
Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.
Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.
Тот же способ подойдет и для сравнения сразу большого количества колонок с данными на точное совпадение не только значения, но и строки. Для этого применяется уже не оператор ЕСЛИ, а функция СЧЕТЕСЛИ. Принцип написания и размещения формулы похожий.
Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.
Если поставить вместо тройки двойку, результатом будет поиск только тех совпадений с первой колонкой, которые присутствуют в одном из других столбцов. Причем, тройные дубликаты формула проигнорирует. Так же как и совпадения второй и третьей колонки.
3 Формула подстановки ВПР
Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.
4 Функция СОВПАД
Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:
- В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
- Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
- Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.
Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».
Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.
5 Сравнение с выделением совпадений цветом
В поисках совпадений между данными в 2 столбцах пользователю Excel может понадобиться выделить найденные дубликаты, чтобы их было легко найти. Это позволит упростить поиск ячеек, в которых находятся совпадающие значения. Выделять совпадения и различия можно цветом — для этого понадобится применить условное форматирование.
Часто стоит задача сравнить два списка элементов. Делать это вручную слишком утомительно, и к тому же нельзя исключать вероятность ошибок. Excel упрощает эту операцию. В данном совете описывается метод с применением условного форматирования.
На рис. 164.1 приведен пример двух многостолбцовых списков имен. Применение условного форматирования может сделать различия в списках очевидными. Эти примеры списков содержат текст, но рассматриваемый метод работает и с числовыми данными.
Рис. 164.1. Вы можете использовать условное форматирование, чтобы выделить различия в двух диапазонах
Первый список — А2:В31 , этот диапазон называется OldList. Второй список — D2:E31 , диапазон называется NewList. Диапазоны были названы с помощью команды Формулы ► Определенные имена ► Присвоить имя. Давать названия диапазонам необязательно, но это облегчает работу с ними.
Начнем с добавления условного форматирования к старому списку.
Рис. 164.2. Применение условного форматирования
Ячейки в диапазоне NewList используют подобную формулу условного форматирования.
В результате имена, которые находятся в старом списке, но которых нет в новом, будут выделенными (рис. 164.3). Кроме того, имена в новом списке, которых нет в старом, также выделены, но другим цветом. Имена, появляющиеся в обоих списках, не выделены.
Обе формулы условного форматирования используют функцию СЧЁТЕСЛИ. Она рассчитывает, какое количество раз определенное значение появляется в диапазоне. Если формула возвращает 0, это означает, что элемент не входит в диапазон. Таким образом, условное форматирование берется за дело, и цвет фона ячейки меняется.
Рис. 164.3. Условное форматирование приводит к тому, что различия в двух списках выделяются
Ссылка на ячейку в функции СЧЁТЕСЛИ всегда должна быть в верхней левой ячейке выбранного диапазона.
Читайте также: