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

Столкнулся с проблемой, которую не могу решить. Имеется файл с расширением .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, так как раньше ничего подобного не наблюдалось.

Добрый день!

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

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

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

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

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

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

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

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

Добрый день!

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

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

Всем привет!

Пожалуйста, помогите идеями ЧТО произошло и КАК бороться. Имеется файл с порядка 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")}. Поскольку у меня не получилось придумать как это реализовать именно через формулу массива, пришлось пойти более длинным путем (файл прилагается). Прошу знатоков и всех интересующихся взглянуть на файл и сказать нет ли более изящного решения этой задачи.

Спасибо!

Добрый день!

Помогите, пожалуйста, с решением такой проблемы. Есть двухмерный массив с перечнем текстовых элементов (колонка 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 дня.

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

Всем привет!

Прошерстил приемы и поиск по темам по запросу "именован". Не нашел того, что хотел. Суть проблемы.

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

=OFFSET(Sheet1!$H$182,,,,VLOOKUP(Sheet1!$E$5,'C:\Users\username\Documents\[file.xlsx]Sheet1'!$I$2:$J$13,2,)*2+2);Sheet1!$AH$182

Вопрос. Как быстро во всех диапазонах убрать отсылку к другой книге? Я понимаю, что, скорее всего, это сделать можно только макросом. С макросами сталкивался очень поверхностно, но очень хочу научиться. Код VBA видел и даже писал на нем макросы, интуитивно понимая что надо подправить и выполняя пошаговую отладку. Осознанным кодом это назвать было нельзя. Я сначала в визарде создавал макрос, а потом смотрел, как он реализовался в виде кода, и уже этот код правил, там где видел нестыковки. У меня математическое образование и с алгоритмами я знаком. Так что интуитивно там было понятно что к чему. Попробовал и в этом случае так сделать, но не получилось. Пожалуйста, помогите с кодом.

P.S. Я за третий день создаю третью тему. Тут так принято? Один вопрос - одна тема?

Привет!

Суть такова. По работе пришлось создать модель, где я массово использовал юзерские форматы. Во вложенном примере есть файл с числом 1111111. На графике оно должно отражаться следующим образом - "+1,111". Для этого был использован юзерский формат. В частности, единицы в тысячи я переводил таким образом:

И потом еще дорисовывал плюс.

Теперь вопрос. Когда файл открывает человек в другой стране, у которого в региональных настройках разделителем разрядов является пробел, он видит какую-то галиматью в виде "111111,1,". Связано это с тем, что при изменении региональных настроек Excel, видимо, в формулах не заменяет автоматически запятые на пробелы. Формула продолжает иметь вид
, а не

Это как-то решается? Может, я неправильно формат задаю? Надо, чтоб при изменении региональных настроек разделитель в пользовательских форматах менялся автоматически. Дело в том, что во встроенных Custom форматах, которые предлагает выбрать Excel, разделитель групп разрядов меняется автоматически.

Всем привет!

Помогите, пожалуйста, со следующей проблемой. Требуется создать кластерную диаграмму - значение плана и факта за каждый из месяцев. С этим как бы проблем нет. На выходе будет горизонтальная ось с 12 делениями. На каждом делении (месяце) - по два столбца.

После этого надо сделать так, чтобы один столбец слегка закрывал другой. С этим тоже проблем нет: играемся процентами в overlap series.

А вот теперь проблема. После всего перечисленного надо, чтоб каждый из столбцов был еще и стековой диаграммой, состоящей из двух частей. На выходе должно получиться такое: 12 делений, на каждом делении - по два столбца. Один столбец слегка закрывает другой. Каждый из столбцов разбит на две части. Не понимаю, как это можно реализовать и реализуемо ли такое в принципе.

Привет!

Нужна помощь в таком деле. Есть множество одинаковых файлов (больше 300) - распоряжения банка об установке курса обмена валют на каждый день. Соответственно, текст внутри файлов чуть разнится, но в каждом файле присутствует одинаковая таблица с курсами валют. Можно каким-то образом перетянуть эти данные в Excel, чтобы не открывать файл и не копипастить 300 раз?