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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сравнение двух списков без учёта порядка (Формулы/Formulas)
Сравнение двух списков без учёта порядка
Mishka1980 Дата: Вторник, 17.02.2015, 11:38 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Нужно сравнить два списка значений без учёта порядка.

Для списков, в которых нет повторяющихся значений, подходит формула: =СУММ(ЕСЛИ(ТРАНСП(Диапазон1)=Диапазон2;1;0))
Формула выводит количество совпадающих значений.

Но, если хотя бы в одном из списков есть повторяющиеся значения, формула даёт неправильный результат.
Пожалуйста, помогите поправить формулу, чтобы она давала правильный результат в этом случае. Количество значений в двух списках может быть разное.
К сообщению приложен файл: -_____.xlsx (10.5 Kb)
 
Ответить
СообщениеНужно сравнить два списка значений без учёта порядка.

Для списков, в которых нет повторяющихся значений, подходит формула: =СУММ(ЕСЛИ(ТРАНСП(Диапазон1)=Диапазон2;1;0))
Формула выводит количество совпадающих значений.

Но, если хотя бы в одном из списков есть повторяющиеся значения, формула даёт неправильный результат.
Пожалуйста, помогите поправить формулу, чтобы она давала правильный результат в этом случае. Количество значений в двух списках может быть разное.
Автор - Mishka1980
Дата добавления - 17.02.2015 в 11:38
_Boroda_ Дата: Вторник, 17.02.2015, 11:46 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16910
Репутация: 6616 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСКПОЗ(D19:D25;B19:B25;)))

D19:D25 - какой диапазон проверяем
B19:B25 - где проверяем
К сообщению приложен файл: 684257_1.xlsx (10.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСКПОЗ(D19:D25;B19:B25;)))

D19:D25 - какой диапазон проверяем
B19:B25 - где проверяем
Автор - _Boroda_
Дата добавления - 17.02.2015 в 11:46
Mishka1980 Дата: Вторник, 17.02.2015, 12:13 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, спасибо, но нет.

Например, если удалить 3 значения из 1-ого списка, то результат должен быть равен 4. Так как у списков осталось 4 пересечения.
К сообщению приложен файл: 684257_1-.xlsx (10.6 Kb)
 
Ответить
Сообщение_Boroda_, спасибо, но нет.

Например, если удалить 3 значения из 1-ого списка, то результат должен быть равен 4. Так как у списков осталось 4 пересечения.
Автор - Mishka1980
Дата добавления - 17.02.2015 в 12:13
slAvIk159 Дата: Вторник, 17.02.2015, 12:24 | Сообщение № 4
Группа: Проверенные
Ранг: Участник
Сообщений: 83
Репутация: 13 ±
Замечаний: 0% ±

Excel 2013
Код
=СУММ(ЕСЛИ(ПОИСКПОЗ(B20:B23;D19:D25;0)>0;1;0))

Mishka1980, а так?
К сообщению приложен файл: Mishka1980.xlsx (10.6 Kb)


Сообщение отредактировал slAvIk159 - Вторник, 17.02.2015, 12:27
 
Ответить
Сообщение
Код
=СУММ(ЕСЛИ(ПОИСКПОЗ(B20:B23;D19:D25;0)>0;1;0))

Mishka1980, а так?
Автор - slAvIk159
Дата добавления - 17.02.2015 в 12:24
Mishka1980 Дата: Вторник, 17.02.2015, 13:11 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
slAvIk159, спасибо, вроде бы.

Но не подходит мне, так как если удалить или изменить одно из значений, выдаётся результат Н/Д. Или если изменить один из диапазонов, также Н/Д.
К сообщению приложен файл: Mishka1980-.xlsx (12.8 Kb)
 
Ответить
СообщениеslAvIk159, спасибо, вроде бы.

Но не подходит мне, так как если удалить или изменить одно из значений, выдаётся результат Н/Д. Или если изменить один из диапазонов, также Н/Д.
Автор - Mishka1980
Дата добавления - 17.02.2015 в 13:11
buchlotnik Дата: Вторник, 17.02.2015, 13:20 | Сообщение № 6
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
я, конечно, накрутил, но посмотрите
Код
=СУММПРОИЗВ(--(ЕСЛИОШИБКА(ПОИСКПОЗ($B$20:$B$23;$B$20:$B$23;0);0)=СТРОКА(1:4));ЕСЛИ(СЧЁТЕСЛИ($B$20:$B$23;$B$20:$B$23)>=СЧЁТЕСЛИ($D$19:$D$25;$B$20:$B$23);СЧЁТЕСЛИ($D$19:$D$25;$B$20:$B$23);СЧЁТЕСЛИ($B$20:$B$23;$B$20:$B$23)))
массивка
смысл формулы - получаем перечень уникальных значений из списка 1 и суммируем их количества по списку 1 или по списку 2 в зависимости от того, где меньше

UPD если число строк постоянно меняется так будет лучше:
Код
=СУММПРОИЗВ(--(ЕСЛИОШИБКА(ПОИСКПОЗ($B$20:$B$23;$B$20:$B$23;0);0)=СТРОКА(СМЕЩ(A1;0;0;СЧЁТЗ($B$20:$B$23);1)));ЕСЛИ(СЧЁТЕСЛИ($B$20:$B$23;$B$20:$B$23)>=СЧЁТЕСЛИ($D$19:$D$25;$B$20:$B$23);СЧЁТЕСЛИ($D$19:$D$25;$B$20:$B$23);СЧЁТЕСЛИ($B$20:$B$23;$B$20:$B$23)))
К сообщению приложен файл: _684257_1-.xlsx (10.9 Kb)


Сообщение отредактировал buchlotnik - Вторник, 17.02.2015, 13:42
 
Ответить
Сообщениея, конечно, накрутил, но посмотрите
Код
=СУММПРОИЗВ(--(ЕСЛИОШИБКА(ПОИСКПОЗ($B$20:$B$23;$B$20:$B$23;0);0)=СТРОКА(1:4));ЕСЛИ(СЧЁТЕСЛИ($B$20:$B$23;$B$20:$B$23)>=СЧЁТЕСЛИ($D$19:$D$25;$B$20:$B$23);СЧЁТЕСЛИ($D$19:$D$25;$B$20:$B$23);СЧЁТЕСЛИ($B$20:$B$23;$B$20:$B$23)))
массивка
смысл формулы - получаем перечень уникальных значений из списка 1 и суммируем их количества по списку 1 или по списку 2 в зависимости от того, где меньше

UPD если число строк постоянно меняется так будет лучше:
Код
=СУММПРОИЗВ(--(ЕСЛИОШИБКА(ПОИСКПОЗ($B$20:$B$23;$B$20:$B$23;0);0)=СТРОКА(СМЕЩ(A1;0;0;СЧЁТЗ($B$20:$B$23);1)));ЕСЛИ(СЧЁТЕСЛИ($B$20:$B$23;$B$20:$B$23)>=СЧЁТЕСЛИ($D$19:$D$25;$B$20:$B$23);СЧЁТЕСЛИ($D$19:$D$25;$B$20:$B$23);СЧЁТЕСЛИ($B$20:$B$23;$B$20:$B$23)))
Автор - buchlotnik
Дата добавления - 17.02.2015 в 13:20
slAvIk159 Дата: Вторник, 17.02.2015, 13:56 | Сообщение № 7
Группа: Проверенные
Ранг: Участник
Сообщений: 83
Репутация: 13 ±
Замечаний: 0% ±

Excel 2013
а ещё можна через VBA:
[vba]
Код

Function count_found(diap1, diap2)
     Dim d1 As Range
     Dim d2 As Range
     Dim count1 As Integer
     Dim count2 As Integer
     count2 = 0
     For Each d1 In diap1
         If d1.Value <> "" Then
             count1 = 0
             For Each d2 In diap2
                 If d1.Value = d2.Value Then
                     count1 = count1 + 1
                 End If
             Next
             If count1 > 0 Then
             count2 = count2 + 1
             End If
         End If
     Next
     count_found = count2
End Function
[/vba]
К сообщению приложен файл: Mishka1980-2.xlsm (19.9 Kb)
 
Ответить
Сообщениеа ещё можна через VBA:
[vba]
Код

Function count_found(diap1, diap2)
     Dim d1 As Range
     Dim d2 As Range
     Dim count1 As Integer
     Dim count2 As Integer
     count2 = 0
     For Each d1 In diap1
         If d1.Value <> "" Then
             count1 = 0
             For Each d2 In diap2
                 If d1.Value = d2.Value Then
                     count1 = count1 + 1
                 End If
             Next
             If count1 > 0 Then
             count2 = count2 + 1
             End If
         End If
     Next
     count_found = count2
End Function
[/vba]
Автор - slAvIk159
Дата добавления - 17.02.2015 в 13:56
Mishka1980 Дата: Вторник, 17.02.2015, 14:43 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
slAvIk159, buchlotnik, спасибо
 
Ответить
СообщениеslAvIk159, buchlotnik, спасибо
Автор - Mishka1980
Дата добавления - 17.02.2015 в 14:43
_Boroda_ Дата: Вторник, 17.02.2015, 15:48 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16910
Репутация: 6616 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Предлагаю такой вариант:
Код
=СУММ(СЧЁТЕСЛИ(B5:B11;ЕСЛИ(ПОИСКПОЗ(D5:D11;D5:D11;)=СТРОКА(D$1:D$7);D5:D11))-ТЕКСТ(СЧЁТЕСЛИ(B5:B11;ЕСЛИ(ПОИСКПОЗ(D5:D11;D5:D11;)=СТРОКА(D$1:D$7);D5:D11))-СЧЁТЕСЛИ(D5:D11;ЕСЛИ(ПОИСКПОЗ(D5:D11;D5:D11;)=СТРОКА(D$1:D$7);D5:D11));"0;\0"))

Не зависит от количества значений в B5:B11 и D5:D11
К сообщению приложен файл: Mishka1980-1.xlsx (13.1 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПредлагаю такой вариант:
Код
=СУММ(СЧЁТЕСЛИ(B5:B11;ЕСЛИ(ПОИСКПОЗ(D5:D11;D5:D11;)=СТРОКА(D$1:D$7);D5:D11))-ТЕКСТ(СЧЁТЕСЛИ(B5:B11;ЕСЛИ(ПОИСКПОЗ(D5:D11;D5:D11;)=СТРОКА(D$1:D$7);D5:D11))-СЧЁТЕСЛИ(D5:D11;ЕСЛИ(ПОИСКПОЗ(D5:D11;D5:D11;)=СТРОКА(D$1:D$7);D5:D11));"0;\0"))

Не зависит от количества значений в B5:B11 и D5:D11
Автор - _Boroda_
Дата добавления - 17.02.2015 в 15:48
Mishka1980 Дата: Среда, 18.02.2015, 09:36 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, спасибо, тоже подходит
 
Ответить
Сообщение_Boroda_, спасибо, тоже подходит
Автор - Mishka1980
Дата добавления - 18.02.2015 в 09:36
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сравнение двух списков без учёта порядка (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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