Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 След.
Автоматизация действий внутри файла excel по его названию
 
Цитата
Joiner написал:
А может проблема в чем-то другом или в ком-то?
Вы философ, или мимо проходили? Я платежеспособный заказчик. И Бизнес-аналитик неплохой. 90% всех своих проблем решаю сам, чтением технической литературы и пользуясь интернетом. То, чего не могу найти, ищу здесь. 5-7 лет назад здесь творили чудеса. Сейчас... увы.
Автоматизация действий внутри файла excel по его названию
 
Две мои последние задачи, включая эту. Если бы сам не нашел исполнителя, эта задача была бы тоже не решена. И мое частное мнение - из 50 любых задач, размещенных в этой ветке, 40 решаются прочтением справочника формул или чтения книжки "Excel для чайников", что я периодически делают и сам. 8 - требуют вдумчивого подхода. 2-е оставшихся  - не решаются вообще. 5-7 лет назад работал с человеком, который специализировался по таким вот 2-м задачам. Жаль, что он больше не работает. Сейчас вижу желание форума помогать в решении первых 40 задач. Жаль. Потому как все мои задачи, в основном относятся к двум последним.
Автоматизация действий внутри файла excel по его названию
 
Нет, не тендер. Здесь никто не отвечает. И уже не в первый раз. Или все улетели на другую планету или кризис жанра. А вообще, нужен человек на периодические задачи, которому не надо разжевывать банальности.
Автоматизация действий внутри файла excel по его названию
 

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

Готов оплатить работу тому, кто сделает быстро и качественно - на карточку.

Мне нужно, сначала, выставить требуемый магазин:

это решается конструкцией типа:

Код
Sub Выбор_магазина()
'
' Выбор_магазина Макрос
'

'
    Sheets("1_Планы_сотрудников_и_магазина").Select
    Sheets("Выбор_магазина").Visible = True
    With ActiveWorkbook.SlicerCaches("Срез_Магазин.")
.SlicerItems("Ашан").Selected = True
.SlicerItems("Billa").Selected = False
.SlicerItems("Маяк").Selected = False
.SlicerItems("Вышка").Selected = False
    End With
    With ActiveWorkbook.SlicerCaches("Срез_Магазин311111")
.SlicerItems("Ашан").Selected = True
.SlicerItems("Billa").Selected = False
.SlicerItems("Маяк").Selected = False
.SlicerItems("Вышка").Selected = False
    End With
    With ActiveWorkbook.SlicerCaches("Срез_Магазин")
.SlicerItems("Ашан").Selected = True
.SlicerItems("Billa").Selected = False
.SlicerItems("Маяк").Selected = False
.SlicerItems("Вышка").Selected = False
    End With
    With ActiveWorkbook.SlicerCaches("Срез_Магазин1")
.SlicerItems("Ашан").Selected = True
.SlicerItems("Billa").Selected = False
.SlicerItems("Маяк").Selected = False
.SlicerItems("Вышка").Selected = False
    End With
    With ActiveWorkbook.SlicerCaches("Срез_Магазин2")
.SlicerItems("Ашан").Selected = True
.SlicerItems("Billa").Selected = False
.SlicerItems("Маяк").Selected = False
.SlicerItems("Вышка").Selected = False
    End With
    Sheets("Выбор_магазина").Select
    ActiveWindow.SelectedSheets.Visible = False
   
End Sub

Второй макрос, на более ранней стадии, открывал файл, обновлял все сводные, сохранял файл в папку OneDrive, а сам файл закрывал.
Код
Private Sub Workbook_Open()

Application.DisplayAlerts = False
ActiveWorkbook.RefreshAll 'обновление сводных таблиц

Application.Wait (Now + TimeValue("0:00:10"))

ActiveWorkbook.SaveAs Filename:= _
        "https://pgroup-my.sharepoint.com/personal/p_pgroup_onmicrosoft_com/Documents/Ашан.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Application.Wait (Now + TimeValue("0:00:10"))

ActiveWorkbook.Close

Теперь хочу несколько изменить и  объединить оба макроса в один  и повесить все действия на открытие файла и его название.
1. Открыть файл.
2. Открыть скрытый лист.
3. Выставить .SlicerItems("Название_файла").Selected = True
4. Сделать паузу для обработки срезов ( видимо из-за не очень чистого метода, процесс занимает секунд 15-20)
5. Закрыть скрытый лист
6. Запустить обновление всех сводных таблиц с внешнего содержимого
7. Сделать паузу для обработки сводных
8 Сохранить файл.
9.Сохранить его копию в хранилище OneDrive. ( раньше копии не делал )
10. Закрыть файл
Напрямую, в папке OneDrive ничего корректно не работает. Файл обрабатывается в другой папке, а потом принудительно заменяет собой уже существующий файл в OneDrive.

Алексей
.

макрос закрытия всех файлов по событию открытия файла
 
Решил все иным образом.Исходная база теперь закрывается командой через .bat файл
Код
@echo on
taskkill /f /im excel.exe
Изменено: Алексей Рубцов - 08.07.2019 13:09:39
макрос закрытия всех файлов по событию открытия файла
 
Мне надо закрыть все книги. Включая ту, где находится мой код:
Код
Sub yy()
Application.Quit
End Sub
А код закрывает все книги, кроме нее. У меня Office 2016
Изменено: Алексей Рубцов - 08.07.2019 12:58:55
макрос закрытия всех файлов по событию открытия файла
 
Перед тем, как задать вопрос, сделал такую конструкцию:
Код
Private Sub Workbook_Open()
Application.Quit
End Sub
Не работает. Файл закрывает сам себя, не трогая остальные.

Код
Private Sub Workbook_Open()
Sub yy()
Application.Quit
End Sub
А вот такое вообще не работает.
Изменено: Алексей Рубцов - 08.07.2019 10:54:28
макрос закрытия всех файлов по событию открытия файла
 
Уезжаю в отпуск, стоит задача автоматизировать выполнение своих функций.
В программировании на VBA я полный профан, но мне хорошо удается  подбирать и комбинировать подходящие мне решения. Свои обязанности я автоматизировал частично макросами, частично через .bat и powershell
Все получилось, но не могу сделать последний штрих. Закрыть файл с исходной базой перед усыпление компьютера.
нашел в сети такой макрос:
Код
Sub yy()
Application.Quit
End Sub
Он крайне эффективно закрывает все файлы Excel. И запускаю я его через Powershell. Но как мне повесить его на событие открытия файла?
Изменено: Алексей Рубцов - 08.07.2019 10:40:31
Открытие, сохранение и закрытие группы файлов excel, Нужен макрос открывающий последовательно группу файлов, таймер работы, сохранение и закрытие, из другого файла excel.
 
Вы о чем?  Какая другая ветка? Я выдал ТЗ. Жду предложений по реализации. Пока не вижу. У меня работает макрос внутри файла. Но он мне, в одном случае, сильно мешает основной работе. В другом случае, вообще периодически приводит к синему экрану. Поэтому мне нужно внешнее решение.
Изменено: Алексей Рубцов - 05.07.2019 22:26:33
Открытие, сохранение и закрытие группы файлов excel, Нужен макрос открывающий последовательно группу файлов, таймер работы, сохранение и закрытие, из другого файла excel.
 
Экспериментируя с макросами, добился 100% результатов, внедряя в сводные отчеты такой вот макрос:
Код
Private Sub Workbook_Open()

Application.DisplayAlerts = False
ActiveWorkbook.RefreshAll 'обновление сводных таблиц

ActiveWorkbook.Save

Application.Wait (Now + TimeValue("0:00:10"))

ActiveWorkbook.Close

End Sub

В базе же с PowerPivot, он или любой другой, приводит на старте файла к бесконечной загрузке . Net Framework и полной блокировке обновлений.

А еще нашел вот такую конструкцию:

Закрыть одну из книг, если макрос находится в другой, можно так:
Код
Код
Application.DisplayAlerts = False
Windows('Книга2').Close
Application.DisplayAlerts = True

Я так понимаю, что и остальные события можно заложить в этот макрос. И список файлов...
Изменено: Алексей Рубцов - 05.07.2019 19:42:13
Открытие, сохранение и закрытие группы файлов excel, Нужен макрос открывающий последовательно группу файлов, таймер работы, сохранение и закрытие, из другого файла excel.
 
Есть группа файлов, представляющая собой центральную базу данных и отдельных excel-отчеты на сводных таблицах. Центральная база работает на PowerPivot и категорически не желает работать с макросами. Сводные отчеты, которые обновляются при открытии, с этой базы, потом синхронизируются с облаком OneDrive и там макросы тоже нежелательны. Они блокируются, а на экран выводятся системные сообщения, смущающие моих коллег.
Мне нужен отдельный файл .xlsm который бы позволил последовательно запускать требуемые мне файлы, давать им время на обновление, а потом сохранять и закрывать.
Желаемый интерфейс в аттаче. Сам файл будет запускаться через батник и планировщик задач.
СРОЧНО.
Подмена данных по нескольким условиям в PowerPivot, Подмена данных по нескольким условиям в PowerPivot
 
У меня в Pivote сидит выгрузка самописной базы Oracle 10 с продажами сети магазинов за 5 лет.  В ней, ввиду полнейшего отсутствия документации,  пришлось в сжатые сроки  делать обратный инжиниринг. А потом лепить кучу костылей под неисправляемые ошибки. У меня модель не просто захламлена лишними расчетными столбцами. Их там несколько сот в 30 мега таблицах. с многочисленным дублированием с разной математикой для перепроверки расчетов. И от фиксированных значений, я бегаю как черт от ладана. Потому как это единственная панацея отловить баг в очередной немыслимой рабочей ситуации.
Подмена данных по нескольким условиям в PowerPivot, Подмена данных по нескольким условиям в PowerPivot
 
Попробовал. Работает. Но надо еще смоделировать реальную ситуацию. Я настроил чертовски многогранную мотивацию персонала, но постоянно находятся умники, которые создают совершенно невозможные варианты своей деятельности, от которых я просто шалею.
Подмена данных по нескольким условиям в PowerPivot, Подмена данных по нескольким условиям в PowerPivot
 
Да. Сработало. А чем Ваша конструкция  && 'DTable'[EMPLOYEE_ID] = EARLIER('DTable'[EMPLOYEE_ID])) отличается от моей FILTER('DTable'; 'DTable'[EMPLOYEE_ID] = EARLIER('DTable'[EMPLOYEE_ID])) и можно ли таким же образом  добавить && 'DTable'[COMPANY_ID] = EARLIER('DTable'[COMPANY_ID]))?
Подмена данных по нескольким условиям в PowerPivot, Подмена данных по нескольким условиям в PowerPivot
 

Андрей, большое спасибо.НО... В Вашем примере все работает.  А когда я перенес вашу формулу простым копированием в свою базу, с  совершенно идентичными полями, все работать перестало. И в бубен стучал, и в рельсу. Не работает. Заменило всем POSITION ID на "2". Игнорируя все установки.
Второй момент. В формуле не учтен EMPLOYEE_ID. В тебеле, одномоментно, находится около 700 человек. И идут они вперемешку. Кто как табель заполняет.
Третье, это я упустил, есть 10 магазинов. характеризующихся признаком COMPANY_ID.
Попытался я добавить в  вашу формулу фильтры EMPLOYEE_ID и COMPANY_ID. У меня всегда получалось в аналогичном синтаксисе, типа:
FILTER('DTable'; 'DTable'[EMPLOYEE_ID] = EARLIER('DTable'[EMPLOYEE_ID])) и FILTER('DTable'; 'DTable'[COMPANY_ID] = EARLIER('DTable'[COMPANY_ID]));
Но здесь не прошло. Как заколдовано. Я что то сделал не так?
Приложил оригинал базы с двумя магазинами и Вашей формулой. На всякий случай - в вашем примере EMPLOYEE_ID  = 245 420

Подмена данных по нескольким условиям в PowerPivot, Подмена данных по нескольким условиям в PowerPivot
 
Пишу систему мотивации персонала. Периодически случается такая ситуация, что некий сотрудник со своим идентификационным кодом EMPLOYEE_ID, переходит с должности POSITION ID  "Стажер" в должность "Продавец" в середине месяца. Этот факт регистрируется сменой идентификатора  POSITION ID  с "18" на "2"
И сразу меняются порядка десяти факторов расчета, связанные с должностью. Я не могу их не усреднить, ни сложить впоследствии. Вижу выход в том, чтобы внутри месяца отслеживать изменение POSITION ID  с "18" на "2". И, если данный факт был, то заменить внутри месяца все "18" на "2" только по конкретному сотруднику EMPLOYEE_ID.
Возможно такое реализовать?
Подстановка недостающих данных из предыдущих периодов в PoverPivot
 
"Лучше подобное делать в Power Query"
Представьте себе, что приходите Вы на крупное предприятие, где самописная СУБД на базе ORACLE с 300 таблицами без документации и описания. И без возможности ее изменения. И без понятия, какие грабли тебя ждут. И наступаешь на них каждый день. Потому что базу делали и латали непрерывно 15 лет. Поэтому я выгрузил все ядро, которое смог определить, сразу в PowerPivot. И создал более 100 расчетных полей.
Хорошо хоть, обрезал 1,5 млн записей, а то бы не нашел достаточно мощного ПК, чтобы это запустить.
Подстановка недостающих данных из предыдущих периодов в PoverPivot
 
Андрей, спасибо за интересное решение. Но я не смог, даже, заполнить пустые места недостающими номерами. То, что хорошо сработало на малом объеме, не смогло
рассчитаться на 450 тыс строк продаж, 10 магазинах и 100 людях персонала.
Это при том, что у меня Core i5-6500 +16GB+SSD PCI-E. 10 раз запускал процесс. Ни разу не смог довести до конца. Реализовал другое решение, крайне грубое, но очень быстрое. Использовал конструктив  ISBLANK[Должность];LOOKUPVALUE... с подстановкой данных из предыдущей даты. Если данных не оказалось, то  повторяю формулу в следующим столбике, с проверкой предыдущего результата. И так 14 столбиков, со смещением в 1 сутки.  Конечно, грязное решение, но не напрягает ПК совсем.
Изменено: Алексей Рубцов - 07.05.2019 17:25:58
Подстановка недостающих данных из предыдущих периодов в PoverPivot
 

Не знаю, уже каким простым языком описать мою задачу:
"Допустим, три продажи сотрудника прошли 12.03,13.03 и 14.03, когда человек был дома. В магазине - в учетной системе, остались его персональный код  и его ФИО. Но нет кода должности и самой должности.

Зато есть ликвидная запись от 10.03.2019, где указаны Код сотрудника, ФИО и Должность с ее кодом
Вопрос: Как мне подтянуть эту должность и ее код в нужные дни?"

Таким образом, вопрос звучит так, как подставить последнюю актуальную и явно указанную должность сотрудника, в ту последующую запись, где эта должность отсутствует? У меня в наличии не оптимизированный массив субд Oracle. Я не могу его оптимизировать. И работаю с ним, как есть. Я выгрузил в PowerPivot около 40 таблиц и обработку мне нужно сделать там же.

Подстановка недостающих данных из предыдущих периодов в PoverPivot
 
Сделал формулу подсчета продаж сотрудников. Там, где нет должности - BLANK().  Формула игнорирует любое количество пропусков дат.
Идея: используя формулу LASTONBLANK, подставить последнее не пустое значение из столбца "Должность"
Изменено: Алексей Рубцов - 03.05.2019 18:18:30
Подстановка недостающих данных из предыдущих периодов в PoverPivot
 
Андрей,
Это построение не учитывает временные периоды.  Сотрудник до 5 числа может быть стажером, с 6 до 20 продавцом, а с 21 по 31 администратором.
А ваша формула, в этом случае, возвращает три результата в одну ячейку. В итоге #Ошибка.
Я предполагаю, что решение находится в использовании функции LASTNONBLANK(), где, при совпадении [Код _сотрудника] будет возвращаться последнее заполненное значение [Код_должности], без учета того, сколько дней пропущено. У меня есть некоторые промежуточные результаты, но полное решение не дается.
Изменено: Алексей Рубцов - 03.05.2019 17:44:12
Подстановка недостающих данных из предыдущих периодов в PoverPivot
 
Спасибо за совет. Но зачем мне информация, о которой я не просил ? Я выгрузил в PowerPivot 2 млн записей. Задачу описал. Сотрудник до 5 числа может быть стажером, с 6 до 20 продавцом, а с 21 по 31 администратором. % от продаж привязан к должности. Которой нет в явном виде. Мне нужен совет, как подтянуть эту должность из предыдущей записи, где она есть. Потому как, вознаграждение будет складываться из трех составляющих.
Подстановка недостающих данных из предыдущих периодов в PoverPivot
 
Будь это обычный Excel, я бы решил задачу за 3 минуты. Но у меня база Oracle с 50 связанными справочниками и 2 млн записей. И записи идут совсем не по порядку. Поэтому только PowerPivot. И я не понял сути классического подхода...Мне нужно внести недостающие данные в исходный массив. Потому как эти данные участвуют в расчетах нескольких десятков параметров. И выводить их в сводную мне не нужно.
Изменено: Алексей Рубцов - 02.05.2019 18:18:39
Подстановка недостающих данных из предыдущих периодов в PoverPivot
 

Сотрудники небольшой сети магазинов имеют привычку продавать товар, даже находясь дома. Делают они это, в основном, своим постоянным клиентам. Для этого, их коллеги, на рабочем месте, проводят продажу по их личному коду.
И тут возникает коллизия: По табелю, этих сотрудников нет на работе.  Справочник должностей, жестко привязан к табелю учета рабочего времени. Нет должности - нет бонусов за продажи.
А мне, как раз и надо рассчитывать эти бонусы, автоматически и быстро.
Допустим, три продажи сотрудника прошли 12.03,13.03 и 14.03, когда человек был дома. В магазине - в учетной системе, остались его персональный код  и его ФИО. Но нет кода должности и самой должности.

Зато есть ликвидная запись от 10.03.2019, где указаны Код сотрудника, ФИО и Должность с ее кодом
Вопрос: Как мне подтянуть эти должность и ее код в нужные дни?

Изменено: Алексей Рубцов - 02.05.2019 17:49:04
Не могу поделиться результатами работы в PowerPivot через Excel Online
 
Файл открывается, редактируется и становится полностью работоспособным под учетной записью One Drive с сервисом SharePoint, будучи помещен в Online хранилище. Т.е. недостаточно открыть ссылку на файл на другом ПК. Файл должен быть физически помещен в хранилище One Drive адресата c действующим сервисом SharePoint. Осталось выяснить малое. Есть 2 дешевых тарифа с SharePoint. За 12,5 енотов с Офисом 365 и за 6 баксов в месяц, с возможностью работать только с excel online в браузере.
В 12 долларовом тарифе все работает, завтра подключу 6 долларовый и отпишусь.
Не могу поделиться результатами работы в PowerPivot через Excel Online
 
Коллеги, прочел две Ваших рекомендации. Поясню ситуацию. Я месяц разрабатывал концепцию очень сложного проекта, а потом еще неделю переписывал его под PowerPivot и у меня уже нет возможности переписать продукт третий раз, поэтому вопрос по существу. Что я делаю не так?  У меня самый дорогой тариф Office 365 для бизнес-групп E5.
И ни один из моих контрагентов не может просмотреть мой файл. Ни по ссылке, ни в составе рабочей группы, ни с разрешением работать в Power BI
Когда я открываю свой файл, то ссылка на него следующего вида https://company-bla-bla.sharepoint.com,  
Ключевое слово SharePoint. Я уже зарегистрировал еще один бизнес аккаунт - с Шарепоинтом. И все то же то же самое -
"Ваш файл обращается к внешнему содержимому или содержит инструменты бизнес-аналитики"
Не могу поделиться результатами работы в PowerPivot через Excel Online
 

Коллеги,

Поставили мне задачу автоматизировать большущий объем сложных расчетов и полученный результат сделать доступным для просмотра в online для партнеров в разных концах страны.

Сделал, положил в One drive, расшарил через Excel Online и получил сообщение: «Excel Online поддерживает файлы до 10 мегабайт, воспользуйтесь моделью PowerPivot».

Воспользовался. Сжал файл в 50 раз. Выложил в свой One  Drive, запустил Excel Online и получил следующее сообщение:

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

Ладно. Зарегистрировал тестовый аккаунт Office 365 для малых предприятий уровня Е5. Это который максимальный, аж дальше некуда. С ценой в 504 убитых американских енота в год

(35 х 12 х 1,2) . И единственный, в котором заявлена поддержка Power BI Pro.

Запустил. Работает. Криво, медленно. Но работает. Файл размеров 2 мегабайта ворочается так, как будто он весит 500 мегабайт.

Сделал ссылку на него, отослал. Человек присылает скрин с экрана:

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

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

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

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

Или я что-то не учел?

Вопрос по формуле PowerPivot, Расчет отличия от вчерашнего дня
 
Ок. Я переоформлю, но позже. Очень спешу с переделкой проекта. Его надо было сдать вчера утром. А я только сегодня нашел основное  решение.
Вопрос по формуле PowerPivot, Расчет отличия от вчерашнего дня
 
Вот, то что получилось
Вопрос по формуле PowerPivot, Расчет отличия от вчерашнего дня
 
=if('Исход 2'[Вчерашний остаток]=0;0;CALCULATE(sum('Исход 2'[кол-во]);FILTER('Исход 2';'Исход 2'[Дата]=EARLIER('Исход 2'[Сегодня]));FILTER('Исход 2';'Исход 2'[Коды ТМЦ и Дистрибьютора]=EARLIER('Исход 2'[Коды ТМЦ и Дистрибьютора])))-CALCULATE(sum('Исход 2'[кол-во]);FILTER('Исход 2';'Исход 2'[Дата]=EARLIER('Исход 2'[Вчера]));FILTER('Исход 2';'Исход 2'[Коды ТМЦ и Дистрибьютора]=EARLIER('Исход 2'[Коды ТМЦ и Дистрибьютора]))))
Страницы: 1 2 След.
Наверх