Проблема решилась самостоятельно и совершенно неожиданным образом: установил более новый Office 2021. Сразу все заработало

Господа, хелп. Может кто-то сталкивался с чем-то похожим? Фиг с ним с Power Query. Сама ошибка Automation Error как-то лечится?

Единственное, что осталось - писать в поддержку Microsoft.

P.S. Выяснил также, что на другой машине файл без проблем открывается и макросы в нем видны. Так что возможно проблема в самом Excel на моем ноутбуке.

Приветствую участников.

Столкнулся с проблемой, которую не могу решить. Имеется файл с расширением .xlsm и макросами в нем. В файле имеется огромная простыня статических исходных данных. Каждый месяц я их готовлю в отдельной книге и делаю копипаст. Недавно я изменил файл: добавил подключение к MS SQL Database через Power Query чтобы эта простыня обновлялась сама без моего участия. После запуска макроса файл перестает сохраняться, а все макросы исчезают из View/View Macros. Последовательно выполняя шаги и сохраняя файл обнаружил такую последовательность:

1. Добавляю Connection к базе данных и подтягиваю данные.
2. Сохраняю файл. Файл сохраняется.
3. Запускаю макрос, отработка успешная.
4. Пробую сохранить файл с результатами. Файл не сохраняется. Вылетает ошибка "Errors were detected while saving..." и следом "File not saved".
5. Все макросы исчезают. То есть физически в файле они присутствуют, но их невозможно увидеть в списке макросов через View. Через VBA я их вижу, но при попытке открыть модуль (любой) вылетает ошибка Automation Error.
6. Работоспособность макросов сохранена. Я могу их вызвать по назначенной кнопке или назначенному шорткату и они отработают.

Итого: не могу зайти в код ни одного из макросов, файл не сохраняется (ни сам по себе ни при попытке сохранить его копию под другим именем).

Можете подсказать хотя бы в каком направлении копать? Вероятно это как-то связано именно с Power Query, так как раньше ничего подобного не наблюдалось.

А, разобрался. У меня слово "(hide)" появляется на листе Work, а не Tech. В Tech у меня просто ошибка, а уже на Work формула ЕСЛИОШИБКА заменяет ее на "(hide)". Обновлю формулу так, чтоб это происходило на листе Tech  

Спасибо!

Первое решение работает, но выполняется много пересчетов (реальный файл очень большой). Это может быть связано с тем, что не отключено обновление экрана перед выполнением макроса?

Второе решение, к сожалению, не работает. Я вставил этот же код и в файл примера, и там то же самое: макрос не реагирует никак.

Добрый день!

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

Есть два листа Work и Tech. На вкладке Tech в столбце A:A описываются марки автомобилей. На самом деле это тоже делает формула из другого источника, формирует список уникальных значений из стороннего диапазона, поэтому я в некоторых строках специально ввел значение ошибки, как мне его отображает формула в оригинальном файле.

На лист Work эти значения подтягиваются в таком же порядке формулой ЕСЛИОШИБКА, которая заменяет ошибочные значения на слово "(hide)". Задача - прописать макрос так, чтобы при любых изменениях диапазона на листе Tech макрос скрывал на листе Work строки со словом "(hide)" в колонке А:А (ну или отображал, если опять что-то изменилось на листе Tech).

Я понимаю, что это делается через макрос Private Sub WorkSheet_Change, вставленный в исходный код листа Tech. Но сам синтаксис осилить не смог.

Пример во вложении.

Вопрос снят, я нашел решение. Все же правильный ход мыслей уже был описан, видимо, я просто допустил ошибку, проверяя этот вариант. Надо привести сумму инвестиции к 5-му месяцу и рассчитать аннуитетный платеж от этой суммы. Файл прикладываю.

Приветствую форумчан.

Столкнулся с проблемой. Есть сумма инвестиции (единоразовая), срок инвестиции и желаемая доходность. Выплата дохода производится равными частями, но начиная с месяца k. Вопрос: как рассчитать аннуитетный платеж? В примере платежи стартуют с 5-го месяца.

Простая формула PMT тут не подходит. Очевидно, что надо как-то учесть: если бы я получал платежи сразу, то я бы их немедленно реинвестировал под ту же доходность. Я много уже разных способов попробовал, например, приводя этот миллион к 5-му месяцу и считая аннуитет от него. Или приводя к 5-му месяцу недополученные платежи за первые 4 месяца, а потом разбрасывал их сумму равномерно по оставшимся месяцам. Все тщетно Есть ли идеи?

Добрый день!

Для получения имени листа использую известную и много где описанную комбинацию =MID(CELL("filename");FIND("]";CELL("filename"))+1;LEN(CELL("filename")))
Но на разных листах эта функция возвращает одно и то же значение. Если нажать F9, функция вернет правильное имя текущего листа, но такое же имя вернет такая же функция на другом листе с другим именем. Есть какое-то решение?

Еще один вопрос. Правильно ли, что в русской версии Excel аргумент "filename" не переведется на "имяфайла"? На всякий случай защитился от этого IFERROR'ом. Но, может, зря нагружаю функцию.

Сергей, вы решили конкретно этот пример А в реале все гораздо сложнее. Я просто набросал примерчик проблемы: данные, на которые надо сослаться, хранятся на другом листе. Которого может и не существовать

Mershik, я не умею пользоваться макросами, плюс ими не умеет пользоваться заказчик этой работы.

Переформулирую вопрос: нет ли других способов обратиться к к листу, которого может и не существовать? То есть интересуют еще функции, у которых в качестве одного из аргументов является имя листа.

Да. Все листы городов выглядят идентично и данные всегда в одних и тех же местах.

А нет ли идей, как обойтись без INDIRECT? Модель должна быть гибкой в плане добавления/удаления городов и стран. То есть набор листов не постоянен и не получится создать прямую связь с листом формулами типа VLOOKUP или INDEX. Во вложении пример того, ЧТО имеется в виду.

Всем привет!

Пожалуйста, помогите идеями ЧТО произошло и КАК бороться. Имеется файл с порядка 20 листами (на каждом достаточно формул, порядка 2000 строк вычислений на каждом листе). Каждый из листов соответствует городу. Есть страновые листы, на которые подтягивается информация по городам соответствующих стран. Есть "глобальные листы", на которые подтягивается дата по странам. И есть dashboard - один лист, на котором в виде срезов представлена разная информация с обилием формул INDIRECT (ДВССЫЛ).

Ситуация: файл "весит" порядка 26 Мб. Но из-за обилия формул пересчитывается секунд 25. С этим можно смириться. Но в определенный момент при попытке добавить копию еще одного из страновых листов файл перестает пересчитываться. Не реагирует ни на F9, ни на ALT+SHIFT+F9. Пересчет формул - автоматический. Методом тыка увидел две ситуации при попытке отключить и включить автоматический пересчет (с целью заставить файл пересчитать все формулы):
1) Внизу справа появляется прогресс пересчета и так и остается на 0%. После 20 минут ожидания и щелчком парой клавиш Excel прекращает работу.
2) Прогресс появляется и тут же исчезает. Файл не зависает, но и не пересчитывает формулы.
3) Если включить ручной пересчет и пересчитывать по очереди каждый лист - пересчет срабатывает.

Х-ки - Windows 10 Enterprise (64 bit), 16 Гб DDR4, 8-ядерный процессор Core i7 8-го поколения. То есть вроде вычислительных мощностей хватает. Я видел и потяжелее файлы раз так в 6, и ничего, пересчитывались со скрипом. Есть ли идеи ЧТО могло произойти?

Лебедева А Ваш - более интересен с точки зрения скорости реализации (меньше дополнительных конструкций)

Спасибо большое всем отписавшимся. Как я понял, мое решение тоже вполне норм. Приму к сведению и предложенные варианты (вариант Михаила очень похож на мой собственный).

Приветствую всех!

Есть следующая задача. Сделать выпадающие список из диапазона A3:A10, в котором будут участвовать только те ячейки, у которых в диапазоне B3:B10 стоит значение "Y". По сути сделать массив {(A3:A10)*(B3:B10="Y")}. Поскольку у меня не получилось придумать как это реализовать именно через формулу массива, пришлось пойти более длинным путем (файл прилагается). Прошу знатоков и всех интересующихся взглянуть на файл и сказать нет ли более изящного решения этой задачи.

Спасибо!

Спасибо! Это оно

Я Вам очень благодарен за помощь. Может, я неправильно сформулировал тему (но в файле я тоже спецом оставил комментарий, что нужно вывести отсортированный массив). Давайте попробую переформулировать.

Есть массив данных A:B. Он не статический, а динамический, результаты в него записываются из других листов. Нужно при помощи формул упорядочить его и вывести в массив E:F так, чтобы он выглядел как отсортированный по описанным критериям: сначала ненулевые по убыванию, потом нулевые в любом порядке. Можно ли что-то придумать?

Уже я вижу, выше есть предложения с формулой RANK. В принципе, практически решение: можно дальше через VLOOKUP по номеру ранга подтягивать город. Но из-за того что у всех нулевых городов ранг одинаковый, VLOOKUP не сработает.

Простите. Я все равно не понимаю КАК это работает. Можно ли сделать Пример, где колонки E:E и F:F будут заполнены формулами и опираться на колонки A:A, B:B и любые вспомогательные колонки (если это необходимо)?

Это еще и условие, которое мне поставили. Без макросов, потому что пользователи часто пугаются, увидев "Enable content". Я понимаю, что звучит тупо, но условие именно такое.

Нужно вывести в отдельном месте. Конечно, я не настолько ламер в Excel, чтобы думать будто можно сделать все формулами в одном месте

Это не статический список, а динамический (там, где он используется). Не проблема сделать дополнительный столбик и формулой все нули в нем заменить на NULL. Но как дальше формулой отсортировать?

Добрый день!

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

Решение макросом не подходит. Файл очень огромный и будет в конце сохранен в формате .xlsb для уменьшения "веса".

МатросНаЗебре, спасибо! Это именно то, что нужно (там была небольшая ошибочка в формуле, но суть понятна).

Здравствуйте!

Помогите с построением массива чисел длиной N, сумма которого равна заданному числу X, а сами значения массива являются значениями функции плотности нормального распределения.

БМВ, помогло, спасибо!

Приветствую уважаемых участников форума.

Не нашел в поиске похожих тем, поэтому создаю новую. Случился у меня такой непонятный кейс. Имеем таблицу в Google Docs и одна из колонок в ней - даты. Формат дат в Google Docs - ДД.ММ,ГГГГ. На вкладке "Формат" убеждаюсь, что вся колонка действительно имеет формат данных "Дата". При импорте данных в Excel через Power Query в предварительном просмотре выбираю импортировать этот столбец как даты. В Excel столбец загружается корректно, в правильно формате (у меня даты читаются как ДД/ММ/ГГГГ). Но при этом все даты оказываются больше исходных на 1462 дня (ровно 4 года, из которых 1 високосный + еще 1 день).

Что интересно, если сделать копипаст из Google Docs этого столбика в Excel, а потом обратиться через Power Query к скопированному столбцу прямо из этой же книги, даты будут распознаны корректно без надбавки в 1462 дня.

Файл не прикладываю, потому что не получается воспроизвести проблемы кроме как на оригинальном документе с конфиденциальной информацией.

БМВ, сейчас пороюсь в поиске и создам новую, если ничего не найду. Большое спасибо за помощь.

БМВ, прошу извинить) My fault. Я завтыкал, что у меня везде 2015 год, а в Е1 я на автомате вносил даты 2019 года.

Не взглянете ли еще на вторую часть вопроса? Я подредактировал свой начальный пост.

БМВ, благодарю за быстрый ответ. Кажется, Ваше решение не учитывает ячейку Е1. Где нужно указывать дату относительно которой ищется последний и предпоследний осмотр.

Приветствую всех!

Дабы не плодить тем, напишу здесь, поскольку мой кейс очень сильно похож с предыдущим и имеет, видимо, похожее решение.

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

а) дату последнего осмотра до заданной даты
б) дату предпоследнего осмотра до заданной даты

Пожалуйста, подскажите решение или в каком направлении двигаться.

2) Здесь же озвучу другой вопрос, связанный с этими мастерами. График их визитов ведется в Google Docs. Через Power Query я вытягиваю даты и в Excel они все оказываются на 1462 дня больше (на 5 лет). Даты идут в нормальном порядке, по возрастанию, но все увеличены. В Google Docs отображаются корректно. Есть ли идеи за счет чего это может происходить?