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

Вход

Регистрация

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

 

= Мир MS Excel/Купить дешевле - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир MS Excel » Работа и общение » Мозговой штурм » Купить дешевле
Купить дешевле
Понравилась задача?
ZORRO2005 Дата: Воскресенье, 16.04.2017, 01:28 | Сообщение № 1
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Взял задачку отсюда.
Надо с помощью одной формулы найти минимальную сумму для каждого товара, если покупать продукты (определенное кол-во) в магазинах.
Формула в ячейке I2 (протягиваемая).
К сообщению приложен файл: 5782502.xlsx (10.2 Kb)
 
Ответить
СообщениеВзял задачку отсюда.
Надо с помощью одной формулы найти минимальную сумму для каждого товара, если покупать продукты (определенное кол-во) в магазинах.
Формула в ячейке I2 (протягиваемая).
Автор - ZORRO2005
Дата добавления - 16.04.2017 в 01:28
MCH Дата: Воскресенье, 16.04.2017, 19:46 | Сообщение № 2
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Формула массива - 182 171 167 118 знаков
вместо "Магазин" использовал ссылку на B2
 
Ответить
СообщениеФормула массива - 182 171 167 118 знаков
вместо "Магазин" использовал ссылку на B2
Автор - MCH
Дата добавления - 16.04.2017 в 19:46
Светлый Дата: Воскресенье, 16.04.2017, 23:04 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1851
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
Тоже массивная 128 знаков, но есть возможность за счёт ="Магазин" сократить.

Программировать проще, чем писать стихи.
 
Ответить
СообщениеТоже массивная 128 знаков, но есть возможность за счёт ="Магазин" сократить.
Автор - Светлый
Дата добавления - 16.04.2017 в 23:04
ZORRO2005 Дата: Понедельник, 17.04.2017, 00:09 | Сообщение № 4
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Массивная - 127.


Сообщение отредактировал ZORRO2005 - Понедельник, 17.04.2017, 00:14
 
Ответить
СообщениеМассивная - 127.
Автор - ZORRO2005
Дата добавления - 17.04.2017 в 00:09
Светлый Дата: Понедельник, 17.04.2017, 05:58 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1851
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
Добил до 118 115 символов. Если использовать вместо "Магазин" ссылку на ячейку с таким значением, то 112 109 символов. Массивная.

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 17.04.2017, 06:07
 
Ответить
СообщениеДобил до 118 115 символов. Если использовать вместо "Магазин" ссылку на ячейку с таким значением, то 112 109 символов. Массивная.
Автор - Светлый
Дата добавления - 17.04.2017 в 05:58
Светлый Дата: Вторник, 18.04.2017, 19:55 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1851
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
В 2003 моя формула на 109 работать не будет, поэтому мой окончательный вариант 118 символов. Как у MCH. Скорее всего и формулы одинаковые.

Программировать проще, чем писать стихи.
 
Ответить
СообщениеВ 2003 моя формула на 109 работать не будет, поэтому мой окончательный вариант 118 символов. Как у MCH. Скорее всего и формулы одинаковые.
Автор - Светлый
Дата добавления - 18.04.2017 в 19:55
MCH Дата: Вторник, 18.04.2017, 21:35 | Сообщение № 7
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Цитата Светлый, 18.04.2017 в 19:55, в сообщении № 6 ()
В 2003 моя формула на 109 работать не будет

Вместо ТРАНСП(СТРОКА) - СТОЛБЕЦ?
тогда у меня 108
 
Ответить
Сообщение
Цитата Светлый, 18.04.2017 в 19:55, в сообщении № 6 ()
В 2003 моя формула на 109 работать не будет

Вместо ТРАНСП(СТРОКА) - СТОЛБЕЦ?
тогда у меня 108
Автор - MCH
Дата добавления - 18.04.2017 в 21:35
Светлый Дата: Вторник, 18.04.2017, 23:07 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1851
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата MCH, 18.04.2017 в 21:35, в сообщении № 7 ()
тогда у меня 108

Я не ограничился значением столбца "Требуется" 702-мя шт., поэтому на символ больше получилось. Вдруг потребуется 1000 или больше. Ограничение в 256 столбцов, конечно, существеннее.
Я почти уверен, что формулы совпадают до символа.
[offtop]Кстати, маленькая загадка: 1000 - это ВСЕ
Кто догадается, о чём идёт речь? Вопрос касается непосредственно Excel. Может быть в МШ эту загадку?

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата MCH, 18.04.2017 в 21:35, в сообщении № 7 ()
тогда у меня 108

Я не ограничился значением столбца "Требуется" 702-мя шт., поэтому на символ больше получилось. Вдруг потребуется 1000 или больше. Ограничение в 256 столбцов, конечно, существеннее.
Я почти уверен, что формулы совпадают до символа.
[offtop]Кстати, маленькая загадка: 1000 - это ВСЕ
Кто догадается, о чём идёт речь? Вопрос касается непосредственно Excel. Может быть в МШ эту загадку?
Автор - Светлый
Дата добавления - 18.04.2017 в 23:07
MCH Дата: Среда, 19.04.2017, 07:35 | Сообщение № 9
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Цитата Светлый, 18.04.2017 в 23:07, в сообщении № 8 ()
Я почти уверен, что формулы совпадают до символа.

Может быть не совпадают, я в размере формулы считаю знак "=", алгоритм скорее всего одинаковый, но есть вариативность, например, использование СМЕЩ вместо ДВССЫЛ, или вместо (a=b)*(c=d) использовать (a&c=b&d), или исключить не нужные скобки, так что одна и та же формула может быть написана по разному

Вскрываться будем?
 
Ответить
Сообщение
Цитата Светлый, 18.04.2017 в 23:07, в сообщении № 8 ()
Я почти уверен, что формулы совпадают до символа.

Может быть не совпадают, я в размере формулы считаю знак "=", алгоритм скорее всего одинаковый, но есть вариативность, например, использование СМЕЩ вместо ДВССЫЛ, или вместо (a=b)*(c=d) использовать (a&c=b&d), или исключить не нужные скобки, так что одна и та же формула может быть написана по разному

Вскрываться будем?
Автор - MCH
Дата добавления - 19.04.2017 в 07:35
Светлый Дата: Среда, 19.04.2017, 20:18 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1851
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата MCH, 19.04.2017 в 07:35, в сообщении № 9 ()
Вскрываться будем?

Автор темы пусть командует парадом. Желающие присоединиться не заявили о своём желании.
Даже косвенные подсказки помогают. Сократил ещё на пару символов. Теперь 116 и 107 символов. Это без "=".

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата MCH, 19.04.2017 в 07:35, в сообщении № 9 ()
Вскрываться будем?

Автор темы пусть командует парадом. Желающие присоединиться не заявили о своём желании.
Даже косвенные подсказки помогают. Сократил ещё на пару символов. Теперь 116 и 107 символов. Это без "=".
Автор - Светлый
Дата добавления - 19.04.2017 в 20:18
ZORRO2005 Дата: Среда, 19.04.2017, 20:33 | Сообщение № 11
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Да, можно вскрыться.
Главная моя цель - узнать алгоритмы решения, а не написать самую короткую формулу.
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ($1:$9999)<=(A$2:A$22&B$2:B$22=G2&"Магазин")*C$2:C$22;D$2:D$22);СТРОКА(ДВССЫЛ("1:"&H2))))

Ограничение - 9999 ед. для одного магазина.
 
Ответить
СообщениеДа, можно вскрыться.
Главная моя цель - узнать алгоритмы решения, а не написать самую короткую формулу.
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ($1:$9999)<=(A$2:A$22&B$2:B$22=G2&"Магазин")*C$2:C$22;D$2:D$22);СТРОКА(ДВССЫЛ("1:"&H2))))

Ограничение - 9999 ед. для одного магазина.
Автор - ZORRO2005
Дата добавления - 19.04.2017 в 20:33
Светлый Дата: Среда, 19.04.2017, 21:25 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1851
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
С таким же ограничением 117 символов:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(ТРАНСП(СТРОКА($1:$9999))<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))

И для >2003 с ограничением >16000 107 символов:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ(A:XFA)<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))


И ещё один вариант на 110 символов созрел с подсказкой. Но не знаю, будет ли в 2003 работать:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ($1:$9999)<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))

В 2007 "СТОЛБЕЦ($1:$25000)" работает и не выдаёт ошибку, хотя там количество столбцов меньше, 2^14.

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 19.04.2017, 22:01
 
Ответить
СообщениеС таким же ограничением 117 символов:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(ТРАНСП(СТРОКА($1:$9999))<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))

И для >2003 с ограничением >16000 107 символов:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ(A:XFA)<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))


И ещё один вариант на 110 символов созрел с подсказкой. Но не знаю, будет ли в 2003 работать:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ($1:$9999)<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))

В 2007 "СТОЛБЕЦ($1:$25000)" работает и не выдаёт ошибку, хотя там количество столбцов меньше, 2^14.
Автор - Светлый
Дата добавления - 19.04.2017 в 21:25
_Boroda_ Дата: Среда, 19.04.2017, 23:27 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 16714
Репутация: 6503 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Цитата Светлый, 19.04.2017 в 21:25, в сообщении № 12 ()
В 2007 ... количество столбцов меньше, 2^14.

Меньше, чем где?

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата Светлый, 19.04.2017 в 21:25, в сообщении № 12 ()
В 2007 ... количество столбцов меньше, 2^14.

Меньше, чем где?
Автор - _Boroda_
Дата добавления - 19.04.2017 в 23:27
ZORRO2005 Дата: Четверг, 20.04.2017, 00:22 | Сообщение № 14
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Саша, меньше чем 25000.
 
Ответить
СообщениеСаша, меньше чем 25000.
Автор - ZORRO2005
Дата добавления - 20.04.2017 в 00:22
MCH Дата: Четверг, 20.04.2017, 07:49 | Сообщение № 15
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Мои формулы:
108:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ(A:XFC)<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))


с "подсказкой" - 106:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ(1:1)<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))
функция СТОЛБЕЦ(1:1), также как и СТОЛБЕЦ($1:$9999) будет генерировать массив содержащий 16384 значений, соответственно в 2003XL - 256 значений (нужно проверить, может быть ошибаюсь)

Она же, но более универсально:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ(СМЕЩ(A1;;;;МАКС(C:C)))<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))


Первоначальная формула, реализованная по другому алгоритму - 167:
Код
=СУММ(НАИМЕНЬШИЙ(ПРОСМОТР(СТРОКА(ДВССЫЛ("1:"&СУММ(C:C)))-1;СУММЕСЛИ(СМЕЩ(C$1;;;СТРОКА($1:$21));"<>");ЕСЛИ(A$2:A$22&B$2:B$22=G2&B$2;D$2:D$22));СТРОКА(ДВССЫЛ("1:"&H2))))


PS:
Цитата Светлый, 18.04.2017 в 23:07, в сообщении № 8 ()
Я почти уверен, что формулы совпадают до символа.

формулы различаются на один символ: XFC и XFA
 
Ответить
СообщениеМои формулы:
108:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ(A:XFC)<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))


с "подсказкой" - 106:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ(1:1)<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))
функция СТОЛБЕЦ(1:1), также как и СТОЛБЕЦ($1:$9999) будет генерировать массив содержащий 16384 значений, соответственно в 2003XL - 256 значений (нужно проверить, может быть ошибаюсь)

Она же, но более универсально:
Код
=СУММ(НАИМЕНЬШИЙ(ЕСЛИ(СТОЛБЕЦ(СМЕЩ(A1;;;;МАКС(C:C)))<=C$2:C$22*(A$2:A$22&B$2:B$22=G2&B$2);D$2:D$22);СТРОКА(СМЕЩ(A$1;;;H2))))


Первоначальная формула, реализованная по другому алгоритму - 167:
Код
=СУММ(НАИМЕНЬШИЙ(ПРОСМОТР(СТРОКА(ДВССЫЛ("1:"&СУММ(C:C)))-1;СУММЕСЛИ(СМЕЩ(C$1;;;СТРОКА($1:$21));"<>");ЕСЛИ(A$2:A$22&B$2:B$22=G2&B$2;D$2:D$22));СТРОКА(ДВССЫЛ("1:"&H2))))


PS:
Цитата Светлый, 18.04.2017 в 23:07, в сообщении № 8 ()
Я почти уверен, что формулы совпадают до символа.

формулы различаются на один символ: XFC и XFA
Автор - MCH
Дата добавления - 20.04.2017 в 07:49
ZORRO2005 Дата: Четверг, 20.04.2017, 13:16 | Сообщение № 16
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Цитата MCH, 20.04.2017 в 07:49, в сообщении № 15 ()
функция СТОЛБЕЦ(1:1), также как и СТОЛБЕЦ($1:$9999) будет генерировать массив содержащий 16384 значений, соответственно в 2003XL - 256 значений (нужно проверить, может быть ошибаюсь)

Да,
Код
=СЧЁТ(СТОЛБЕЦ(1:1))
и
Код
=СЧЁТ(СТОЛБЕЦ(1:25000))
выдаст 16384 в xlsx, а в xls-256.

Цитата MCH, 20.04.2017 в 07:49, в сообщении № 15 ()
Первоначальная формула, реализованная по другому алгоритму - 167

Мне очень понравилось решение через ПРОСМОТР(). Возьму на заметку)
Ребята, спасибо за участие!
 
Ответить
Сообщение
Цитата MCH, 20.04.2017 в 07:49, в сообщении № 15 ()
функция СТОЛБЕЦ(1:1), также как и СТОЛБЕЦ($1:$9999) будет генерировать массив содержащий 16384 значений, соответственно в 2003XL - 256 значений (нужно проверить, может быть ошибаюсь)

Да,
Код
=СЧЁТ(СТОЛБЕЦ(1:1))
и
Код
=СЧЁТ(СТОЛБЕЦ(1:25000))
выдаст 16384 в xlsx, а в xls-256.

Цитата MCH, 20.04.2017 в 07:49, в сообщении № 15 ()
Первоначальная формула, реализованная по другому алгоритму - 167

Мне очень понравилось решение через ПРОСМОТР(). Возьму на заметку)
Ребята, спасибо за участие!
Автор - ZORRO2005
Дата добавления - 20.04.2017 в 13:16
MCH Дата: Четверг, 20.04.2017, 14:27 | Сообщение № 17
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Во вложении несколько вариантов решения, в. т.ч. с использованием дополнительных вычислений.
К сообщению приложен файл: _17.xlsx (12.0 Kb)
 
Ответить
СообщениеВо вложении несколько вариантов решения, в. т.ч. с использованием дополнительных вычислений.
Автор - MCH
Дата добавления - 20.04.2017 в 14:27
Мир MS Excel » Работа и общение » Мозговой штурм » Купить дешевле
  • Страница 1 из 1
  • 1
Поиск:

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