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

Программирование на Visual Basic for Applications
@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 14.06.2007 (Чт) 15:06

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

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

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 14.06.2007 (Чт) 17:18

@Nik писал(а):- зачем нужно "+ТДАТА()*0" ???


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

@Nik писал(а):- чем отличается способ 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
Привет,
KL

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 15.06.2007 (Пт) 11:19

Спасибо :-)

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

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

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 15.06.2007 (Пт) 13:44

а расширеным фильтром?
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 15.06.2007 (Пт) 13:56

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

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 15.06.2007 (Пт) 19:12

может так?

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

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 16.06.2007 (Сб) 10:44

а зачем СТРОКА(A1), если можно просто СТРОКА()? :)
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 16.06.2007 (Сб) 11:07

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


Пожалуй :-)
Привет,
KL

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 16.06.2007 (Сб) 13:42

кто лучше :)
мне надо найти ближайшее к данному числу (В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
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 16.06.2007 (Сб) 14:41

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

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 16.06.2007 (Сб) 15:00

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

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 16.06.2007 (Сб) 15:17

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

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 16.06.2007 (Сб) 15:26

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

А чтоб нашло "9" при поиске значения "8.5", то надо к искомому значению "8.5" непосредственно перед поиском прибавить, скажем, "0.0001".
Последний раз редактировалось @Nik 16.06.2007 (Сб) 15:51, всего редактировалось 1 раз.

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 16.06.2007 (Сб) 15:38

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

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 16.06.2007 (Сб) 16:21

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


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

{=ПРОСМОТР(МИН(ABS(A1:A6-B1));A1:A6-B1;A1:A6)}
Привет,
KL

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 16.06.2007 (Сб) 17:19

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. вопрос остается открытым... нужна функция для любого несортированого списка выдающая при равноудаленных двух числах из списка большее...
у меня получилось такое :shock:
{=ИНДЕКС(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
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 16.06.2007 (Сб) 18:41

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


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

Krasnaja Shapka писал(а):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 16.06.2007 (Сб) 18:56, всего редактировалось 1 раз.
Привет,
KL

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 16.06.2007 (Сб) 18:55

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


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

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

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

Решение формулой чисто как ответ на "challenge" пожалуй сойдет, но в практическом смысле по-моему малоинтересно.
Вложения
Book1.xls
(16 Кб) Скачиваний: 168
Последний раз редактировалось KL 16.06.2007 (Сб) 19:35, всего редактировалось 1 раз.
Привет,
KL

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 16.06.2007 (Сб) 19:23

Krasnaja Shapka писал(а):p.p.s. вопрос остается открытым... нужна функция для любого несортированого списка выдающая при равноудаленных двух числах из списка большее...
у меня получилось такое :shock:
{=ИНДЕКС(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))}
Привет,
KL

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 18.06.2007 (Пн) 9:31

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

а вот это супер... все фурычит...
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 18.06.2007 (Пн) 9:52

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

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

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

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 18.06.2007 (Пн) 12:00

Вот подчищенное решение последней задачи @Nik. Теперь неважно кол-во нулей в ранге, а в варианте с mtxFormula также неважно наличие пробелов.
Вложения
SSHEET.LongestSequenceInArray.xls
(16 Кб) Скачиваний: 167
Привет,
KL

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 18.06.2007 (Пн) 13:52

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

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

Данные находятся в том же месте, что и у тебя: А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"-ми ))
Последний раз редактировалось @Nik 18.06.2007 (Пн) 15:14, всего редактировалось 1 раз.

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 18.06.2007 (Пн) 15:09

@Nik писал(а):Данные находятся в том же месте, что и у тебя: А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]. Как реагирует твоя формула и как мои (разумеется кроме первой)?
Привет,
KL

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 18.06.2007 (Пн) 15:45

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

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 17.07.2007 (Вт) 16:30

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

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

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

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 17.07.2007 (Вт) 16:36

Задача вторая :
А 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
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 17.07.2007 (Вт) 17:16

Ответы на обе задачи в приложении.
Вложения
test.xls
(21.5 Кб) Скачиваний: 167
Привет,
KL

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 18.07.2007 (Ср) 10:28

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

Теперь аж несколько стыдно показывать свои решения... :oops:
Но, тем не менее, вот они:
Вложения
test_2.1.xls
(19 Кб) Скачиваний: 176

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 18.07.2007 (Ср) 11:03

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

Пред.След.

Вернуться в VBA

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 5

    TopList