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

Вход

Регистрация

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

 

= Мир MS Excel/вычисление значений на пересечении формулой - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » вычисление значений на пересечении формулой (Формулы/Formulas)
вычисление значений на пересечении формулой
champ Дата: Среда, 17.08.2016, 17:42 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте,
прошу помочь необходимо вставить в ячейки I9:T12 значения из ячеек С1:Е5
можно 3 формулами
К сообщению приложен файл: 4426617.xlsx (9.2 Kb)


Сообщение отредактировал champ - Среда, 17.08.2016, 17:43
 
Ответить
СообщениеЗдравствуйте,
прошу помочь необходимо вставить в ячейки I9:T12 значения из ячеек С1:Е5
можно 3 формулами
Автор - champ
Дата добавления - 17.08.2016 в 17:42
Pelena Дата: Среда, 17.08.2016, 21:00 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Формула, конечно, монструозная получилась, но, вроде, считает
Код
=ЕСЛИ($H9=I$8;"#";ЕСЛИОШИБКА(ЕСЛИ(ЕПУСТО(ЕСЛИОШИБКА(ИНДЕКС($C$1:$E$5;ПОИСКПОЗ($H9&I$8;$A$1:$A$5&$B$1:$B$5;0);СЧЁТЕСЛИ($I$8:I8;I$8));ИНДЕКС($C$1:$E$5;ПОИСКПОЗ(I$8&$H9;$A$1:$A$5&$B$1:$B$5;0);СЧЁТЕСЛИ($I$8:I8;I$8))));"";ЕСЛИОШИБКА(ИНДЕКС($C$1:$E$5;ПОИСКПОЗ($H9&I$8;$A$1:$A$5&$B$1:$B$5;0);СЧЁТЕСЛИ($I$8:I8;I$8));ИНДЕКС($C$1:$E$5;ПОИСКПОЗ(I$8&$H9;$A$1:$A$5&$B$1:$B$5;0);4-СЧЁТЕСЛИ($I$8:I8;I$8))));""))
К сообщению приложен файл: 2671176.xlsx (11.7 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Формула, конечно, монструозная получилась, но, вроде, считает
Код
=ЕСЛИ($H9=I$8;"#";ЕСЛИОШИБКА(ЕСЛИ(ЕПУСТО(ЕСЛИОШИБКА(ИНДЕКС($C$1:$E$5;ПОИСКПОЗ($H9&I$8;$A$1:$A$5&$B$1:$B$5;0);СЧЁТЕСЛИ($I$8:I8;I$8));ИНДЕКС($C$1:$E$5;ПОИСКПОЗ(I$8&$H9;$A$1:$A$5&$B$1:$B$5;0);СЧЁТЕСЛИ($I$8:I8;I$8))));"";ЕСЛИОШИБКА(ИНДЕКС($C$1:$E$5;ПОИСКПОЗ($H9&I$8;$A$1:$A$5&$B$1:$B$5;0);СЧЁТЕСЛИ($I$8:I8;I$8));ИНДЕКС($C$1:$E$5;ПОИСКПОЗ(I$8&$H9;$A$1:$A$5&$B$1:$B$5;0);4-СЧЁТЕСЛИ($I$8:I8;I$8))));""))
Автор - Pelena
Дата добавления - 17.08.2016 в 21:00
champ Дата: Среда, 17.08.2016, 22:37 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, спасибо
а можно сделать формулы отдельно для ячеек
I9, I10, I11?
 
Ответить
СообщениеPelena, спасибо
а можно сделать формулы отдельно для ячеек
I9, I10, I11?
Автор - champ
Дата добавления - 17.08.2016 в 22:37
Pelena Дата: Среда, 17.08.2016, 23:43 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Не совсем поняла, а зачем разные, если можно одной?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеНе совсем поняла, а зачем разные, если можно одной?
Автор - Pelena
Дата добавления - 17.08.2016 в 23:43
krosav4ig Дата: Четверг, 18.08.2016, 02:55 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Здравствуйте
намудрил еще 1 вариант
Код
=ЕСЛИОШИБКА(ИНДЕКС($C:$E;ПОИСКПОЗ("*"&$H9&I$8&"*";$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;);ПОИСК(ПРОСМОТР(2;ПОИСК($H9&I$8;$A$1:$A$5&$B$1:$B$5&$A$1:$A$5))&СЧЁТЕСЛИ($I$8:I$8;I$8);"333231112222113")/4);"#")
К сообщению приложен файл: 6377898.xlsx (11.0 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Четверг, 18.08.2016, 03:09
 
Ответить
СообщениеЗдравствуйте
намудрил еще 1 вариант
Код
=ЕСЛИОШИБКА(ИНДЕКС($C:$E;ПОИСКПОЗ("*"&$H9&I$8&"*";$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;);ПОИСК(ПРОСМОТР(2;ПОИСК($H9&I$8;$A$1:$A$5&$B$1:$B$5&$A$1:$A$5))&СЧЁТЕСЛИ($I$8:I$8;I$8);"333231112222113")/4);"#")
Автор - krosav4ig
Дата добавления - 18.08.2016 в 02:55
AlexM Дата: Четверг, 18.08.2016, 10:17 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация: 1114 ±
Замечаний: 0% ±

Excel 2003
Использовал идеи Андрея. Массивная формула
Код
=ЕСЛИОШИБКА(ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;ИНДЕКС($C$1:$E$5;;ОСТАТ(СТОЛБЕЦ(C1);3)+1));2;);"#")
PS. При таких мелких ячейках, как в примере, функция ЕСЛИОШИБКА() не нужна. Значение Н/Д отображается в ячейке символами "##"
К сообщению приложен файл: 4426617_1.xls (33.5 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 18.08.2016, 10:35
 
Ответить
СообщениеИспользовал идеи Андрея. Массивная формула
Код
=ЕСЛИОШИБКА(ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;ИНДЕКС($C$1:$E$5;;ОСТАТ(СТОЛБЕЦ(C1);3)+1));2;);"#")
PS. При таких мелких ячейках, как в примере, функция ЕСЛИОШИБКА() не нужна. Значение Н/Д отображается в ячейке символами "##"
Автор - AlexM
Дата добавления - 18.08.2016 в 10:17
champ Дата: Четверг, 18.08.2016, 10:53 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
krosav4ig, благодарю за решение, но немного не так, если пустые ячейки в столбцах С и Е или нет позиций, например B и D, то и в соответствующих ячейках I9:T12 должно быть пусто.
Цитата Pelena, 17.08.2016 в 23:43, в сообщении № 4
Не совсем поняла, а зачем разные, если можно одной?

тремя формулами возможно короче каждая формула будет, но вы круто так сделали одной формулой меня впечатлило...
 
Ответить
Сообщениеkrosav4ig, благодарю за решение, но немного не так, если пустые ячейки в столбцах С и Е или нет позиций, например B и D, то и в соответствующих ячейках I9:T12 должно быть пусто.
Цитата Pelena, 17.08.2016 в 23:43, в сообщении № 4
Не совсем поняла, а зачем разные, если можно одной?

тремя формулами возможно короче каждая формула будет, но вы круто так сделали одной формулой меня впечатлило...
Автор - champ
Дата добавления - 18.08.2016 в 10:53
AlexM Дата: Четверг, 18.08.2016, 11:30 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация: 1114 ±
Замечаний: 0% ±

Excel 2003
Цитата champ, 18.08.2016 в 10:53, в сообщении № 7
если пустые ячейки в столбцах С и Е или нет позиций, например B и D, то и в соответствующих ячейках I9:T12 должно быть пусто.
Код
=ЕСЛИОШИБКА(ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;ОСТАТ(СТОЛБЕЦ(C1);3)+1));2;);"#")
Формула учитывает ваши пожелания


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата champ, 18.08.2016 в 10:53, в сообщении № 7
если пустые ячейки в столбцах С и Е или нет позиций, например B и D, то и в соответствующих ячейках I9:T12 должно быть пусто.
Код
=ЕСЛИОШИБКА(ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;ОСТАТ(СТОЛБЕЦ(C1);3)+1));2;);"#")
Формула учитывает ваши пожелания
Автор - AlexM
Дата добавления - 18.08.2016 в 11:30
Pelena Дата: Четверг, 18.08.2016, 11:33 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
А моя формула ещё учитывает, что, если А с В сыграли 6:4, то В с А сыграли 4:6 :p
Сократила немного свою формулу
Код
=ЕСЛИ($H9=I$8;"#";ЕСЛИОШИБКА(ЕСЛИОШИБКА(""&ИНДЕКС($C$1:$E$5;ПОИСКПОЗ($H9&I$8;$A$1:$A$5&$B$1:$B$5;0);СЧЁТЕСЛИ($I$8:I8;I$8));""&ИНДЕКС($C$1:$E$5;ПОИСКПОЗ(I$8&$H9;$A$1:$A$5&$B$1:$B$5;0);4-СЧЁТЕСЛИ($I$8:I8;I$8)));""))

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеА моя формула ещё учитывает, что, если А с В сыграли 6:4, то В с А сыграли 4:6 :p
Сократила немного свою формулу
Код
=ЕСЛИ($H9=I$8;"#";ЕСЛИОШИБКА(ЕСЛИОШИБКА(""&ИНДЕКС($C$1:$E$5;ПОИСКПОЗ($H9&I$8;$A$1:$A$5&$B$1:$B$5;0);СЧЁТЕСЛИ($I$8:I8;I$8));""&ИНДЕКС($C$1:$E$5;ПОИСКПОЗ(I$8&$H9;$A$1:$A$5&$B$1:$B$5;0);4-СЧЁТЕСЛИ($I$8:I8;I$8)));""))
Автор - Pelena
Дата добавления - 18.08.2016 в 11:33
AlexM Дата: Четверг, 18.08.2016, 11:37 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация: 1114 ±
Замечаний: 0% ±

Excel 2003
Pelena, Ой, я даже не заметил, что нужно порядок менять. :(
Исправил. Без функции ЕСЛИОШИБКА() формула такая
Код
=ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;ABS(($H9>I$8)*4-(ОСТАТ(СТОЛБЕЦ(C1);3)+1))));2;)
К сообщению приложен файл: 4426617_2.xls (35.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 18.08.2016, 11:51
 
Ответить
СообщениеPelena, Ой, я даже не заметил, что нужно порядок менять. :(
Исправил. Без функции ЕСЛИОШИБКА() формула такая
Код
=ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;ABS(($H9>I$8)*4-(ОСТАТ(СТОЛБЕЦ(C1);3)+1))));2;)
Автор - AlexM
Дата добавления - 18.08.2016 в 11:37
AlexM Дата: Четверг, 18.08.2016, 17:24 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация: 1114 ±
Замечаний: 0% ±

Excel 2003
Цитата champ, 18.08.2016 в 10:53, в сообщении № 7
тремя формулами возможно короче
Формулы без функции ЕСЛИОШИБКА(), можно дополнить.[vba]
Код
=ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;3^($H9>I$8)));2;)
=ВПР("*"&$H9&J$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&$D$1:$D$5);2;)
=ВПР("*"&$H9&K$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;3^($H9<=K$8)));2;)
[/vba]Формулы для I9, J9 и K9. После ввода формул, выделить три ячейки, копировать. Вставить в отстальные тройки ячеек.
К сообщению приложен файл: 4426617_4.xls (33.5 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 18.08.2016, 17:29
 
Ответить
Сообщение
Цитата champ, 18.08.2016 в 10:53, в сообщении № 7
тремя формулами возможно короче
Формулы без функции ЕСЛИОШИБКА(), можно дополнить.[vba]
Код
=ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;3^($H9>I$8)));2;)
=ВПР("*"&$H9&J$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&$D$1:$D$5);2;)
=ВПР("*"&$H9&K$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;3^($H9<=K$8)));2;)
[/vba]Формулы для I9, J9 и K9. После ввода формул, выделить три ячейки, копировать. Вставить в отстальные тройки ячеек.
Автор - AlexM
Дата добавления - 18.08.2016 в 17:24
champ Дата: Четверг, 18.08.2016, 20:38 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
AlexM,
ячейки R10, S10, T10, L12, M12, N12 должны быть пустыми и ячейки C5:E5 были пустыми изначально.
 
Ответить
СообщениеAlexM,
ячейки R10, S10, T10, L12, M12, N12 должны быть пустыми и ячейки C5:E5 были пустыми изначально.
Автор - champ
Дата добавления - 18.08.2016 в 20:38
AlexM Дата: Четверг, 18.08.2016, 23:40 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация: 1114 ±
Замечаний: 0% ±

Excel 2003
Цитата champ, 18.08.2016 в 20:38, в сообщении № 12
ячейки C5:E5 были пустыми изначально
Очистите их, в чем проблема?
Цитата champ, 18.08.2016 в 20:38, в сообщении № 12
ячейки R10, S10, T10, L12, M12, N12 должны быть пустыми
Объясните почему? Если примените ЕСЛИОШИБКА(), то можете сделать #, сейчас там Н/Д (отображается ##)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата champ, 18.08.2016 в 20:38, в сообщении № 12
ячейки C5:E5 были пустыми изначально
Очистите их, в чем проблема?
Цитата champ, 18.08.2016 в 20:38, в сообщении № 12
ячейки R10, S10, T10, L12, M12, N12 должны быть пустыми
Объясните почему? Если примените ЕСЛИОШИБКА(), то можете сделать #, сейчас там Н/Д (отображается ##)
Автор - AlexM
Дата добавления - 18.08.2016 в 23:40
champ Дата: Пятница, 19.08.2016, 11:05 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Цитата AlexM, 18.08.2016 в 23:40, в сообщении № 13
ячейки R10, S10, T10, L12, M12, N12 должны быть пустыми
Объясните почему? Если примените ЕСЛИОШИБКА(), то можете сделать #, сейчас там Н/Д (отображается ##)


это условие задачи, в ячейках I9: T12 должны быть данные или пустые ячейки только из C1:E5
 
Ответить
Сообщение
Цитата AlexM, 18.08.2016 в 23:40, в сообщении № 13
ячейки R10, S10, T10, L12, M12, N12 должны быть пустыми
Объясните почему? Если примените ЕСЛИОШИБКА(), то можете сделать #, сейчас там Н/Д (отображается ##)


это условие задачи, в ячейках I9: T12 должны быть данные или пустые ячейки только из C1:E5
Автор - champ
Дата добавления - 19.08.2016 в 11:05
AlexM Дата: Пятница, 19.08.2016, 11:37 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация: 1114 ±
Замечаний: 0% ±

Excel 2003
champ, В функции ЕСЛИОШИБКА() второй аргумент это то что будет в ячейке если значение вернет ошибку.
Этот аргумент может быть "#", тогда при ошибке в ячейке будет символ # или второй аргумент "", тогда при ошибке в ячейке будет пусто.
Так что вы можете использовать любой символ или пусто, при возникновении ошибки.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениеchamp, В функции ЕСЛИОШИБКА() второй аргумент это то что будет в ячейке если значение вернет ошибку.
Этот аргумент может быть "#", тогда при ошибке в ячейке будет символ # или второй аргумент "", тогда при ошибке в ячейке будет пусто.
Так что вы можете использовать любой символ или пусто, при возникновении ошибки.
Автор - AlexM
Дата добавления - 19.08.2016 в 11:37
champ Дата: Пятница, 19.08.2016, 11:53 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
AlexM, все понятно, благодарю за помощь
 
Ответить
СообщениеAlexM, все понятно, благодарю за помощь
Автор - champ
Дата добавления - 19.08.2016 в 11:53
AlexM Дата: Пятница, 19.08.2016, 12:33 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация: 1114 ±
Замечаний: 0% ±

Excel 2003
Последний вариант с тремя формулами. Первая и третья формулы массива, вторая обычная. Ошибка скрывается условным форматированием.[vba]
Код
=ЕСЛИ($H9=I$8;"#";ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;3^($H9>I$8)));2;))
=ЕСЛИ(ЕЧИСЛО(-I9);":";I9)
=ЕСЛИ($H9=K$8;"#";ВПР("*"&$H9&K$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;3^($H9<=K$8)));2;))
[/vba]Формулы для I9, J9 и K9. После ввода формул, выделить три ячейки, копировать. Вставить в остальные тройки ячеек.
К сообщению приложен файл: 4426617_5.xls (33.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеПоследний вариант с тремя формулами. Первая и третья формулы массива, вторая обычная. Ошибка скрывается условным форматированием.[vba]
Код
=ЕСЛИ($H9=I$8;"#";ВПР("*"&$H9&I$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;3^($H9>I$8)));2;))
=ЕСЛИ(ЕЧИСЛО(-I9);":";I9)
=ЕСЛИ($H9=K$8;"#";ВПР("*"&$H9&K$8&"*";ЕСЛИ({1;0};$A$1:$A$5&$B$1:$B$5&$A$1:$A$5;""&ИНДЕКС($C$1:$E$5;;3^($H9<=K$8)));2;))
[/vba]Формулы для I9, J9 и K9. После ввода формул, выделить три ячейки, копировать. Вставить в остальные тройки ячеек.
Автор - AlexM
Дата добавления - 19.08.2016 в 12:33
Мир MS Excel » Вопросы и решения » Вопросы по Excel » вычисление значений на пересечении формулой (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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