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

Вход

Регистрация

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

 

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

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

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


Сообщение отредактировал champ - Среда, 17.08.2016, 17:43
 
Ответить
СообщениеЗдравствуйте,
прошу помочь необходимо вставить в ячейки I9:T12 значения из ячеек С1:Е5
можно 3 формулами

Автор - champ
Дата добавления - 17.08.2016 в 17:42
Pelena Дата: Среда, 17.08.2016, 21:00 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10979
Репутация: 2454 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Здравствуйте.
Формула, конечно, монструозная получилась, но, вроде, считает
Код
=ЕСЛИ($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(12Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 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
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10979
Репутация: 2454 ±
Замечаний: 0% ±

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


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеНе совсем поняла, а зачем разные, если можно одной?

Автор - Pelena
Дата добавления - 17.08.2016 в 23:43
krosav4ig Дата: Четверг, 18.08.2016, 02:55 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1465
Репутация: 596 ±
Замечаний: 0% ±

Excel 2007, 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(11Kb)


(_)Õvõ(_)

Сообщение отредактировал 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

Использовал идеи Андрея. Массивная формула
Код
=ЕСЛИОШИБКА(ВПР("*"&$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(34Kb)


Номер мобильного модема (без голосовой связи)
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 должно быть пусто.
Не совсем поняла, а зачем разные, если можно одной?

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

тремя формулами возможно короче каждая формула будет, но вы круто так сделали одной формулой меня впечатлило...

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

если пустые ячейки в столбцах С и Е или нет позиций, например 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 МегаФон, Московский регион.
 
Ответить
Сообщение
если пустые ячейки в столбцах С и Е или нет позиций, например 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
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10979
Репутация: 2454 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
А моя формула ещё учитывает, что, если А с В сыграли 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)));""))


"Черт возьми, Холмс! Но как??!!"
ЯД 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

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(35Kb)


Номер мобильного модема (без голосовой связи)
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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

тремя формулами возможно короче
Формулы без функции ЕСЛИОШИБКА(), можно дополнить.[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(34Kb)


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


Сообщение отредактировал AlexM - Четверг, 18.08.2016, 17:29
 
Ответить
Сообщение
тремя формулами возможно короче
Формулы без функции ЕСЛИОШИБКА(), можно дополнить.[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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

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


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
ячейки C5:E5 были пустыми изначально
Очистите их, в чем проблема?
ячейки R10, S10, T10, L12, M12, N12 должны быть пустыми
Объясните почему? Если примените ЕСЛИОШИБКА(), то можете сделать #, сейчас там Н/Д (отображается ##)

Автор - AlexM
Дата добавления - 18.08.2016 в 23:40
champ Дата: Пятница, 19.08.2016, 11:05 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

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


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


это условие задачи, в ячейках I9: T12 должны быть данные или пустые ячейки только из C1:E5

Автор - champ
Дата добавления - 19.08.2016 в 11:05
AlexM Дата: Пятница, 19.08.2016, 11:37 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

Последний вариант с тремя формулами. Первая и третья формулы массива, вторая обычная. Ошибка скрывается условным форматированием.[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(33Kb)


Номер мобильного модема (без голосовой связи)
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 из 11
Поиск:

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