Любителям задач "со звёздочкой" в экселе...

[@Nik]

Ты меня каждый раз приятно поражаешь.... Чем больше я разбираюсь в экселе, тем больше я понимаю, как мало я его знаю....

Только есть 2 вопроса:
- зачем нужно "+ТДАТА()*0" ???
- чем отличается способ 1 от способа 2 ? Они, кажись, абсолютно идентичные....

[KL]

- зачем нужно "+ТДАТА()*0" ???

Для летучести. Функция ТДАТА() - летучая по определению. Хотя в данном случае я это сделал по-привычке - летучесть тут не нужна

- чем отличается способ 1 от способа 2 ? Они, кажись, абсолютно идентичные....

Нет они не одинаковые, но принцып один. В VBA я бы сделал так:

Код: Выделить всё

Function FormulaToArray(myCell As Range) As Variant
    If myCell.HasFormula Then
        FormulaToArray = Evaluate(myCell.Formula)
    Else
        FormulaToArray = CVErr(xlErrNA)
    End If
End Function

[@Nik]

Спасибо

Есть ещё одна интересная задачка. Есть 2 столбца значений. Нужно из второго столбца выбрать только те значения, для которых в первом столбце значения равны опр. константе. Нюанс в том, чтобы полученные значения расположить по порядку, без пропусков и без вспомогательного столбца
Например:
А В С
1 4 3
3 8 8
4 3 1
7 8
9 0
4 8
6 4
4 1

Поиск "4" по столбцу "А" и выбор значений из столбца "В". Результат - в столбце "С"

[Krasnaja Shapka]

а расширеным фильтром?

[@Nik]

Та не, так не интересно и не всегда удобно. Хотю формулами

[KL]

может так?

{=ЕСЛИ(СЧЁТЕСЛИ($A$1:$A$8;4)>=(СТРОКА(A1)-СТРОКА($A$1)+1);ИНДЕКС($B:$B;НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$8=4;СТРОКА($A$1:$A$8));(СТРОКА(A1)-СТРОКА($A$1)+1)));"")}

[Krasnaja Shapka]

а зачем СТРОКА(A1), если можно просто СТРОКА()?

[KL]

а зачем СТРОКА(A1), если можно просто СТРОКА()?

Пожалуй

[Krasnaja Shapka]

кто лучше
мне надо найти ближайшее к данному числу (В1) число из списка(A1:A6)

А В
1 3 7
2 5
3 8
4 9
5 12
6 15

ответ 8

мой вариант:
для списка сортирован по возростанию
{=ГПР(МИН(ABS(F18:K18-F29))+F29;F18:K18;1)}
для любого списка
{=ИНДЕКС(A1:A6;ПОИСКПОЗ(МИН(ABS(A1:A6-B1));A1:A6-B1;0))}

[@Nik]

А ларчик-то просто открывался
KL, спасибо, ты лучший...!!!

[@Nik]

Да, Krasnaja Shapka, классный метод! И задача довольно часто встречающаяся...
Только, единственная поправка:
{=ИНДЕКС(A1:A6;ПОИСКПОЗ(МИН(ABS(A1:A6-B1));ABS(A1:A6-B1);0))}
Иначе попробуй, скажем, число "5" найти...

[Krasnaja Shapka]

в моем примере 5 ищется на ура... а в твоем на 8,5 найдет ближайшее 8.. хотя по канонам округления правильно все же 9 ... все зависит от поставленой задачи

[@Nik]

Ой, торможу, не "5", а "6". Для 6 должно найти "5", а высвечивает "#Н/Д"

А чтоб нашло "9" при поиске значения "8.5", то надо к искомому значению "8.5" непосредственно перед поиском прибавить, скажем, "0.0001".

[@Nik]

Столкнулся с ещё одной довольно интересной задачей и при помощи формул экселя не смог выкрутиться...
Надо подсчитать максимальное количество идущих подряд единиц.
Например: для ряда {1;0;1;1;1;0} ответ - 3. Меня также устроит решение, если просто определить наличие в искомом ряде {1;1;1}. Ряд {1;0;1;1;1;0} образуется в результате вычисления формулы, а не лежит где-нибудь на листе. Допускается использовать ячейки для промежут. вычислений.

[KL]

для списка сортирован по возростанию
{=ГПР(МИН(ABS(F18:K18-F29))+F29;F18:K18;1)}

для списка сортированного по возрастанию - еще такой вариант:

{=ПРОСМОТР(МИН(ABS(A1:A6-B1));A1:A6-B1;A1:A6)}

[Krasnaja Shapka]

2 @Nik
да... точно... выдает ошибку...
2 KL
"ПРОСМОТР" для не сортированого списка не очень канает...
согласно справке
"Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат."

p.s. вот нашел список: 8, 5, 3, 9, 12, 15
при поиске 6,5 выдает 3

p.p.s. вопрос остается открытым... нужна функция для любого несортированого списка выдающая при равноудаленных двух числах из списка большее...
у меня получилось такое
{=ИНДЕКС(A1:A6;ПОИСКПОЗ(МИН(ABS(A1:A6-B1));ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(МИН(ABS(A1:A6-B1));A1:A6-B1;0));B1-A1:A6;A1:A6-B1);0))}
p.p.p.s. кстати, вопрос, как формулу массива посчитать в VBA c помощью Evaluate или WorksheetFunctions???

[KL]

2 KL
"ПРОСМОТР" для не сортированого списка не очень канает...

Вообще "не канает". Ты зря потратил время на ответ мне - прочти еще раз мое сообщение

p.p.p.s. кстати, вопрос, как формулу массива посчитать в VBA c помощью Evaluate или WorksheetFunctions???

Можешь конкретнее? Какая формула и в чем проблема. Evaluate, насколько я понимаю, прекрасно считает формулы массива:

Код: Выделить всё

Sub test()
    Dim i As Long
    Dim mtx As Variant
    [A1:A10] = Evaluate("{1;2;3;4;5;6;7;8;9;10}")
    [B1:B10] = Evaluate("{1;1;1;1;1;1;1;1;1;1}")
    mtx = Evaluate("A1:A10-B1:B10")
    For i = LBound(mtx) To UBound(mtx)
        Debug.Print mtx(i, 1)
    Next i
End Sub

[KL]

Столкнулся с ещё одной довольно интересной задачей и при помощи формул экселя не смог выкрутиться...
Надо подсчитать максимальное количество идущих подряд единиц.
Например: для ряда {1;0;1;1;1;0} ответ - 3. Меня также устроит решение, если просто определить наличие в искомом ряде {1;1;1}. Ряд {1;0;1;1;1;0} образуется в результате вычисления формулы, а не лежит где-нибудь на листе. Допускается использовать ячейки для промежут. вычислений.

Надо еще тестировать, но мочь, похоже, можно, а вот нужно ли - решать тебе Прилагаю решение в одной ячейке. Массив засунут в имя для удобочитаемости и во избежание превышения макс. длины формулы. Формула расчитана на...

1) отсутствие пустых ячеек и вообще значений отличных от 1 и 0
2) наличее как минимум двух нулей

...но при желании можно оттачивать дальше.

Решение формулой чисто как ответ на "challenge" пожалуй сойдет, но в практическом смысле по-моему малоинтересно.

[KL]

p.p.s. вопрос остается открытым... нужна функция для любого несортированого списка выдающая при равноудаленных двух числах из списка большее...
у меня получилось такое
{=ИНДЕКС(A1:A6;ПОИСКПОЗ(МИН(ABS(A1:A6-B1));ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(МИН(ABS(A1:A6-B1));A1:A6-B1;0));B1-A1:A6;A1:A6-B1);0))}

попробуй вот это:

{=МАКС(ЕСЛИ(ABS(A1:A20-B1)=МИН(ABS(A1:A20-B1));A1:A20))}

[Krasnaja Shapka]

Можешь конкретнее? Какая формула и в чем проблема. Evaluate, насколько я понимаю, прекрасно считает формулы массива

да... все правильно... оказалось надо просто правильнее формулу писать

попробуй вот это:

а вот это супер... все фурычит...

[@Nik]

Так я не пойму, Krasnaja Shapka, почему тебе не нравится способ:

{=ИНДЕКС(A1:A6;ПОИСКПОЗ(МИН(ABS(A1:A6-B1-0,00001));ABS(A1:A6-B1-0,00001);0))} ??

А если работаешь с числами большой точности, то отнимай ещё меньшую величину...

[KL]

Вот подчищенное решение последней задачи @Nik. Теперь неважно кол-во нулей в ранге, а в варианте с mtxFormula также неважно наличие пробелов.

[@Nik]

KL - молодчина ! Целый час разбирался с твоей формулой.... Оригинальный метод...

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

Данные находятся в том же месте, что и у тебя: А1:А18.

{=МАКС(НАИМЕНЬШИЙ(СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A)))*A1:A18*НЕ(A2:A19);СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A))))-НАИМЕНЬШИЙ(СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A)))*НЕ(A1:A18)*A2:A19;СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A)))))}

Желающим разобраться в формуле напомню, что для этого удобно использовать клавишу F9 (преобразование формулы в значения). Выделяем в строке формул законченный кусок формулы и нажимаем F9 (напр.: если выделить "СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A)))" и нажать Ф9, получим {1:2:3 ....... :17:18}, при условии, что А1:А18 заполнены значениями (в дан. случае ("1"-ми или "0"-ми ))

[KL]

Данные находятся в том же месте, что и у тебя: А1:А18.

{=МАКС(НАИМЕНЬШИЙ(СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A)))*(2=ЕСЛИ((A1:A18=1)*(A2:A19=0);2));СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A))))-НАИМЕНЬШИЙ(СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A)))*(1=ЕСЛИ((A1:A18=0)*(A2:A19=1);1;0));СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A)))))}

Желающим разобраться в формуле напомню, что для этого удобно использовать клавишу F9 (преобразование формулы в значения). Выделяем в строке формул законченный кусок формулы и нажимаем F9 (напр.: если выделить "СТРОКА(СМЕЩ(A1;;;СЧЁТ(A:A)))" и нажать Ф9, получим {1:2:3 ....... :17:18} )

В целом неплохо, но по-моему мы решали задачу с разными условиями ;-)

1) кто-то из нас забыл про это: "Ряд {1;0;1;1;1;0} образуется в результате вычисления формулы, а не лежит где-нибудь на листе", а функция СМЕЩ() с "неячеечными" диапазонами не работает :-(

2) формула твоя летучая все из-за той же СМЕЩ() - задолбает пересчетами. Можно поправить так:

{=МАКС(НАИМЕНЬШИЙ((mtxRange=1)*(mtxRange2=0)*СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;СЧЁТ(mtxRange)));СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;СЧЁТ(mtxRange))))-НАИМЕНЬШИЙ((mtxRange=0)*(mtxRange2=1)*СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;СЧЁТ(mtxRange)));СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;СЧЁТ(mtxRange)))))}

где:
mtxRange заданый именем ранг [А1:А18]
mtxRange2 заданый именем ранг [А2:А19]

3) попробуй ввести еще строку над [A1]. Как реагирует твоя формула и как мои (разумеется кроме первой)?

[@Nik]

Да, всё верно....
А я ещё хотел, было, задать тебе вопрос, мол, чего ты пишешь всё время ИНДЕКС(А:А;1), если можно просто писать А1. А теперь понял

[@Nik]

Вот ещё 2 интересные задачки и главное - достаточно полезныве Решение уже есть, но выкладывать пока не буду
Итак, первая:
Есть таблица с заголовками столбцов (с шапкой). Заголовки столбцов могут повторяться. Задача просуммировать все столбцы с заданым заголовком (напр. "Сумма")

Напр, заголовки таблицы:
Диз.топ Литры Сумма Бенз. Литры Сумма Газ М3 Сумма

Суммировать все значения из столбцов "Сумма" одной формулой

[@Nik]

Задача вторая :
А B
1 гор1 5
2 гор2 1
3 гор3 10
4 гор4 8
5 гор5 3
6 гор2 2
7 гор3 1
8 гор3 5
9 гор4 5
10 гор5 2
11 гор2 7
12 гор4 7
13 гор5 7

16 гор1 5
17 гор2 1+2+7 = 10
18 гор3 10+1+5 = 16

5+10+16= 31 - получить одной формулой

[KL]

Ответы на обе задачи в приложении.

[@Nik]

Капец, у меня просто нет слов.....
Я с горем по полам придумал решение и прыгал от радости, а он - сразу несколько и все - покруче моего...!

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

[@Nik]

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