Меню

Excel сравнение значения ячейки с диапазоном значений



Сравнение с диапазоном значений в Excel-e

Подскажите, плз, как в Excele организовать сравнение какой-либо величины с диапазоном других значений: а) через формулу (должна быть какая-то короткая запись); б) то же самое, но через VBA.

А что ты хочешь на выходе получить?

А что ты хочешь на выходе получить?

при совпадении искомого значения с каким-либо значением из диапазона поиска — должно быть совершено некое действие — для простоты — присвоение третьей переменной (ячейке и др.) некоего значения.

Причем диапазон значений не последовательный. т.е. он может выглядеть например так: 1,3,126,90,5,2,87. и т.д.

Тебе формула массива нужна.
Допустим, хочешь диапазон A1:A5 сравнить с ячейкой B1.
1. Выделяешь диапазон, в который поместить результат (должен быть такого же размера, как исходный диапазон).
2. В строке формул вбиваешь формулу =ЕСЛИ(A1:A5=B1;1;0)
3. Жмешь Ctrl + Shift + Enter
З.Ы. Так получаешь результат сравнения с каждой ячейкой диапазона. Или ты хочешь, чтобы при сопадении хотя бы с одним из значений что-то делалось?

да — надо именно, чтоб при совпадении хотя бы одного значения.
ЗЫ: а ctrl+Shift+Enter — это к чему?

С помощью Ctrl+Shift+Enter вводятся формулы массива.
Имеет смысл, когда нужно проделать однотипную операцию с элементами массивов одинаковой размерности, или когда хочешь на выходе получить массив.
Могу предложить вариант индикатора совпадения.
Вбей в любую ячейку =СУММ(ЕСЛИ(A1:A5=B1;1;0 и нажми Ctrl+Shift+Enter
Получишь количество совпадений.
Был в загашнике другой вариант, но сейчас подзабылось.

комбинация ctrl+Shift+Enter должна обрамить твою формулу втакие вот <> скобочки
т.е. добжно выглядеть как <=если(. )>
почитай про формулы массива в справке

Спасибо большое, парни!
Встретимся на просторах форума где-нибудь — за-плюсую насмерть!

А, вспомнил другой вариант — пользовать функцию ПОИСКПОЗ для поиска и ЕОШИБКА для анализа результата.

А что-то «поискпоз» у меня пермаментно ошибку выдает?!

Он ошибку выдает, если значение не найдено.
Именно для этого рекомендую юзать ЕОШИБКА.
Примерно так: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;A1:A5;0;0;1)

прикол в том, что там это значение есть 100% — я для проверки делал.
Ну да ладно — разберусь! =)
Спасибо за помощь!

В экселе можно каким-либо образом без макросов организовать столбец с уникальными значениями?

с уникальными значениями?

допустим прайс периодически пополняется вручную. Нужно чтобы все позиции прайса были уникальными по колонке «Наименование товара»

Не совсем понял условия.
Если тебе нужно из одного столбца выделить уникальные значения, то это делается с помощью расширенного фильтра.
Просто копируешь столбец, Данные-Фильтр-Расширенный фильтр. Указываешь куда скопировать, ставишь галочку «только уникальные».

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

Без макроса не знаю способа.

да.. здесь только макросить надо.
пишешь макрос на событие листа Onchange — на каждое изменение будет проводится проверка и там ты сможешь вбить любое предупреждение, какое пожелаешь!

спасибо

Источник

Сравнение значения в ячейке со значением в диапазоне

Добрый день. Потребовалось сравнить значения.
Допустим в столбце А записываются по порядку цифры с 1 и до какого-нибудь значения. В разных книгах их количество различно.
В столбце AS также записываются цифры в произвольном порядке, но их либо всегда меньше, то есть какое-нибудь число пропущено, либо одинаковое количество.
Задача такая, что бы макрос искал в столбце AS число, которое находится в ячейке A1 (это будет число 1) и если находит его, то в другую ячейку в этой же строке, но в другом столбце, записывалось значение, а если нет, то ставится пустота. И так по каждой ячейке.
Я написал код, но вылезает ошибка 13

Сравнение значения в Лэйбле со значением в ячейке базы данных
Существует база данных. В ячейке записано значение(числовое). А также существует Лэйбл с числовым.

Подсчет колл ячеек в диапазоне в зависимости от цвета ячейки и значения в смежной ячейке
Добрый день уважаемые специалисты, Помогите пожалуйста с формулой (можно макрос) для подсчет.

Сравнение значения переменной со значением из txt-файла
Ребят мне надо чтобы программа проверяла данную из .txt и сверяла её с данными из переменной i Там.

Читайте также:  Rx 570 сравнение с gtx 1660

Поиск минимального значения в 1 таблице и сравнение с минимальным значением 2 таблицы
Всем привет! Не могу решить казалось бы простую задачу. На одном листе есть 2 таблицы, в них по 2.

Давайте уточним вашу задачу:

1) В столбце «А» записаны числа ( не цифры) без пробелов и по порядку. ( допустим от 1 до 15 )
2) В столбце «AS» записаны числа ( не цифры) в произвольном порядке , причем их 1

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 — это прямоугольная область рабочего листа, которая образуется при пересечении одного столбца и одной строки. Если две ячейки имеют одну общую сторону, то ячейки называют смежными. Соответственно, если две ячейки общих сторон не имеют, то их называют несмежными. Кроме этого в Excel различают диапазон ячеек и группу ячеек .

Что такое диапазон ячеек в Excel?

В свою очередь диапазоны ячеек также могут быть как смежными, так и несмежными. Смежный д иапазон ячеек в Excel — это совокупность ячеек, представляющая собой прямоугольную область, полученную при пересечении нескольких столбцов и нескольких строк, идущих подряд. Два или более диапазонов, не примыкающих друг к другу, не имеющие общих сторон, являются несмежными. Такие диапазоны также называют несвязанными. Ячейки, входящие в несмежные диапазоны образуют группу ячеек.

Что такое используемый диапазон ячеек в Excel?

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

Что такое именованный диапазон ячеек в Excel?

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

По умолчанию имена диапазонов ячеек автоматически считаются абсолютными ссылками.

Для имен действует ряд ограничений:

— имя может содержать до 255 символов;

— первым символом в имени должна быть буква, знак подчеркивания (_) либо обратная косая черта (\), остальные символы имени могутбыть буквами, цифрами, точками и знаками подчеркивания;

— имена не могут быть такими же, как ссылки на ячейки;

— пробелы в именах не допускаются;

— строчные и прописные буквы не различаются.

Управление существующими именованными диапазонами (создание, просмотр и изменение) можно осуществлять при помощи диспетчера имен. В Excel 2007 диспетчер находится на вкладке «Формулы», в группе кнопок «Определенные имена».

Основные действия с диапазонами

Выделение диапазонов

О том как выделять ячейки и группы ячеек уже рассказывалось в одной из наших публикаций. Также ранее рассматривалась тема о том как выделять строки в рабочих листах Excel, но строка является одним из частных видов диапазона ячеек. Рассмотрим несколько способов выделения диапазонов ячеек в общем виде.

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

Читайте также:  Отрицательные числа их сравнения конспект урока

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

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

Сравнение диапазонов

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

Например, для построчного сравнения часто используется логическая функция «ЕСЛИ» и какой-либо из операторов сравнения (также можно использовать и другие функции, например «СЧЕТЕСЛИ» из категории статистические для проверки вхождения элементов одного списка в другой).

Также для поиска отличий по столбцам или по строкам используется стандартное средство Excel, которое находится на вкладке «Главная», в группе кнопок «Редактирование», в меню кнопки «Найти и выделить». Если в этом меню выбрать пункт «Перейти» и далее нажать кнопку «Выделить», то в диалоговом окне «Выделение группы ячеек» можно выбрать одну из опций «Отличия по строкам» или «Отличия по столбцам».

Для поиска повторяющихся или уникальных значений в двух диапазонах можно использовать условное форматирование.

Сравнение диапазонов можно провести и при помощи надстройки для Excel, которая позволяет находить и подсвечивать заливкой различия или совпадения в двух заданных диапазонах.

Изменение (преобразование) диапазонов значений

Одним из способов преобразования диапазона значений является транспонирование. Транспонирование — это такое преобразование диапазона значений, при котором данные, расположенные построчно перемещаются в столбцы и наоборот с сохранением порядка, то есть первая строка становится первым столбцом, вторая строка — вторым столбцом и так далее.

Транспонирование можно осуществить при помощи функции «=ТРАНСП(Диапазон)», которая находится в категории «Ссылки и массивы». Есть и другой способ — копирование диапазона значений с последующей специальной вставкой, при которой ставится флажок в поле «Транспонировать».

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

Надстройка для работы с диапазонами в Excel

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

Источник

8 способов как сравнить две таблицы в Excel

Добрый день!

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Простой способ, как сравнить две таблицы в Excel

Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.

Читайте также:  Сравнение российской экономики с началом века

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

Как сравнить две таблицы в Excel функции ЕСЛИ

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

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

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

Источник