Помощь Excel
#1 OFFLINE
Posted 18 June 2015 - 12:03
Суть вопроса: Реально ли воплотить в жизнь такую штуку:
Есть 2 числовых столбца. Необходимо посчитать сумму 2 чисел.
1 число - наибольшее в левом столбце ( в нашем случае 201.1)
2 число - наибольшее в правом столбце, но находящееся уже по уровню ниже 201.1( в нашем случае 88.55)
Тоже самое проделать наоборот: наиб-е в правом столбце+ наибол-е из левого, но на уровень ниже.
[img]radikal.ru/fp/6c6feebe40b44eb980201cba51f05066[/img]
Если это реально воплотить в жизнь, прошу оказать посильную помощь. А то по работе иногда приходиться проштудировать столбцы с более чем 200-ми чисел.
#2 OFFLINE
Posted 18 June 2015 - 14:50
допустим вы эти мах1 мах2 поместили над исходными столбиками (хотя можно где угодно). Тогда верните в вспомогательные только те числа которые меньше вашего максимума а если больше или равно верните 0 и уже в этих столбиках найдите мах . для первого вспомогательного столбика в условии будет мах2 а для второго мах1
#3 OFFLINE
Posted 18 June 2015 - 17:05
Bambuk, on 18 June 2015 - 14:50, said:
допустим вы эти мах1 мах2 поместили над исходными столбиками (хотя можно где угодно). Тогда верните в вспомогательные только те числа которые меньше вашего максимума а если больше или равно верните 0 и уже в этих столбиках найдите мах . для первого вспомогательного столбика в условии будет мах2 а для второго мах1
Решение нашел, но Ваше понять не могу. Поможете? )
Если мы будем возвращать только числа меньше максимума, тогда у нас в вспомогательных столбцах будут все те же числа кроме максимума. Дальше если ищем максимумы в вспомогательных, то это будут текущие максимумы в вспомогательных. А что дальше?
Edited by tester.nt, 18 June 2015 - 17:10.
#4 OFFLINE
Posted 18 June 2015 - 18:05
tester.nt, on 18 June 2015 - 17:05, said:
Если мы будем возвращать только числа меньше максимума, тогда у нас в вспомогательных столбцах будут все те же числа кроме максимума. Дальше если ищем максимумы в вспомогательных, то это будут текущие максимумы в вспомогательных. А что дальше?
тогда во вспомогательном 2 останутся числа меньше мах1 если мах 1 =200 а во втором столбце числа 300 350 201 150 75 90 ....то вернутся выделенные а остальные 0 тогда тут 150 которое меньше мах1 и которое мах в остатке который образован условием <мах1.
Edited by Bambuk, 18 June 2015 - 18:14.
#5 OFFLINE
Posted 18 June 2015 - 18:18
Bambuk, on 18 June 2015 - 18:05, said:
тогда во вспомогательном 2 останутся числа меньше мах1 если мах 1 =200 а во втором столбце числа 300 350 201 150 75 90 ....то вернутся выделенные а остальные 0 тогда тут 150 которое меньше мах1 и которое мах в остатке который образован условием <мах1.
По-моему это Вы не правильно поняли условие задачи. Автор говорит "по уровню ниже", имея ввиду позицию на листе, а не числовое значение. Смотрите картинку по ссылке.
Edited by tester.nt, 18 June 2015 - 18:19.
#6 OFFLINE
Posted 18 June 2015 - 18:41
Screen Shot 2015-06-18 at 20.37.07.png 26.41K 1 downloads Screen Shot 2015-06-18 at 20.34.45.png 65.57K 0 downloads
#7 OFFLINE
Posted 18 June 2015 - 18:54
Если позиции то по смыслу тоже самое. надо просто ввести столбик с номерами записей 1,2,3,4... а потом по мах1 функцией ПОИСКПОЗ найти положение этого мах1 в таблице и вернуть во второй вспомогательный столбец записи второго столбца ниже этой позиции (в условии просто сравнивать с номерами которые мы ввели, а адрес яч с позицией и функцией поискпоз надо просто писать как абсолютный --тогда тащить можно просто формулу условия возвращающего значения).
Можно и через индексы делать... да я думаю автор там сам додумает....
Edited by Bambuk, 18 June 2015 - 19:02.
#8 OFFLINE
Posted 18 June 2015 - 19:20
Bambuk, on 18 June 2015 - 18:54, said:
Если позиции то по смыслу тоже самое. надо просто ввести столбик с номерами записей 1,2,3,4... а потом по мах1 функцией ПОИСКПОЗ найти положение этого мах1 в таблице и вернуть во второй вспомогательный столбец записи второго столбца ниже этой позиции (в условии просто сравнивать с номерами которые мы ввели, а адрес яч с позицией и функцией поискпоз надо просто писать как абсолютный --тогда тащить можно просто формулу условия возвращающего значения).
Можно и через индексы делать... да я думаю автор там сам додумает....
Да, по смыслу то же самое
Конечно, мне такое решение кажется немного избыточным. Но если не делать доп.столбцов, то придется немного поизвращаться.
#9 OFFLINE
Posted 18 June 2015 - 19:30
tester.nt, on 18 June 2015 - 19:20, said:
Конечно, мне такое решение кажется немного избыточным. Но если не делать доп.столбцов, то придется немного поизвращаться.
вот тут вчера делал для коней потом выяснилось что это вообще не надо
=ЕСЛИ(ЕОШИБКА(ПОИСК($W$23;I7;1));"";ПОДСТАВИТЬ(I7;$W$23;$V$23))&ЕСЛИ(ЕОШИБКА(ПОИСК($W$24;I7;1));"";ПОДСТАВИТЬ(I7;$W$24;$V$24))&ЕСЛИ(ЕОШИБКА (ПОИСК($W$25;I7;1));"";ПОДСТАВИТЬ(I7;$W$25;$V$25))&ЕСЛИ(ЕОШИБКА(ПОИСК($W$26;I7;1));"";ПОДСТАВИТЬ(I7;$W$26;$V$26))&ЕСЛИ(ЕОШИБКА(ПОИСК($W$27;I7;1)) ;"";ПОДСТАВИТЬ(I7;$W$27;$V$27))&ЕСЛИ(ЕОШИБКА(ПОИСК($W$28;I7;1));"";ПОДСТАВИТЬ(I7;$W$28;$V$28))&ЕСЛИ(ЕОШИБКА(ПОИСК($W$29;I7;1));"";ПОДСТАВИТЬ (I7;$W$29;$V$29))&ЕСЛИ(ЕОШИБКА(ПОИСК($W$30;I7;1));"";ПОДСТАВИТЬ(I7;$W$30;$V$30))&ЕСЛИ(ЕОШИБКА(ПОИСК($W$31;I7;1));"";ПОДСТАВИТЬ(I7;$W$31;$V$31))& ЕСЛИ(ЕОШИБКА(ПОИСК($W$32;I7;1));"";ПОДСТАВИТЬ(I7;$W$32;$V$32))&ЕСЛИ(ЕОШИБКА(ПОИСК($W$33;I7;1));"";ПОДСТАВИТЬ(I7;$W$33;$V$33))&ЕСЛИ(ЕОШИБКА (ПОИСК($W$34;I7;1));"";ПОДСТАВИТЬ(I7;$W$34;$V$34))
Edited by Bambuk, 18 June 2015 - 19:38.
#10 OFFLINE
Posted 18 June 2015 - 19:45
Bambuk, on 18 June 2015 - 19:30, said:
А вообще-то с Вашей подачи я начал в VBA вникать. Как оказалось, полезная штука! Если писать функцию, то в нее надо передать диапазон из двух колонок, а все остальное уже внутри можно высчитать (я про данную задачу).
Edited by tester.nt, 18 June 2015 - 19:46.
#11 OFFLINE
Posted 19 June 2015 - 07:28
tester.nt, on 18 June 2015 - 18:41, said:
Тут еще эти пропуски, блин. Вообщем голова закипела..
А вообще ребят спасибо, что откликнулись....
Ну и плюс кол-во показателей не ростоянно. От 130 до 380(мах что было за все время)
#12 OFFLINE
Posted 19 June 2015 - 08:44
tester.nt, on 18 June 2015 - 19:45, said:
2015-06-19_102942.png 7.15K 1 downloads
сам функция на javascript (думаю не сложно переписать на бэйсике, Number() - конвертит строку в число):
function Scarabey8386(col1, col2) { var max1 = 0; for (var i1 = 1; i1 < col1.length; ++i1) { if (Number(col1[i1]) > Number(col1[max1])) { max1 = i1; } } var max2 = max1 + 1; for (var i2 = max2 + 1; i2 < col2.length; ++i2) { if (Number(col2[i2]) > Number(col2[max2])) { max2 = i2; } } return Number(col1[max1]) + Number(col2[max2]); }
#13 OFFLINE
Posted 19 June 2015 - 09:49
Все, что Вам осталось сделать - присвоить имена диапазонам, где будут храниться данные ColA и ColB. Ну и без ошибок переписать формулы. Возьмите диапазон на 1000 ячеек вниз, чтобы с запасом был. А потом только вставляете данные и все. Или же переносите формулы на другие листы.
Screen Shot 2015-06-19 at 11.43.04.png 16.53K 2 downloads
Screen Shot 2015-06-19 at 11.43.26.png 33.42K 2 downloads
Edited by tester.nt, 19 June 2015 - 09:50.
#14 OFFLINE
Posted 19 June 2015 - 10:28
Ниже решение без привязки к столбцам (только к диапазонам ColA и ColB):
=MAX(ColA)+MAX(INDIRECT("R"&MATCH(MAX(ColA),ColA,0)+ROW(ColB)-1&"C"&COLUMN(ColB),0):INDIRECT("R"&ROWS(ColB)&"C"&COLUMN(ColB),0))
=MAX(ColB)+MAX(INDIRECT("R"&MATCH(MAX(ColB),ColB,0)+ROW(ColA)-1&"C"&COLUMN(ColA),0):INDIRECT("R"&ROWS(ColA)&"C"&COLUMN(ColA),0))
#15 OFFLINE
Posted 19 June 2015 - 17:17
Это шедевр. Ты спас мои глаза. Теперь эти цифры перестанут рябить в моих зенках Жму руку. Спасибо!!!
Всем спасибо за участие!