Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Сравнение двух столбцов с выборкой совпадений и различий - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сравнение двух столбцов с выборкой совпадений и различий (Формулы)
Сравнение двух столбцов с выборкой совпадений и различий
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 столбцы соответственно, без пробелов.


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

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


Спасибо.
Автор - AlekseySpoil
Дата добавления - 08.11.2013 в 19:38
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.xls (44.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата 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)
Автор - 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,

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

Спасибо.
Автор - AlekseySpoil
Дата добавления - 11.11.2013 в 17:27
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, 11.11.2013 в 17:27, в сообщении № 4
Не смог подобрать нужное число
Сравните D$5:D$17 и СТРОКА($1:$13), G$5:G$14 и СТРОКА($1:$10). Ни о чем не говорит?
Автор - Serge_007
Дата добавления - 11.11.2013 в 20:24
AlekseySpoil Дата: Вторник, 12.11.2013, 11:40 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Привет Serge_007.

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

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

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

Спасибо.
 
Ответить
СообщениеПривет Serge_007.

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

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

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

Спасибо.
Автор - AlekseySpoil
Дата добавления - 12.11.2013 в 11:40
Pelena Дата: Вторник, 12.11.2013, 12:03 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19226
Репутация: 4430 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата AlekseySpoil, 12.11.2013 в 11:40, в сообщении № 6
чем отличаются формулы с вводом через Ctrl+Shift+Enter

Это так называемые формулы массива
http://www.planetaexcel.ru/techniques/2/91/

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата AlekseySpoil, 12.11.2013 в 11:40, в сообщении № 6
чем отличаются формулы с вводом через Ctrl+Shift+Enter

Это так называемые формулы массива
http://www.planetaexcel.ru/techniques/2/91/
Автор - Pelena
Дата добавления - 12.11.2013 в 12:03
AlekseySpoil Дата: Вторник, 12.11.2013, 15:46 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Pelena, спасибо.
 
Ответить
СообщениеPelena, спасибо.
Автор - AlekseySpoil
Дата добавления - 12.11.2013 в 15:46
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сравнение двух столбцов с выборкой совпадений и различий (Формулы)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!