Большое спасибо за ваш урок. Буду очень признательна, если подскажите, как сделать, условно говоря, Аня минус Борис, у них же поле одно? (Нужно тоже через вычисляемое поле)
Очень хорошее видео. Автору большое спасибо. Сводная будет автоматически обновляться если сначала из сырых данных сделать Умную таблицу а затем из неё Сводную.
Здравствуйте! У меня так не получается. Вообще никак не получается. нужно подключится к гугл таблице с запретом на редактирование. Долго думает, потом ошибку выдает. Помогите пожалуйста, в чем может быть дело....с других таблиц и ресурсов все подгружает
Так если запрет на редактирование - может Google просто отказывает в доступе к файлу. Если его нельзя редактировать - нельзя скачать. А на этом основан принцип работы данного механизма. Думаю, имеет смысл сделать копию файла в Google, данные в который тянуть из текущего через IMPORTRANGE и уже эти данные считывать через PowerQuery
Спасибо огромное! Видео 6 лет, а до сих пор судя по комментариям многим пригождается! Очень доступно рассказываете- показываете! Как только вам терпения хватает😄 Спасибо!
Что то очень долго считает у меня, всегда в ручную подбирал суммы, а тут задумался а не может ли ексель за меня посчитать, мне кажется что вручную было бы быстрее, если добавить время на разобраться
Здесь сложно советовать что-то конкретное - такие алгоритмы у Excel. Попробуйте выбрать Эволюционный метод решения(если Excel позволяет - не во всех версиях он есть).
6:05 Нет, в исходной таблице должны быть только абсолютные значения. Все относительные показатели, как то проценты, доли, нарастающие итоги и т.д. должны считаться в сводной. Иначе, если б мы посчитали стоимость за единицу товара в исходной таблице, а затем стали бы использовать этот показатель в сводной, то сводная бы тупо его суммировала по выбранным срезам, что является ошибкой.
Дмитрий, подскажите: " В сводной таблице одно название товара, но по разным ценам , соединяться по количеству" . Как сделать в сводной таблице чтобы Товар с одним названием но с разными ценами, разделялся? Спасибо Вам!
Дмитрий здравствуйте, благодарю вас, а вы можете на примере пояснить почему не стоит использовать автозаполнение и так далее... Поясните пожалуйста, не совсем понял ....спасибо
Смысл прост - PQ может сделать многое, что очень сложно сделать формулами Excel и многие задачи решают сейчас именно в PQ. И какие-то вычисления необходимо делать именно после других преобразований, чтобы они автоматом применились к преобразованным данным. Прописывать их к выгруженной на листе таблице плохая идея, т.к. что-то в запросе может измениться(кол-во строк или столбцов). Иными словами - если Вы пока не видите в этом смысла - это не значит, что его там нет :)
@@AlekseyNA, вообще вопрос поставлен очень размыто. Все зависит от того, куда и какие вычисления должны быть записаны. Если вычисления должны быть записаны в каждую строку - думаю в этом случае лучше вычисления переносить в Power Pivot. Power Query не лучшее решения в таком случае. Но для List.Sum 1,5млн строк не должно стать сильной проблемой, если надо просто получить одну сумму.
Подскажите пожалуйста, так как заполнить "Параметры поиска решений" если нужно не приблизительный итог а точная сумма (на Вашем примере это сумма в ячейке H2)?
Достаточно начать смотреть с 4:20 секунд :) Ставите в поиске решения не Минимум, а конкретное значение. В данном случае для указанного отрезка видео это 0. Или изначально в качестве результата укажите в поле поиска решения "Значение" и нужную сумму до копеек. Но как и в видео повторю: я бы так не делал, потому что поиск решения и так будет считать 0 минимальным решением, если такой результат можно вообще получить. А если нельзя - просто получите час ожидания и отсутствие результата.
А если номер контракта может находиться на разных страницах (например на Астраханьоблгаз и Липецкоблгаз) Как тогда найти их сумму, что нужно поменять в формуле? =ВПР(A2;ДВССЫЛ("'"&ИНДЕКС($E$2:$E$10;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ("'"&$E$2:$E$10 &"'!A1:A1000");A2)>0;0))&"'!A:B");2;0)
Это уже не для ВПР. ВПР ничего не суммирует, а просто возвращает первое найденное. На моем сайте есть решения по суммированию с нескольких листов: www.excel-vba.ru/chto-umeet-excel/kak-prosummirovat-dannye-s-neskolkix-listov-v-tom-chisle-po-usloviyu/
Алексей, а можно поподробнее, что именно Вас так развеселило? Что не так с нижним подчеркиванием? Расскажите всем, как делать иначе, если нужны зависимые списки и заголовки при этом более чем из одного слова - всем будет интересно. При автоматизированном создании имен Excel так же заменяет пробелы именно нижним подчеркиванием и ни у кого это не вызывает реакции, подобно Вашей.
@@alexeys462 , в Excel вообще в принципе не один вид подчеркивания, а аж 4: одинарное; двойное; одинарное, по ячейке; двойное, по ячейке. Это те, которые применяются для форматирования. А знак "подчеркивание", который я называю "нижнее подчеркивание" - называется нижним в обиходе и понятен всем именно так, как это требуется. Поэтому я применяю то название, которое будет понятно без лишних разъяснений и которое используется подавляющим большинством людей в речи. Вы можете всем говорить просто подчеркивание и смотреть на реакцию оппонента - поймет он Вас с первого раза или нет :) Если я буду называть все символы корректно с точки зрения их точных названий - меня половина людей не поймет. Та же "звездочка" называется астерикс, а не звездочка. Но если я буду так её называть - как думаете, много людей поймет о чем речь? :) Так что Ваш комментарий был бы очень кстати на канале лингвистов, но никак не здесь.
Добрый день, как реализовать подскажите пожалуйста. У меня неизвестно где именно находится искомое значение, а когда выделяешь всю таблицу, ф-ция поискпоз не работает. я прописал следующую формулу =ИНДЕКС(A1:E15;1;ЕСЛИОШИБКА(ПОИСКПОЗ(H11;A:A;0);ПОИСКПОЗ(H11;B:B;0))), то есть если искомое значение небыло найдено, формула ищет его в следующем столбце, Но у меня очень много столбцов, и просто не возможно в формуле прописать их в ручную, как можно сделать, чтоб формула при ошибке автоматически переходила на следующий столбец и искала в нем, и эти переходы были до того момента пока искомое значение не будет найдено
Без конкретного примера можно лишь что-то такое же не конкретное посоветовать. Совершенно непонятно, что делать, если нашли значение? И что делать, если не нашли :) Могу предложить такую формулу(ищет значение ячейки H11 в диапазоне A1:D10 и если находит это значение - его и выводит: =ВПР(H11;ИНДЕКС(A1:D10;;МАКС(ЕСЛИ(СЧЁТЕСЛИ(СМЕЩ(A1:A10;0;{0;1;2;3});H11)>0;{1;2;3;4})));1;0)
Ecли я хочу просмотреть содержимое уже имеющегося -xml- файла, но он частично зашифрованный (или закомпилированный) - и там местами вместо букв - сплошные ромбики. Как его тогда расшифровать?
Сложно сказать, не видя файла - надо хоть какую-то закономерность определить, откуда эти ромбики берутся. Возможно даже никак, если это является изначальной ошибкой кодировки.
🆘🆘🆘ПОДСКАЖИТЕ, ПОЖАЛУЙСТА, как просчитать если в некоторых месяцах отсутствуют показали в связи с отсутствием товара на складе? Если брать за ноль, то прогноз выходит неверным 😢
Если делаете формулой - то придется кардинально менять расчет коэффициента сезонности и без данных сказать сложно как именно. Если делаете через лист прогноза - то удалите нулевые показатели(просто клавишей Del) и тогда в параметрах можно выбрать расчет отсутствующих значений методом интерполяции.
@@excel-vba_ru забираю слова обратно - все получилось, но я перенес формулу в гугл таблицу и понял что гугл не умеет работать с некоторыми функциями из формулы (индекс,поискпоз) в массиве, ошибка н/а. Возможно получиться еще этот момент в видео осветить 🙄
@@МаксимПетрик-м9х не получится. Гугл и Excel - совершенно разные программы и невозможно в каждом ролике показывать и для того и для другого. На вскидку могу лишь сказать, что в случае с Гугл лучше будет использовать функцию QUERY - она может найти и вывести ВСЕ строки, отвечающие заданным критериям, в том числе из нескольких диапазонов
Да, придется каждый раз нажимать поиск решения. Автоматизировать можно только при помощи Visual Basic for Applications, написав свой код, который будет "жать кнопку" за Вас
05:05 - после того как вы развернули столбец порядок фамилий в "ФИО сотрудника" изменился. С чем это связано и как сохранить изначальный порядок строк?
Связано со спецификой объединения запросов. Сохранить изначальный порядок можно только создав ПЕРЕД объединением столбец Индекса, по которому после объединения отсортировать таблицу.
Сергей, в данном случае ничего не сделать - Поиск решения не может работать более чем с 200 ячейками изменяемого диапазона, если не изменяет память. Если только модель изменить таким образом, чтобы изменяемых ячеек стало меньше.
@@excel-vba_ru ну и как быть когда ячеек 10 тысяч? И разбить на части не возможно, ну или возможно конечно все, но сумма та общая. и как впихнуть невпихуемое? а надо будет отоборать по трем суммам, которые составляют одну общую по всем 10 тысячам строк, но там пересортица произошла...((((( Помогите пожалуйста!
@@excel-vba_ru просто я уже в состоянии истерической паники от невозможности найти решение как вырулить. Там произошла пересортица в бюджетах. и копейка в копейку даже и была бы возможность у данной надстройки не получится так подобрать. Вы для меня последняя надежда...вот толтко одно но...очень сжатый срок можно сказать один день, и второе но, я знаю, что это дорого стоит, а оплатить я не смогу ((((
Дмитрий, здравствуйте! Пытаюсь разобраться в проблеме - при использовании ПРЕДСКАЗ на агрегированных данных работает нормально, а если строить прогноз детально по продуктам она возвращает отрицательные значения продаж. Можете подсказать, в чем может быть проблем? Та же проблема при использовании ПРЕДСКАЗ.ETS
Светлана, без примера данных проблему не понять и не решить. Возможно, не агрегированные данные имеют тенденцию к снижение, что и показывает ПРЕДСКАЗ. А когда Вы их агрегируете - отрицательные значения "схлопываются" положительными. Попробуйте написать в форум (www.excel-vba.ru/forum/index.php?board=3.0) - там можно прикладывать файлы. Можете и здесь, конечно, попробовать ссылку кинуть - но конф.информацию лучше заранее удалить в любом случае.
Добрый день! У меня почему-то при вводе формулы для коэффициента сезонности когда нажимаю сочетание клавиш cntrl+shift+enter для массива появляется ошибка: «В таблицах нельзя использовать формулы для массивов с несколькими ячейками. Хотя делаю аналогично как показывают на видео
Потому что Вы пытаетесь ввести формулу массива в ячейки умной таблицы. А там этого действительно нельзя делать сразу для нескольких строк. Преобразуйте таблицу в обычную
@@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 цифр и чисел
@@АлександраМартынова-х5х Не думаю, что здесь вообще Поиск решения нужен. Допустим, в А1:А10 у нас числа. В Н1 - произведение. Можно в В1:В10 записать такую формулу массива: =ЕСЛИОШИБКА(ПОИСКПОЗ($H$1;A1*ТРАНСП($A$1:$A$10);0);0) Она отберет только те числа, которые в перемножении с другими числами массива дадут нужное число.
