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

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Сравнение двух столбцов с выборкой совпадений и различий (Формулы)

AlekseySpoil

Дата: Четверг, 07.11.2013, 19:12 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Здравствуйте.

У меня появился довольно серьезный вопрос, который я не смог решить самостоятельно. Стоит MS Excel 2003.

У меня есть 2 Excel файла. В каждом из них первой строкой идет заголовок, который содержит название полей. Вторая, третья и далее строки - значения по полям. Мне надо чтобы в третьем файле бралась строка 1 из первого файла (названия полей), делалась столбцом, отсортированным по алфавиту. Потом в этот же третий файл делалось аналогичное действие для второго файла. Должно выйти 2 столбца отсортированных по алфавиту. В этих столбцах могут быть совпадения в названиях, но не на одной и той же строке а вообще в столбцах, так как они будут разные по размеру (тут обязательно, чтобы значения проверялись и на регистр, т.е. важны только полностью совпавшие имена). Все совпадения мне надо вынести в отдельный столбец. Далее значения из 1 и 2 столбцов которые не нашли аналогов друг в друге были выведены в 4 и 5 столбцы соответственно, без пробелов.

В приложении более подробно по примеру.

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

Спасибо.

К сообщению приложен файл: Book1.xls (20.0 Kb)

Сообщение отредактировал AlekseySpoil - Четверг, 07.11.2013, 19:12

Ответить

СообщениеЗдравствуйте.

У меня появился довольно серьезный вопрос, который я не смог решить самостоятельно. Стоит MS Excel 2003.

У меня есть 2 Excel файла. В каждом из них первой строкой идет заголовок, который содержит название полей. Вторая, третья и далее строки - значения по полям. Мне надо чтобы в третьем файле бралась строка 1 из первого файла (названия полей), делалась столбцом, отсортированным по алфавиту. Потом в этот же третий файл делалось аналогичное действие для второго файла. Должно выйти 2 столбца отсортированных по алфавиту. В этих столбцах могут быть совпадения в названиях, но не на одной и той же строке а вообще в столбцах, так как они будут разные по размеру (тут обязательно, чтобы значения проверялись и на регистр, т.е. важны только полностью совпавшие имена). Все совпадения мне надо вынести в отдельный столбец. Далее значения из 1 и 2 столбцов которые не нашли аналогов друг в друге были выведены в 4 и 5 столбцы соответственно, без пробелов.

В приложении более подробно по примеру.

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

Спасибо.

Автор - AlekseySpoil
Дата добавления - 07.11.2013 в 19:12

AlekseySpoil

Дата: Пятница, 08.11.2013, 19:38 | Сообщение № 2

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Удалось самостоятельно сделать некоторую часть, которая идет до сравнения.
Очень интересует как сделать описанное сравнение.

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

Все совпадения мне надо вынести в отдельный столбец. Далее значения из 1 и 2 столбцов которые не нашли аналогов друг в друге были выведены в 4 и 5 столбцы соответственно, без пробелов.

Спасибо.

Ответить

Serge_007

Дата: Суббота, 09.11.2013, 01:01 | Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

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

Код

=ИНДЕКС('так будет выглядеть первый файл'!A$1:M$1;;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ('так будет выглядеть первый файл'!A$1:M$1;"<="&'так будет выглядеть первый файл'!A$1:M$1);СУММ(1;СЧЁТЕСЛИ('так будет выглядеть первый файл'!A$1:M$1;D$4:D4);));СЧЁТЕСЛИ('так будет выглядеть первый файл'!A$1:M$1;"<="&'так будет выглядеть первый файл'!A$1:M$1);))

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

в этот же третий файл делалось аналогичное действие для второго файла

Код

=ИНДЕКС('так будет выглядеть второй файл'!A$1:J$1;;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ('так будет выглядеть второй файл'!A$1:J$1;"<="&'так будет выглядеть второй файл'!A$1:J$1);СУММ(1;СЧЁТЕСЛИ('так будет выглядеть второй файл'!A$1:J$1;G$4:G4);));СЧЁТЕСЛИ('так будет выглядеть второй файл'!A$1:J$1;"<="&'так будет выглядеть второй файл'!A$1:J$1);))

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

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

Код

=ИНДЕКС(D$5:D$17;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(D$5:D$17;G$5:G$14;)*ПОИСКПОЗ(КОДСИМВ(D$5:D$17);КОДСИМВ(G$5:G$14);));СТРОКА($1:$13));СТРОКА(A1)))

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

значения из 1 и 2 столбцов которые не нашли аналогов друг в друге были выведены в 4 и 5 столбцы соответственно

Код

=ИНДЕКС(D$5:D$17;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(D$5:D$17;J$5:J$10;));СТРОКА($1:$13));СТРОКА(A1)))

Код

=ИНДЕКС(G$5:G$14;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(G$5:G$14;J$5:J$10;));СТРОКА($1:$10));СТРОКА(A1)))

Все формулы массива (вводятся нажатием клавиш Ctrl+Shift+Enter)

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

Код

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

в этот же третий файл делалось аналогичное действие для второго файла

Код

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

Код

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

значения из 1 и 2 столбцов которые не нашли аналогов друг в друге были выведены в 4 и 5 столбцы соответственно

Код

=ИНДЕКС(D$5:D$17;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(D$5:D$17;J$5:J$10;));СТРОКА($1:$13));СТРОКА(A1)))

Код

=ИНДЕКС(G$5:G$14;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(G$5:G$14;J$5:J$10;));СТРОКА($1:$10));СТРОКА(A1)))

Все формулы массива (вводятся нажатием клавиш Ctrl+Shift+Enter)Serge_007

К сообщению приложен файл: AlekseySpoil.xls (44.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Сообщение

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

Код

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

в этот же третий файл делалось аналогичное действие для второго файла

Код

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

Код

Цитата AlekseySpoil, 07.11.2013 в 19:12, в сообщении № 1

значения из 1 и 2 столбцов которые не нашли аналогов друг в друге были выведены в 4 и 5 столбцы соответственно

Код

=ИНДЕКС(D$5:D$17;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(D$5:D$17;J$5:J$10;));СТРОКА($1:$13));СТРОКА(A1)))

Код

=ИНДЕКС(G$5:G$14;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(G$5:G$14;J$5:J$10;));СТРОКА($1:$10));СТРОКА(A1)))

Все формулы массива (вводятся нажатием клавиш Ctrl+Shift+Enter)

Автор - Serge_007
Дата добавления - 09.11.2013 в 01:01

AlekseySpoil

Дата: Понедельник, 11.11.2013, 17:27 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Serge_007,

Огромное спасибо, но возникла некоторая проблема с СТРОКА($1:$13));СТРОКА(A1). Не смог подобрать нужное число чтобы формула работала (не понял смысла диапазона и как выбрать конечное значение). То есть вышло так: сравнение работает на диапазоне с концом в 13, а также вплоть до 95. Для 96 уже не хочет. А выборка оставшихся полей не работает ни на одном значении для моих данных. Причем меняю только диапазоны для сравнения в формуле. Ввожу формулы нажатием Ctrl+Shift+Enter - тоже так и не понял что это за фишка и для чего нужна, но понял только что без нее не работает)

Спасибо.

Ответить

Serge_007

Дата: Понедельник, 11.11.2013, 20:24 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата AlekseySpoil, 11.11.2013 в 17:27, в сообщении № 4

Не смог подобрать нужное число

Сравните D$5:D$17 и СТРОКА($1:$13), G$5:G$14 и СТРОКА($1:$10). Ни о чем не говорит?

ЮMoney:41001419691823 | WMR:126292472390

Ответить

AlekseySpoil

Дата: Вторник, 12.11.2013, 11:40 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Привет Serge_007.

Еще раз огромное спасибо за подсказку. Все получилось.

Не могли бы вы только объяснить чем отличаются формулы с вводом через Ctrl+Shift+Enter.

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

Спасибо.