15 ответов в эту тему

[Scarabey8386]

Дорогие друзья, нужна помощь человека владеющего навыками по приручению Excel.
Суть вопроса: Реально ли воплотить в жизнь такую штуку:
Есть 2 числовых столбца. Необходимо посчитать сумму 2 чисел.
1 число - наибольшее в левом столбце ( в нашем случае 201.1)
2 число - наибольшее в правом столбце, но находящееся уже по уровню ниже 201.1( в нашем случае 88.55)

Тоже самое проделать наоборот: наиб-е в правом столбце+ наибол-е из левого, но на уровень ниже.

[img]radikal.ru/fp/6c6feebe40b44eb980201cba51f05066[/img]

Если это реально воплотить в жизнь, прошу оказать посильную помощь. А то по работе иногда приходиться проштудировать столбцы с более чем 200-ми чисел.

[Bambuk]

Вы сделайте два дополнительных столбца сначала. Потом для каждого исходного найдите свои максимумы (в екселе есть функция которая в диапазоне найдёт мах)
допустим вы эти мах1 мах2 поместили над исходными столбиками (хотя можно где угодно). Тогда верните в вспомогательные только те числа которые меньше вашего максимума а если больше или равно верните 0 и уже в этих столбиках найдите мах . для первого вспомогательного столбика в условии будет мах2 а для второго мах1

[tester.nt]

Bambuk, on 18 June 2015 - 14:50, сказал:

Вы сделайте два дополнительных столбца сначала. Потом для каждого исходного найдите свои максимумы (в екселе есть функция которая в диапазоне найдёт мах)
допустим вы эти мах1 мах2 поместили над исходными столбиками (хотя можно где угодно). Тогда верните в вспомогательные только те числа которые меньше вашего максимума а если больше или равно верните 0 и уже в этих столбиках найдите мах . для первого вспомогательного столбика в условии будет мах2 а для второго мах1

Решение нашел, но Ваше понять не могу. Поможете? )

Если мы будем возвращать только числа меньше максимума, тогда у нас в вспомогательных столбцах будут все те же числа кроме максимума. Дальше если ищем максимумы в вспомогательных, то это будут текущие максимумы в вспомогательных. А что дальше?

Отредактировано tester.nt, 18 June 2015 - 17:10.

[Bambuk]

tester.nt, on 18 June 2015 - 17:05, сказал:

Решение нашел, но Ваше понять не могу. Поможете? )

Если мы будем возвращать только числа меньше максимума, тогда у нас в вспомогательных столбцах будут все те же числа кроме максимума. Дальше если ищем максимумы в вспомогательных, то это будут текущие максимумы в вспомогательных. А что дальше?

Читайте внимательно условие задачи. Вы берёте мах1 для второго всомогат.столбца и возвращаете в него числа из второго основного столбца которые меньше мах 1 а если больше то 0
тогда во вспомогательном 2 останутся числа меньше мах1 если мах 1 =200 а во втором столбце числа 300 350 201 150 75 90 ....то вернутся выделенные а остальные 0 тогда тут 150 которое меньше мах1 и которое мах в остатке который образован условием <мах1.

Отредактировано Bambuk, 18 June 2015 - 18:14.

[tester.nt]

Bambuk, on 18 June 2015 - 18:05, сказал:

Читайте внимательно условие задачи. Вы берёте мах1 для второго всомогат.столбца и возвращаете в него числа из второго основного столбца которые меньше мах 1 а если больше то 0
тогда во вспомогательном 2 останутся числа меньше мах1 если мах 1 =200 а во втором столбце числа 300 350 201 150 75 90 ....то вернутся выделенные а остальные 0 тогда тут 150 которое меньше мах1 и которое мах в остатке который образован условием <мах1.

По-моему это Вы не правильно поняли условие задачи. Автор говорит "по уровню ниже", имея ввиду позицию на листе, а не числовое значение. Смотрите картинку по ссылке.

Отредактировано tester.nt, 18 June 2015 - 18:19.

[tester.nt]

Ну а если я правильно понял условие, то одно из решений может выглядеть вот так:

 Screen Shot 2015-06-18 at 20.37.07.png   26.41K   1 Количество загрузок  Screen Shot 2015-06-18 at 20.34.45.png   65.57K   0 Количество загрузок

[Bambuk]

Я картинку вообще не смотрел, просто прочитал пост и понял как понял...автор пишет числа 201.1 и 88.55 .
Если позиции то по смыслу тоже самое. надо просто ввести столбик с номерами записей 1,2,3,4... а потом по мах1 функцией ПОИСКПОЗ найти положение этого мах1 в таблице и вернуть во второй вспомогательный столбец записи второго столбца ниже этой позиции (в условии просто сравнивать с номерами которые мы ввели, а адрес яч с позицией и функцией поискпоз надо просто писать как абсолютный --тогда тащить можно просто формулу условия возвращающего значения).
Можно и через индексы делать... да я думаю автор там сам додумает....

Отредактировано Bambuk, 18 June 2015 - 19:02.

[tester.nt]

Bambuk, on 18 June 2015 - 18:54, сказал:

Я картинку вообще не смотрел, просто прочитал пост и понял как понял...автор пишет числа 201.1 и 88.55 .
Если позиции то по смыслу тоже самое. надо просто ввести столбик с номерами записей 1,2,3,4... а потом по мах1 функцией ПОИСКПОЗ найти положение этого мах1 в таблице и вернуть во второй вспомогательный столбец записи второго столбца ниже этой позиции (в условии просто сравнивать с номерами которые мы ввели, а адрес яч с позицией и функцией поискпоз надо просто писать как абсолютный --тогда тащить можно просто формулу условия возвращающего значения).
Можно и через индексы делать... да я думаю автор там сам додумает....

Да, по смыслу то же самое

Конечно, мне такое решение кажется немного избыточным. Но если не делать доп.столбцов, то придется немного поизвращаться.

[Bambuk]

tester.nt, on 18 June 2015 - 19:20, сказал:

Да, по смыслу то же самое

Конечно, мне такое решение кажется немного избыточным. Но если не делать доп.столбцов, то придется немного поизвращаться.

Если не знать как организованы данные то это наверно бесполезно. Если допустим фиксированный размер длины столбцов это одно, если нет--другое, есть пропуски в записях или нет и так далее. Да главное наверно-- чтоб считало правильно. Иногда приходится делать что-то наскоряк так там часто формулы такие наворотишь, что сам потом хрен разберёшься...

вот тут вчера делал для коней потом выяснилось что это вообще не надо

=ЕСЛИ(ЕОШИБКА(ПОИСК($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))

Отредактировано Bambuk, 18 June 2015 - 19:38.

[tester.nt]

Bambuk, on 18 June 2015 - 19:30, сказал:

=ЕСЛИ(ЕОШИБКА(ПОИСК($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))

А вообще-то с Вашей подачи я начал в VBA вникать. Как оказалось, полезная штука! Если писать функцию, то в нее надо передать диапазон из двух колонок, а все остальное уже внутри можно высчитать (я про данную задачу).

Отредактировано tester.nt, 18 June 2015 - 19:46.

[Scarabey8386]

tester.nt, on 18 June 2015 - 18:41, сказал:

Ну а если я правильно понял условие, то одно из решений может выглядеть вот так:

День добрый. Посыл примерно понял, но реализовать это так и не сумел к сожалению.
Тут еще эти пропуски, блин. Вообщем голова закипела..
А вообще ребят спасибо, что откликнулись....

Ну и плюс кол-во показателей не ростоянно. От 130 до 380(мах что было за все время)

[denvb]

tester.nt, on 18 June 2015 - 19:45, сказал:

Если писать функцию, то в нее надо передать диапазон из двух колонок, а все остальное уже внутри можно высчитать (я про данную задачу).

тоже считаю, что проще написать собственную функцию, чем строить доп. колонки. Связываться с VBA нет никакого желания, а на примере гугл-таблиц можно так

 2015-06-19_102942.png   7.15K   1 Количество загрузок

сам функция на 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]);
}

[tester.nt]

Вот и решение, которое занимает одну ячейку на столбец

Все, что Вам осталось сделать - присвоить имена диапазонам, где будут храниться данные ColA и ColB. Ну и без ошибок переписать формулы. Возьмите диапазон на 1000 ячеек вниз, чтобы с запасом был. А потом только вставляете данные и все. Или же переносите формулы на другие листы.

 Screen Shot 2015-06-19 at 11.43.04.png   16.53K   2 Количество загрузок

 Screen Shot 2015-06-19 at 11.43.26.png   33.42K   2 Количество загрузок

Отредактировано tester.nt, 19 June 2015 - 09:50.

[tester.nt]

Только там в формулах прописаны столбики "A" и "B". Следите, чтобы данные были именно в этих столбцах.

Ниже решение без привязки к столбцам (только к диапазонам 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))

[Scarabey8386]

Tester
Это шедевр. Ты спас мои глаза. Теперь эти цифры перестанут рябить в моих зенках Жму руку. Спасибо!!!
Всем спасибо за участие!

[Larin83]

tester.nt, on 18 June 2015 - 19:45, сказал:

А вообще-то с Вашей подачи я начал в VBA вникать.

VBA вещь крутая, всё хочу взяться изучить, но никак времени не найду))