Как сравнить текст в ячейках таблицы Excel

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

Таблица значений

Описание:

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

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

Элементы коллекции: СтрокаТаблицыЗначений

Для объекта доступен обход коллекции посредством оператора Для каждого … Из … Цикл. При обходе выбираются элементы коллекции.

Возможно обращение к элементу коллекции посредством оператора […]. В качестве аргумента передается индекс строки (нумерация с 0).

Свойства:

Индексы Содержит коллекцию индексов таблицы значений.
Колонки Содержит коллекцию колонок таблицы значений.

Методы:

Вставить(Индекс) Вставляет строку на позицию в таблице значений, соответствующую указанному индексу. Возвращает вставленную строку.
ВыбратьСтроку(Заголовок, НачальнаяСтрока) Вызывает модальное диалоговое окно для интерактивного выбора строки из таблицы значений. Возвращает выбранную строку или Неопределено, если пользователь отказался от выбора.
ВыгрузитьКолонку(Колонка) Создает массив и копирует в него значения, содержащиеся в колонке таблицы значений. Возвращает этот массив.
Добавить() Добавляет строку в конец таблицы значений. Возвращает добавленную строку.
ЗагрузитьКолонку(Массив, Колонка) Загружает колонку таблицы значений из массива значений.
ЗаполнитьЗначения(Значение, Колонки) Заполняет все строки таблицы значений указанным значением.
Индекс(Строка) Получает индекс строки в коллекции строк таблицы значений. Если не найдено, то возвращается -1.
Итог(Колонка) Суммирует значения всех строк в указанной колонке. Если в колонке установлен тип и он единственный, то при суммировании будет предприниматься попытка преобразования значения к типу Число. Если колонке не присвоены типы, то в процессе суммирования будут принимать участие только значения, имеющие тип Число, значения других типов будут игнорироваться. Если в колонке несколько типов и среди них есть тип Число, то в процессе суммирования будут принимать участие только значения, имеющие тип Число, значения других типов будут игнорироваться. Если в колонке несколько типов и среди них нет типа Число, то результатом будет значение Неопределено.
Количество() Получает количество строк таблицы значений.
Найти(Значение, Колонки) Осуществляет поиск значения в указанных колонках таблицы значений. Возвращает строку, в которой содержится искомое значение. Если значение не найдено, то возвращается значение Неопределено.
НайтиСтроки(ПараметрыОтбора) Осуществляет поиск строк таблицы значений, отвечающих заданным условиям поиска. Возвращает массив строк таблицы значений, соответствующих условиям поиска. Замечание! Массив хранит ссылки на строки таблицы значений, то есть при изменении строки в таблице, значение в массиве тоже будет измененным.
Очистить() Удаляет все строки таблицы значений. Структура колонок остается неизменной.
Получить(Индекс) Получает значение по индексу. Работает аналогично оператору [].
Свернуть(КолонкиГруппировок, КолонкиСуммирования) Осуществляет свертку таблицы значений по указанным колонкам группировки. Строки, у которых совпадают значения в колонках, указанных в первом параметре, сворачиваются в одну строку. Значения этих строк, хранящиеся в колонках, указанных во втором параметре, накапливаются. Важно! Списки колонок не должны пересекаться. Колонки, не вошедшие ни в один из списков колонок, после выполнения метода удаляются из таблицы значений.
Сдвинуть(Строка, Смещение) Сдвигает строку на указанное количество позиций.
Скопировать(Строки, Колонки) Если указаны строки и колонки, то только они будут скопированы. Если не указаны, то будет создана полная копия таблицы значений.
СкопироватьКолонки(Колонки) Создает таблицу значений с заданным списком колонок.
Сортировать(Колонки, ОбъектСравнения) Сортирует таблицу значений в соответствии с указанными правилами сортировки.
Удалить(Строка) Удаляет строку таблицы значений, где Строка – индекс или сама строка.

Конструктор:

&НаСервереПроцедура ВыполнитьКодНаСервере()     /// Как создать таблицу значений в 1с 8.3, 8.2  // с таблицей значений можно работать только на сервере Таб = Новый ТаблицаЗначений;      /// Как добавить колонки в таблицу значений в 1с 8.3, 8.2  Таб.Колонки.Добавить(“Фрукт”); Таб.Колонки.Добавить(“Цвет”); Таб.Колонки.Добавить(“Вкус”);     /// Как добавить строки в таблицу значений в 1с 8.3, 8.2  Стр = Таб.Добавить(); Стр.Фрукт = “Яблоко”; Стр.Цвет = “Зелёный”; Стр.Вкус = “Кислый”;  Стр = Таб.Добавить(); Стр.Фрукт = “Банан”; Стр.Цвет = “Желтый”; Стр.Вкус = “Сладкий”;  Стр = Таб.Добавить(); Стр.Фрукт = “Слива”; Стр.Цвет = “Синий”; Стр.Вкус = “Терпкий”;      /// Как добавить в таблицу значений колонку с    /// описанием типа и заголовка в 1с 8.3, 8.2  ОписаниеВеса = Новый ОписаниеТипов(“Число”, Новый КвалификаторыЧисла(12, 3));  Таб.Колонки.Добавить(“ВесФрукта”, ОписаниеВеса, “Вес фрукта”); Таб[0].ВесФрукта = 100.005; Таб[1].ВесФрукта = 60.010; Таб[2].ВесФрукта = 25.000;     /// Как перечислить все строки в таблице значений в 1с 8.3, 8.2  Для Каждого Стр Из Таб Цикл Сообщить(Стр.Фрукт + ” “ + Стр.Цвет + ” “ + Стр.Вкус + ” “ + Стр.ВесФрукта); КонецЦикла;     /// Как перечислить все колонки в таблице значений    /// в 1с 8.3, 8.2  Для Каждого Колонка Из Таб.Колонки Цикл Сообщить(Колонка.Имя); КонецЦикла;      /// Как выгрузить колонку таблицы значений в массив в 1с 8.3, 8.2  // выгрузка названий фруктов в массив МассивФруктов = Таб.ВыгрузитьКолонку(“Фрукт”);  // ничего не измениться, так как массив не менялся Таб.ЗагрузитьКолонку(МассивФруктов, “Фрукт”);     /// Как заполнить значения в колонке для всех строк таблицы     /// значений в 1с 8.3, 8.2  // обнулим вес во всех строках Таб.ЗаполнитьЗначения(0, “ВесФрукта”);  // выставим новые веса Таб[0].ВесФрукта = 100; Таб[1].ВесФрукта = 60; Таб[2].ВесФрукта = 25;     /// Как в таблице значений посчитать итог по колонке в 1с 8.3, 8.2  Сообщить(Таб.Итог(“ВесФрукта”)); // 185     /// Как найти строку в таблице значений по значению в 1с 8.3, 8.2  // найдём строчку с яблоком и покажем его цвет НайденнаяСтрока = Таб.Найти( “Яблоко” ); Если НайденнаяСтрока <> Неопределено Тогда Сообщить(НайденнаяСтрока.Цвет); // Зелёный КонецЕсли;     /// Как найти строку в таблице значений поиском значения    /// в определенных колонках в 1с 8.3, 8.2  // будем искать по слову “Кислый” в колонках вкус и цвет НайденнаяСтрока = Таб.Найти(“Кислый”, “Вкус, Цвет”); Если НайденнаяСтрока <> Неопределено Тогда Сообщить(НайденнаяСтрока.Фрукт); // Яблоко КонецЕсли;  // добавим ещё один зелёный фрукт Стр = Таб.Добавить(); Стр.Фрукт = “Киви”; Стр.Цвет = “Зелёный”; Стр.Вкус = “Сладкий”; Стр.ВесФрукта = 30;     /// Как искать в таблице значений неуникальные значения    /// в 1с 8.3, 8.2  Отбор = Новый Структура; Отбор.Вставить(“Цвет”, “Зелёный”); Строки = Таб.НайтиСтроки(Отбор); Для Каждого Стр Из Строки Цикл Сообщить(Стр.Фрукт); // Яблоко Киви КонецЦикла;     /// Как сдвинуть строку таблицы значений в 1с 8.3, 8.2  // сдвигаем первую строку на одну позицию вперёд Таб.Сдвинуть(0, 1);  // и возвращаем обратно Таб.Сдвинуть(1, 1);     /// Как создать копию таблицы значений в 1с 8.3, 8.2  // полная копия КопияТаб = Таб.Скопировать();     /// Как создать копию таблицы значений по нужным колонкам    /// в 1с 8.3, 8.2  // только две колонки будет Фрукт и Цвет КопияТаб = Таб.СкопироватьКолонки(“Фрукт, Цвет”);     /// Как создать копию таблицы значений по отбору в 1с 8.3, 8.2  Отбор = Новый Структура; Отбор.Вставить(“Фрукт”, “Яблоко”);  // таблица будет содержать только строчку с яблоком КопияТаб = Таб.Скопировать(Отбор);  // таблица будет содержать цвет и вес яблока КопияТаб = Таб.Скопировать(Отбор, “Цвет, ВесФрукта”);     /// Как свернуть таблицу значений по колонке в 1с 8.3, 8.2  // свернём по вкусу, просуммируем по весу Таб.Свернуть(“Вкус”, “ВесФрукта”);     /// Как отсортировать таблицу значений по колонке в 1с 8.3, 8.2  Таб.Сортировать(“ВесФрукта Возр”);     /// Как отсортировать таблицу значений по нескольким колонкам    /// в 1с 8.3, 8.2  Таб.Сортировать(“ВесФрукта Возр, Вкус Убыв”);     /// Как удалить строку из таблицы значений в 1с 8.3, 8.2  // по индексу или по значению Таб.Удалить(0); КонецПроцедуры /// Как программно вывести таблицу значений на/// форму в 1с 8.3, 8.2  // см. примеры использования этой функции ниже // ТабВКоде – таблица значений, созданная программно// ТабРеквизит – имя реквизита формы типа ТаблицаЗначений// ТабНаФорме – имя элемента формы, связанного с ТабРеквизит  &НаСервереПроцедура ВывестиТаблицуЗначенияВКодеВТаблицуНаФорме(ТабВКоде, ТабРеквизит, ТабНаФорме)  // реквизит ТабРеквизит и соответствующий // ему элемент формы ТабНаФорме уже созданы // нами в визуальном режиме  // 1. добавляем колонки из ТабВкоде в реквизит ТабРеквизит  НовыеРеквизиты = Новый Массив;  Для Каждого Колонка Из ТабВКоде.Колонки Цикл НовыеРеквизиты.Добавить( Новый РеквизитФормы( Колонка.Имя, Колонка.ТипЗначения, ТабРеквизит ) ); КонецЦикла;  ИзменитьРеквизиты(НовыеРеквизиты);  // 2. добавляем колонки из ТабВКоде в элемент ТабНаФорме  Для Каждого Колонка Из ТабВКоде.Колонки Цикл  НовыйЭлемент = Элементы.Добавить( ТабРеквизит + “_” + Колонка.Имя, Тип(“ПолеФормы”), Элементы[ТабНаФорме] ); НовыйЭлемент.Вид = ВидПоляФормы.ПолеВвода; НовыйЭлемент.ПутьКДанным = ТабРеквизит + “.” + Колонка.Имя; КонецЦикла;  // 3. наконец, передаём данные из ТабВКоде в ТабРеквизит  ЗначениеВРеквизитФормы(ТабВКоде, ТабРеквизит);  // готово!  КонецПроцедуры &НаСервереПроцедура СоздатьТаблицуНаФормеНаСервере()  // программно создаём таблицу значений ТабВКоде  ТабВКоде = Новый ТаблицаЗначений;  ОписаниеСтроки = Новый ОписаниеТипов(“Строка”); ОписаниеВеса = Новый ОписаниеТипов(“Число”, Новый КвалификаторыЧисла(12, 3));  ТабВКоде.Колонки.Добавить(“Фрукт”, ОписаниеСтроки, “Имя фрукта”); ТабВКоде.Колонки.Добавить(“Цвет”, ОписаниеСтроки, “Цвет фрукта”); ТабВКоде.Колонки.Добавить(“Вес”, ОписаниеВеса, “Вес фрукта”);  Стр = ТабВКоде.Добавить(); Стр.Фрукт = “Яблоко”; Стр.Цвет = “Зелёный”; Стр.Вес = 200;  Стр = ТабВКоде.Добавить(); Стр.Фрукт = “Банан”; Стр.Цвет = “Желтый”; Стр.Вес = 150;  ВывестиТаблицуЗначенияВКодеВТаблицуНаФорме(ТабВКоде, “ТабРеквизит1”, “ТабНаФорме1”); КонецПроцедуры /// Как программно вывести на форму результат запроса/// в виде таблицы значений в 1с 8.3, 8.2 &НаСервереПроцедура ВывестиНаФормуРезультатЗапросаНаСервере()   Запрос = Новый Запрос; Запрос.Текст = “ВЫБРАТЬ | Товары.Ссылка, | Товары.ВерсияДанных, | Товары.ПометкаУдаления, | Товары.Код, | Товары.Наименование, | Товары.Цвет, | Товары.ЕдиницаИзмерений, | Товары.Предопределенный, | Товары.ИмяПредопределенныхДанных |ИЗ | Справочник.Товары КАК Товары”;  РезультатЗапроса = Запрос.Выполнить();  ТабВКоде = РезультатЗапроса.Выгрузить(ОбходРезультатаЗапроса.Прямой);   // функция ВывестиТаблицуЗначенияВКодеВТаблицуНаФорме определена выше ВывестиТаблицуЗначенияВКодеВТаблицуНаФорме(ТабВКоде, “ТабРеквизит2”, “ТабНаФорме2”); КонецПроцедуры /// Скачать и выполнить эти примеры на компьютере

).

расскажите (кнопки поделиться ниже) о нём своим друзьям и коллегам. Сделайте это один раз и вы внесете существенный вклад в развитие сайта. На сайте

, но чем больше людей им пользуются, тем больше сил у меня для его поддержки.

Способ № 1: сравнить в базе 1С

На любой платформе 1С есть типовая обработка «Сравнить файлы». Находится в верхнем левом углу: «Главное меню — Файл — Сравнить файлы».

4 способа быстро и легко сравнить большие таблицы

Она позволяет сравнить содержимое файлов формата .txt и .mxl. Другие форматы не принимает и выдает сообщения: «Ошибка при выполнении файловой операции» и «Бинарные файлы различны».

4 способа быстро и легко сравнить большие таблицы

Чтобы начать работать, добавьте сопоставляемые файлы в окошки «Сравнить» и «С». Укажите «Как» сравнить: табличный документ, текстовый документ или двоичное сравнение. Нажмите клавишу «ОК».

4 способа быстро и легко сравнить большие таблицы

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

4 способа быстро и легко сравнить большие таблицы

На верхней панели инструментов доступны следующие команды →4 способа быстро и легко сравнить большие таблицы

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

4 способа быстро и легко сравнить большие таблицыменяет порядок сравнения: если ее нажать, то правый лист окажется слева,
а левый — справа;

4 способа быстро и легко сравнить большие таблицы— настройка ячеек по цветам.

4 способа быстро и легко сравнить большие таблицы

В нижней части окна отображается подсказка, что означает каждый цвет.

Плюсы:

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

Минусы:

  • ограниченное количество форматов — работает только с .txt и .mxl. Нельзя вставить Excel-файлы;
  • невозможно сохранить результат сравнения во внешний файл. Даже если скопировать данные и вставить в тот же Excel, цветное выделение различий не перенесется;
  • нет дополнительных формул, например, чтобы вывести сумму по колонке.

Источник: http://career.i-neti.ru/4-sposoba-sravnit-tablitsy/

Сравнение двух таблиц значений запросом в 1С

Задача банальная — сравнить одним действием две табличные части или таблицы значений и вывести различающиеся по количеству позиции. В моем случае я сравниваю заказ поставщику и документ поступления.

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

Сам текст запроса для сравнения табличных частей:

ВЫБРАТЬ
Заказы.Номенклатура,
СУММА(Заказы.Количество) КАК Количество
ПОМЕСТИТЬ Заказы
ИЗ
Документ.ЗаказПоставщику.Товары КАК Заказы
ГДЕ
Заказы.Ссылка = &Заказ

СГРУППИРОВАТЬ ПО
Заказы.Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Поступления.Номенклатура,
СУММА(Поступления.Количество) КАК Количество
ПОМЕСТИТЬ Поступления
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Поступления
ГДЕ
Поступления.Ссылка = &Поступление

СГРУППИРОВАТЬ ПО
Поступления.Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Заказ.Номенклатура КАК Номенклатура,
ЕСТЬNULL(Заказ.Количество, 0) КАК КоличествоЗаказано,
ЕСТЬNULL(Накладная.Количество, 0) КАК КоличествоПоступило
ПОМЕСТИТЬ Итоговая
ИЗ
Заказы КАК Заказ
ЛЕВОЕ СОЕДИНЕНИЕ Поступления КАК Накладная
ПО Заказ.Номенклатура = Накладная.Номенклатура

ВЫБРАТЬ
Накладная.Номенклатура,
ЕСТЬNULL(Заказ.Количество, 0),
ЕСТЬNULL(Накладная.Количество, 0)
ИЗ
Поступления КАК Накладная
ЛЕВОЕ СОЕДИНЕНИЕ Заказы КАК Заказ
ПО (Заказ.Номенклатура = Накладная.Номенклатура)
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Итоговая.Номенклатура,
Итоговая.КоличествоЗаказано,
Итоговая.КоличествоПоступило,
Итоговая.КоличествоЗаказано — Итоговая.КоличествоПоступило КАК КоличествоРазница
ИЗ
Итоговая КАК Итоговая
ГДЕ
Итоговая.КоличествоЗаказано — Итоговая.КоличествоПоступило 0

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

Если Ваша задача — сравнить две таблицы значений, то я рекомендую передать в запрос две таблицы и составить аналогичный запрос.

Если Вы начинаете изучать 1С программирование, рекомендуем наш бесплатный курс (не забудьте подписаться на YouTube — регулярно выходят новые видео):

К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.

Источник

Источник: http://politoff.ru/1s-sravnit-dva-tablitsy-znacheniy/

Быстрое визуальное сравнение двух таблиц значений

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

Т.е., например, число 5 и строка «5» не покажут разницы.

Но для других случаев этот пример будет очень полезен.

p.s. искал такой же вариант на ИС, но, увы, не нашел.

Специальные предложения

f71a8c4e70c0ff03708038e2b0210df8.png

9c2808762ec294cd4c55532520b9c521.png

895fb1e0f7afc3c0ed0d73bf5ee9d9d0.jpg

egais_promo.jpg

b34b292ed32e9501f98cc31df406353e.png

789363929b9f37ddc5641a069a5fe52e.jpg

bd02fb2c01a58fa8781aab89e5238f05.jpg

5b19cd6c4494a88b2abefce64a1b7565.jpg

7729a85c052dd345bb18b2effa797051.jpg

a308aaa602912f33aa6c7ec364d5406f.png

199e2be4fd21dd8f4209d8ec34616c76.png

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

Т.е., например, число 5 и строка «5» не покажут разницы.

А как насчет выводить тип значения в отдельной колонке? Тогда можно и типы сравнивать вашим этим способом)

Обновление 13.11.15 09:39

Код открыт Не указано

7d54eb2e8773a837fad472828cdbbbc7.gif

См. также

Источник: http://politoff.ru/1s-sravnit-dva-tablitsy-znacheniy/

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

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

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

Для наглядности расположим обе таблицы на одном листе.

Формула

=ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0)

берёт наименование товара из второго прайса, ищет его в первом, и в случае удачи извлекает соответствующую цену из первой таблицы. Она будет записана рядом с новой ценой в столбце H. Если поиск завершился неудачей, то есть такого товара ранее не было, то ставим 0. Таким образом, старая и новая цена оказываются рядом, и их легко сравнить простейшей операцией вычитания. Что и сделано в столбце I.

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

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

Разберём действия пошагово. Формула в ячейке J3 ищет наименование товара из первой позиции второй таблицы внутри первой. Если таковое найдено, извлекается соответствующая этому товару старая цена и сразу же сравнивается с новой. Если они одинаковы, то в ячейку записывается пустота «». 

=ЕСЛИ(ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0)=G3;””;ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$18;2;0);0))

Таким образом, в ячейке J3 будет указана старая цена, если ее удастся найти, а также если она не равна новой.

Далее если ячейка J3 не пустая, то в I3 будет указано наименование товара —  

=ЕСЛИ(J3<>””;F3;””)

а в K3 – его новая цена:  

=ЕСЛИ(J3<>””;G3;””)

Ну а далее в L3 просто найдем разность K3-J3.

Таким образом, в отчёте сравнения мы видим только несовпадения значений второй таблицы по сравнению с первой.

И еще один пример, который может быть полезен. Попытаемся сравнить в итоговой таблице оба прайс-листа с эталонным общим списком товаров.

В ячейке B2 запишем формулу

=ЕСЛИ(ЕНД(ВПР(A2;Прайс1!$B$3:$B$19;1;0));”Нет”;ВПР(A2;Прайс1!$B$3:$C$19;2;0))

Так мы выясним, какие цены из второй таблицы встречаются в первой.

Для каждой цены из первого прайса проверяем, совпадает ли она с новыми данными  —

=ЕСЛИ(ЕНД(ВПР(A2;Прайс2!$B$3:$B$22;1;0));”Нет”;ВПР(A2;Прайс2!$B$3:$C$22;2;0))

Эталонный список находится у нас в столбце A. В результате мы получили своего рода сводную таблицу цен – старых и новых.

Еще несколько примеров использования функции ВПР для сравнения таблиц вы можете найти в этой статье.

Источник: http://mister-office.ru/excel/compare-two-sheets.html

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

Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:

  • Выделить столбцы с данными, в которых нужно вычислить совпадения;
  • На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
  • Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
  • После этого в выделенной колонке будут подсвечены цветом совпадения:

поиск и выделение цветом повторяющихся значений в Excel

Поиск и выделение цветом совпадающих строк в Excel

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

поиск и подсветка дублирующихся строк в Эксель - 1         поиск и подсветка дублирующихся строк в Эксель

В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.

Рассмотрим как найти совпадающие строки в таблице:

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

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

вспомогательная колонка для поиска дублирующихся строк в Excel

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

  • Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15);
  • На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
  • Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
  • После этого в выделенной колонке будут подсвечены дублирующиеся строки:

поиск дубликатов строк в Эксель

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого сделаем следующее:

  • Так же как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:

=A2&B2&C2&D2

Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

вспомогательная колонка для поиска дублирующихся строк в Excel

  • Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15;
  • Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

создать правило условия форматирования в excel

  • В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

новая формула в условном форматировании

  • Не забудьте задать формат найденных дублированных строк.

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

дубликаты строк в excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Пусть Excel сделает тяжелую работу

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

,

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

У вас есть совет по сравнению документов в Excel? Или вам нужна помощь с процессами, описанными в этом руководстве? В любом случае, почему бы не присоединиться к беседе в разделе комментариев ниже?

Сортировка таблицы значений

Для сортировки таблицы значений используется метод Сортировать:

//по возрастанию цены

ТаблицаДокументов.Сортировать(“Цена”);

//по убыванию цены

ТаблицаДокументов.Сортировать(“Цена Убыв”);

//сначала по возрастанию цены, потом по возрастанию наименованию товара

ТаблицаДокументов.Сортировать(“Цена, Товар”);

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

Sub Find_Matches()

Dim CompareRange AsVariant,xAsVariant,yAsVariant

‘ Установка переменной CompareRange равной сравниваемому диапазону

Set CompareRange = Range(“B1:B11”)

Еслисравниваемыйдиапазоннаходитсянадругомлистеиликниге,

‘ используйте следующий синтаксис

Set CompareRange=Workbooks(“Книга2”)._

‘   Worksheets(“Лист2”).Range(“B1:B11”)

‘ Сравнение каждого элемента в выделенном диапазоне с каждым элементом

переменнойCompareRange

ForEachxInSelection

ForEachyInCompareRange

Ifx=yThenx.Offset(0,2)=x

Nexty

Nextx

EndSub

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

     Для использования вашего макроса, вы переходите на ваш рабочий лист, выделяете диапазон (с примера видно, это A1:A11) и нажимаем горячее сочетание клавиш Alt+F8. В новом диалоговом окне выбираете ваш макрос Find_similar и выполняете его.

Источник: http://topexcel.ru/8-sposobov-kak-sravnit-dve-tablicy-v-excel/

Индексы таблицы значений

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

//добавление индексов

ТаблицаДокументов.Индексы.Добавить(“Товар”);

ТаблицаДокументов.Индексы.Добавить(“Сумма”);

//будут созданы 2 индекса

//при поиске по колонке Товар или по колонке Сумма

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

//если выполнить поиск сразу по двум колонкам

//методом НайтиСтроки, то индекс не будет использоваться

//Для этого нужен составной индекс

ТаблицаДокументов.Индексы.Добавить(“Товар, Сумма”);

Подписывайтесь на группу

ВКонтакте

, где я публикую ссылки на новые статьи с данного сайта, а также интересные материалы с других ресурсов по программированию в 1С

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...