Video

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

Здесь сложно советовать что-то конкретное - такие алгоритмы у Excel. Попробуйте выбрать Эволюционный метод решения(если Excel позволяет - не во всех версиях он есть).

В области строк сначала добавляете Название товара, а затем туда же - Цены.

Смысл прост - PQ может сделать многое, что очень сложно сделать формулами Excel и многие задачи решают сейчас именно в PQ. И какие-то вычисления необходимо делать именно после других преобразований, чтобы они автоматом применились к преобразованным данным. Прописывать их к выгруженной на листе таблице плохая идея, т.к. что-то в запросе может измениться(кол-во строк или столбцов). Иными словами - если Вы пока не видите в этом смысла - это не значит, что его там нет :)

А если строк так 1,5млн?

@@AlekseyNA, вообще вопрос поставлен очень размыто. Все зависит от того, куда и какие вычисления должны быть записаны. Если вычисления должны быть записаны в каждую строку - думаю в этом случае лучше вычисления переносить в Power Pivot. Power Query не лучшее решения в таком случае. Но для List.Sum 1,5млн строк не должно стать сильной проблемой, если надо просто получить одну сумму.

Достаточно начать смотреть с 4:20 секунд :) Ставите в поиске решения не Минимум, а конкретное значение. В данном случае для указанного отрезка видео это 0. Или изначально в качестве результата укажите в поле поиска решения "Значение" и нужную сумму до копеек. Но как и в видео повторю: я бы так не делал, потому что поиск решения и так будет считать 0 минимальным решением, если такой результат можно вообще получить. А если нельзя - просто получите час ожидания и отсутствие результата.

Сергей, тогда просто не смотрите и отпишитесь. Цель видео - раскрыть возможности поиска и замены чуть больше, чем только про тильду рассказать.

Это уже не для ВПР. ВПР ничего не суммирует, а просто возвращает первое найденное. На моем сайте есть решения по суммированию с нескольких листов: www.excel-vba.ru/chto-umeet-excel/kak-prosummirovat-dannye-s-neskolkix-listov-v-tom-chisle-po-usloviyu/

Алексей, а можно поподробнее, что именно Вас так развеселило? Что не так с нижним подчеркиванием? Расскажите всем, как делать иначе, если нужны зависимые списки и заголовки при этом более чем из одного слова - всем будет интересно. При автоматизированном создании имен Excel так же заменяет пробелы именно нижним подчеркиванием и ни у кого это не вызывает реакции, подобно Вашей.

@@excel-vba_ru нет никаких верхних, нижних, правых или левых подчеркиваний. Есть просто одно самодостаточное слово - подчеркивание.

​@@alexeys462 , в Excel вообще в принципе не один вид подчеркивания, а аж 4: одинарное; двойное; одинарное, по ячейке; двойное, по ячейке. Это те, которые применяются для форматирования. А знак "подчеркивание", который я называю "нижнее подчеркивание" - называется нижним в обиходе и понятен всем именно так, как это требуется. Поэтому я применяю то название, которое будет понятно без лишних разъяснений и которое используется подавляющим большинством людей в речи. Вы можете всем говорить просто подчеркивание и смотреть на реакцию оппонента - поймет он Вас с первого раза или нет :) Если я буду называть все символы корректно с точки зрения их точных названий - меня половина людей не поймет. Та же "звездочка" называется астерикс, а не звездочка. Но если я буду так её называть - как думаете, много людей поймет о чем речь? :) Так что Ваш комментарий был бы очень кстати на канале лингвистов, но никак не здесь.

@@excel-vba_ru но вы же не называете "звездный астерикс"... в общем, ваша истерика в данном случае точно не уместна )

Без конкретного примера можно лишь что-то такое же не конкретное посоветовать. Совершенно непонятно, что делать, если нашли значение? И что делать, если не нашли :) Могу предложить такую формулу(ищет значение ячейки H11 в диапазоне A1:D10 и если находит это значение - его и выводит: =ВПР(H11;ИНДЕКС(A1:D10;;МАКС(ЕСЛИ(СЧЁТЕСЛИ(СМЕЩ(A1:A10;0;{0;1;2;3});H11)>0;{1;2;3;4})));1;0)

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

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

Максим, согласен с Вами. Поэтому на данный момент ведется работа по записи доп.ролика на эту тему.

@@excel-vba_ru забираю слова обратно - все получилось, но я перенес формулу в гугл таблицу и понял что гугл не умеет работать с некоторыми функциями из формулы (индекс,поискпоз) в массиве, ошибка н/а. Возможно получиться еще этот момент в видео осветить 🙄

@@user-hs7qv5tv9y не получится. Гугл и Excel - совершенно разные программы и невозможно в каждом ролике показывать и для того и для другого. На вскидку могу лишь сказать, что в случае с Гугл лучше будет использовать функцию QUERY - она может найти и вывести ВСЕ строки, отвечающие заданным критериям, в том числе из нескольких диапазонов

@@excel-vba_ru спасибо, я так и сделал через (index(query

Да, придется каждый раз нажимать поиск решения. Автоматизировать можно только при помощи Visual Basic for Applications, написав свой код, который будет "жать кнопку" за Вас

Связано со спецификой объединения запросов. Сохранить изначальный порядок можно только создав ПЕРЕД объединением столбец Индекса, по которому после объединения отсортировать таблицу.

Сергей, в данном случае ничего не сделать - Поиск решения не может работать более чем с 200 ячейками изменяемого диапазона, если не изменяет память. Если только модель изменить таким образом, чтобы изменяемых ячеек стало меньше.

@@excel-vba_ru ну и как быть когда ячеек 10 тысяч? И разбить на части не возможно, ну или возможно конечно все, но сумма та общая. и как впихнуть невпихуемое? а надо будет отоборать по трем суммам, которые составляют одну общую по всем 10 тысячам строк, но там пересортица произошла...((((( Помогите пожалуйста!

@@user-wi5fs3zd5w я не знаю как быть - без файла и данных советовать нечего. Можно свой код на VBA написать для подбора, например...

@@excel-vba_ru могу отправить на почту. Только не знаю как это сделать отсюда

@@excel-vba_ru просто я уже в состоянии истерической паники от невозможности найти решение как вырулить. Там произошла пересортица в бюджетах. и копейка в копейку даже и была бы возможность у данной надстройки не получится так подобрать. Вы для меня последняя надежда...вот толтко одно но...очень сжатый срок можно сказать один день, и второе но, я знаю, что это дорого стоит, а оплатить я не смогу ((((

Светлана, без примера данных проблему не понять и не решить. Возможно, не агрегированные данные имеют тенденцию к снижение, что и показывает ПРЕДСКАЗ. А когда Вы их агрегируете - отрицательные значения "схлопываются" положительными. Попробуйте написать в форум (www.excel-vba.ru/forum/index.php?board=3.0) - там можно прикладывать файлы. Можете и здесь, конечно, попробовать ссылку кинуть - но конф.информацию лучше заранее удалить в любом случае.

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

Добрый день. В 2007 Excel это есть и работает. Скорее всего, что-то делаете не так или сумма не может быть подобрана.

@@excel-vba_ru, думаю что вы просто не пробовали. Даже в Excel 2007 с SP3 это не работает. А точнее - игнорируется условие бинарности подобранного значения (подбираются нецелые числа).

@@Mostboobiest огорчу - но пробовал в 2007. И там это работает. Что значит не работает бинарность? И при чем здесь целые числа? Когда выставляете ограничение "бинарное", то Поиск решения в эти ячейки подставляет либо 0 либо 1. О каких нецелых речь? Он по определению не может туда вставлять какие-то другие значения. Вы уверены, что правильно понимаете суть этого ограничения и на какие ячейки его надо накладывать?

@@excel-vba_ru , я прекрасно понимаю. Excel просто подбирает и подставляет во вторую колонку различные дробные числа, получая таким образом искомый результат - игнорируя условие бинарности чисел в этой колонке.

@@Mostboobiest полагаю, что это какая-то проблема именно у Вас. Никогда это условие не подводило. Лучше будет, если создадите тему на форуме(www.excel-vba.ru/forum/index.php?board=3.0) и приложите файл, с созданным подбором через Поиск решения. Так можно будет сверить настройки и посмотреть действительно ли дело именно в Excel. Спасибо.

Александра, вопрос мало понятен. Возможно, надо изменить саму формулу поиска, но...Не понимаю какой результат нужен. Возьмем число 16: это произведение каких чисел? 8 и 2? Или 4 и 4? Или 2, 2 и 4? В общем, без конкретного примера предложить нечего...

@@excel-vba_ru К примеру у нас есть 10 чисел (ну или цифр) от 1 до 10 и есть произведение нескольких из этих чисел, например 18. (мы знаем, что это может быть 9 и 2, либо 6 и 3). А еще вот, например, у нас сумма 144, при этом мы знаем, что у нас в произведении чисел должно участвовать 3 цифры. При этом у нас есть набор из 15 цифр и чисел

@@user-pi3ol2zs8p Не думаю, что здесь вообще Поиск решения нужен. Допустим, в А1:А10 у нас числа. В Н1 - произведение. Можно в В1:В10 записать такую формулу массива: =ЕСЛИОШИБКА(ПОИСКПОЗ($H$1;A1*ТРАНСП($A$1:$A$10);0);0) Она отберет только те числа, которые в перемножении с другими числами массива дадут нужное число.

www.excel-vba.ru/multex/

Странно, должен быть. Посмотрите, по сути там в списке должно быть значение "бин" - это и есть "бин"арный

Дмитрий, а почему я должен был это показывать, если это не применяется в данном видео? :) Здесь идет разбор совсем другого примера и линейного сложения данных столбцов нет вообще...

@@excel-vba_ru Да, понимаю что тут о другом, но даже не знаю как назвать то что мне нужно)))вот благодаря вам уже знаю))) линейное сложение. а1=b1. а1+а2=b2. а1+а2+а3=b3. это же не сложно для экселя...уверен. просто нет знания(((

@@excel-vba_ru задача решена. Спасибо)