попробуйте charticulator - можно загрузить свои данные, накидать график, выгрузить картинкой, или html для вставки в email рассылку, или экспортировать как кастомный визуал power BI.
Малую автоматизацию можно вести в open source инструментах, используя файлы xlsx. на прошлой неделе обновилась python библиотека Mito, теперь её можно запустить из Jupiter notebook (ранее работала только в Jupiter lab) - позволяет работать с большими данными + графики + сразу генерирует код (аки запись макроса). Полученный код можно вставить в KNIME, в python snippet нод и сохранить в excel. Далее в knime используя ноды continental настроить условное форматирование в файле. Если нужно вставить проверку данных(выпадающие списки) , то можно воспользоваться пакетом openxlsx в R (код в knime в R snippet нод). В R есть также пакеты для формирования word файлов и презентаций PowerPoint (officer и R2PPT) . Взаимодействие с COM объектами excel возможно в R с помощью пакета excel.link, в python библиотека win32.client. Для тех у кого Linux есть ещё прикольная утилита visidata - мощный табличный редактор в терминале, также с возможностью записи Пайтон кода.
бонус - с помощью пакета excel.link можно в excel запускать R код, а используя пакет reticulate выполнять python код. Таким образом можно формировать код формулами экселя и выполнять его. Ещё один вариант использования - работа с большими данными - используя duckDB можно хранить данные локально в паркет файлах, а в excel получать нужные срезы (в R и python есть клиент для duckDB). В этом месяце у duckDB появился нормальный odbc драйвер. Эксель в таком случае просто как пользовательский интерфейс к базе получается.
проверка качества данных: в R пакет validate, в python библиотека great expectations - покрывает всё потребности.
Оркестратор - dagster. Не требует docker, работает в windows.
интерактивные приложения делать с помощью пакета shiny в R или в питоне streamlit, если есть docker, то appsmith лучший low-code инструмент для веб приложений. BI для self-service лучше делать в metabase, в него недавно завезли модели данных как в клик или power bi.
Иногда это велосипед, но иногда очень может выручить.
Удобное внедрение языка программирования "Python" в файл Excel на основе портативной сборки Python, Настроенный файл Excel для удобного вызова скриптов Python из Excel
Ок) чую задел очень много холиварных вопросов. Большое почтение и уважение всем жителям форума!
1) Да, Вы правы, про узкий табличный сегмент - мы боремся только с отчетностью в excel. Основная ЦА - руководители и аналитики - показываем что многостраничные отчеты можно заменить на дашборд со сценарным анализом. условное форматирование может делать etl, графики/диаграмы в bi, собыитя листов - в логику workflow, мат. стат. вынести в R/Python/H20. 2) Передаем знания уже существующим сотрудникам, в отделе кадров, в налоговом департаменте, в отделе отчетности - находим сотрудника, готово поддержать отчет, с большим ИТ договариваемся о интеграции. Поддержка БД с нашей стороны (уже нанятыми архитекторами), поддержка источников - большое ИТ, настройка параметров отчета - пользователи. 3 - 4 вопрос - статистики нет, есть личные наблюдения - вижу с каким трудом и каким ценником приходили люди на позиции с которых я уходил, и вижу как проще и дешевле передать на поддержку в отдел etl + bi решение.
Я не спорю, что excel был и будет, иногда кроме vba нет вообще альтернатив (и в таких конторах работал). Просто сейчас появилось много инструментов, которые могут облегчить боль от работы с данными (надстройка с вызовом питона, что в этой ветке - в их числе), и многие о них даже не слышали. Я лишь за расширение кругозора. Иногда многие вопросы типа обработки сотен json или работы с большими цифрмаи могут быть проще решены вне excel.
P.S: однажды мне на смену пришел сотрудник, который не посмотрел ни один мой макрос, не открыл ни одну access бд,сказав что он просто не знает макросы, но умеет в питоне. За полгода он переписал все. Тогда я понял, что рынок труда меняется, и изучил питон.
Удобное внедрение языка программирования "Python" в файл Excel на основе портативной сборки Python, Настроенный файл Excel для удобного вызова скриптов Python из Excel
Добрый день. у нас в Self-service команде (автоматизация внутри бизнес подразделений) сложился следующий стек - 1) получение данных из различных источников (excel, txt, rest api, xml, json) - используется бесплатный KNIME (выгрузку в эксель и условное форматирование можно делать в нем тоже) 2) проверка качества данных - OpenRefine, Talend Data Quality Stdio, и сейчас внедряем dbt, greate_expectations в связке с оркестровщиком Dagster 3) процессинг данных - R/Python - смотрим что, в каком инструменте проще выполнить (в workflow knime можно вставить выполнение Python/R кода, даже передать данные в jupiter notebook и получить результат обратно в поток). 4) для временных таблиц sqlite и сейчас смотрю на duckDB 5) Основное хранение данных - три физических слоя ODS, DDS, ADS в PostgreSQL (для BI данные переливаются в SQL Server) 6) BI платформа - PowerBI, сам смотрю за развитием metabase. 7) Запуск по расписанию - оркестровка - Dagster
К нам обращаются бизнес отделы с кучей экселей и макросов, мы переделываем это на нормальные etl, создаем нужную схему данных в бд, разрабатываем BI отчет - в итоге пользователи слезают с иглы портянок в экселе. Обучаем ключевых пользователей в бизнес отделах пользоваться KNIME, PowerBI и отдаем отчет им на поддержку. Также практика показывает, что найти на рынке спеца знающего VBA дороже чем студента знающего Python.
Удобное внедрение языка программирования "Python" в файл Excel на основе портативной сборки Python, Настроенный файл Excel для удобного вызова скриптов Python из Excel
Основной интерес в подобных инструментах вызывает возможность выполнять код, который хранится в ячейках листа. Таким образом можно в зависимости от выбранных пользователем настроек запускать не только скрипты, формировать графики, но и динамически менять контент веб-отчета. Как я понял для данной надстройки - это только в платной версии. для языка программирования R есть пакет excel.link который бесплатно имеет подобный функционал - с помощью пакета reticulate из R можно запускать пайтон скрипты, а результаты и графики сразу встают в excel. можно гибко формировать код веб-приложений в shiny, или менятm настройки ml моделей (keras в том числе),
Но это все интересно только в случае если Вам прямо сейчас из экселя интересно что-то посмотреть. Для отчетности, автоматизации - лучше использовать etl инструменты в совокупности с python/R скриптами. Например, любые excel можно легко загнать в KNIME (аналитическая платформа для работы с данными) - сделать необходимые преобразования, трансформации и передать далее в Python/R/Jupiter notebook, получить результат и сразу залить в любую бд, или выгрузить в excel - все бесплатно и open source.
в Python сейчас набирает популярность библиотека mito - с ее помощью можно в интерактивном режиме работать с таблицей, при этом генерируется pandas код, который потом можно выполнить в питоне или в KNIME. Получается как запись макроса в экселе, но для питона. В такой связке VBA вообще не нужен. (Я раньше очень много писал на VBA но теперь им совсем не пользуюсь - переделываю чужие макросы на нормальные etl)
etl процесс проще документировать, проще разрабатывать и проще поддерживать. P.S: Если нужно машинное обучение - в KNIME есть много инструментов для работы с текстом, изображениями,есть интеграция с Keras и H2O(automl в том числе), excel можно использовать для хранения настроечных таблиц.
Расскажу о еще одном способе монетизации. когда - то занимался машинным обучением. Отправили меня в Москву знакомиться с главным специалистом аналитического отдела. Он очень ценился благодаря своей супер формуле расчета кой-какого значения. Мне повезло - мне дали код этой формулы. Оказывается там куча фиктивных переменных, извлечение корня и возведение в степень выражений=1. В итоге рассчииывалась обычное скользящее среднее. Его зп в три раза превышала мою. Вот так монетизация.
Удаленка невозможна, большая и очень инертная кампания. Куча источников и совсем отсутствует слой данных. Приходится руками собирать. О всех трудностях глубокой аналитики без прав локального админа и с ограниченными интернетом я уже писал когда-то. Конечно помимо макросов требуется экономическое образование и аналитический склад ума, но не в этом суть. Люди идут из других контор, бьют пяткой в грудь что могут все. А по факту цикл по ячейкам написать не могут. Зп реально выше рынка, но в отсутствии предложения будем искать студента на грейд ниже. из excel пока можно работать со Скриптами R/python/c++/ fortran. остается лучшим пользовательских интерфейсом для ввода хотелось. Дальше вешать кнопку и запускать все что угодно.
День добрый, смотрел в сторону freelance на международном рынке - везде перебивают индусы. Они готовы писать макросы за копейки. Причем у многих есть хорошие отзывы. Мне на работе за час больше платят. на работе монетизировать-у нас в Самаре кадровый голод знающих vba. Два месяца ищу себе помощника 50-60к на руки - никого нет, кто бы знал как for... To.. Next написать.
День добрый. Для раскрытия темы выходных дней - во вложении макрос собирающий все выходные дни из Консультанского производственного календаря. Каждый год скачиваю календарь в doc формате, дергаю даты, потом уже использую всевозможные формулы.
Сейчас появился еще один вариант, используя веб-запрос, дажеR пакет сделали с его использованием. Но пока непонятно как быстро и как долго будут обновлять данные о выходных днях.
Всегда пользуюсь fuzzyvlookup функцией от Уважаемого Pharmaprofi. В надстройке все модули открыты. Просто переношу модуль в свою книгу и использую как udf или в vba.
День добрый. Возможно это уже оффтоп, кратко по вопросу: Не разрешено политикой безопасности. Аналитика - это бизнесовая часть, не входит в иерархию ИТ-трудно лоббировать свои хотелки. До вируса Петя можно было все,после ничего. Служебками можно выбить установку по на свой комп, Но результат твоей работы должен быть воспроизводим на пк других пользователей.
Маленькое дополнение к предыдущему посту: Для каких задач используется: 1) Excel + R + sqlite - любые задачи обработки, хранения, визуализации данных. Пакеты data.table, dbplyr, tidyverse для обработки, ggplot2, shiny - графики, веб отчёты. Выполняемых код, находится на листе, его можно менять формулами, результаты работы сразу в Excel. 2) knime - парсинг json от рест запросов к веб сервиса, ETL для бд, прогнозирование нагрузки контакт центра. 3) talend - обработка больших данных (более 10 млн. Строк), качественные тесты данных, join big data, Etl.
Добрый день. Давно просматриваю данную ветку - очень знакомая боль. Хочу поделиться своим опытом.
На всех предыдущих местах работы - ритейл, банковский сектор, нефтянка - обычному пользователю и главному специалисту не дают прав локального администратора. Нет возможности зарегистрировать dll, сделать pip install, тем более поставить borland or c++.
Использую следующую экосистему: 1) Excel + R (portable version) + sqlite: Не требует прав администратора, R легко конектится к бд, можно делать параллельные вычисления, куча пакетов для json. Пакет Excel.link позволяет запускать R из Excel vba. Пакет RSQLite позволяет лить все в локальную бд избегая переполнение памяти.
2) аналитическая платформа knime: Легко работать с json, rest api, любые бд. Итоговый workflow компилируется в bat файл, в Excel макрос готовит входные данные, потом запуск bat.
3) Talend Di - любые бд, любые данные, итоговый workflow компилируется в jar архив. Из Excel запускается консольная команда. Talend esb - можно запустить свой rest api и уже из Excel посылать post запросы.
Все опен сорс, все быстрое, доступное обычному пользователю. Access использовал только как adp к SQL серверу.
день добрый, обработку большого xml можно вести в Talend Data Integration Studio (бесплатное open source ПО) - легко можно создать любой ETL workflow. job можно скомпилировать в jar и запускать по расписанию. Результат работы можно писать в любую БД. Для себя сейчас открыл мощную связку Excel/Access (ввод данных) -> VBA->sqlite (data storage)- не требует развертки сервера и прав администратора.
Уважаемые коллеги, вечер добрый. Помогите пожалуйста с преобразованием словаря в обычную таблицу на листе. Источник данных: Веб сервис - POST запрос - получаю JSON. его я могу распарсить в R и ручками привести к таблице (муторно и лень), распарсил в KNIME, перевел в XML, далее через XPATH (не совсем удобно для конечного пользователя - нужно делать веб морду) Хочу в excel.
Сам источник представляет собой 18 столбцов-списков, по одному документу/записи могут быть 5 значений в списке номер 3 и 7 значений в списке 8 и т.д. глубина списка динамическая величина.
Огромное спасибо EducatedFool за совет на просторах форума по считыванию ResponseBodyвместо RespinseText для борьбы с кодировкой. Получил в excel JSON, распарсил его через CDataset.xlsm (уже обсуждался на форуме) - на выходе Dictionary с child.
Есть ли способ перечислить в строку все значения каждого списка, с учетом иерархии?? Т. е если для какого-то ключа встречается вложенный словарь - то он выводится в новые строчки, а названия ключей вложенного словаря в название столбцов.
пример JSON {Имя:Иванов; Фамилия : Петров; Любимый цвет : {цвет1:красный; цвет2:синий} аля словарь Имя: Иванов Фамилия: Петров Любимый цвет Любимый цвет - child(1) цвет1 = красный Любимый цвет - child(2) цвет2 = синий
Как хочу Имя Фамилия Любимый цвет Иванов Петров красный синий
Прошу прощения, может быть непонятно объяснил, давно к vba не подступал - забыл уж многое.
Доброго дня, всем. Просьба особо не пинать - я опять со своим R - просто как альтернатива. Сейчас полностью ушел в data mining в ритейле - для решения задач функционала экселя просто не хватает. Сделал следующую связку - эксель для сбора данных от пользователей (настройки, параметры расчетов), по кнопке вызывает R portable version, где применяются расчеты с техниками машинного обучения, результат выводится в виде веб-страницы (пакет Shiny). Таким образом можно работать с big data сохраняя удобный для пользователя интерфейс. Решаемые задачи - прогноз продаж по категориям с дневной календаризацией с учетом динамической сезонности, парсинг данных с сайтов, обработка REST запросов.
to JeyCi - вставлю свои пять копеек, я опять про R. На данный момент - это лучшая среда для парсинга сайта (через него парсю авито). google пакеты rvest (легкий парсинг по css/xpath), rSelenium (тут и тут есть туториал). В rSelenium lля динамических сайтов используется браузер phantomjs, сайт прогружается в браузере, а потом его структура конвертируется в обычный html.
p.s: в этом году microsoft выкупила платформу разработки R, теперь R это официальный мелкософтный язык программирования. устанавливается без запроса прав администратора тыц. R интегрирован в oracle и sql server.
эту надстройку пробовали? позволяет самому задавать вычисляемое поле, но только на уровне выводимых в сводную таблицу данных. Если нужно создать новую вычисляемую меру, которая будет агрегироваться на всех узлах куба - тогда нужно запускать выполнение mdx запроса
Код
"With MEMBER [Measures].[my_calc] as iif([Measures].[ACT REST]>0,[Measures].[ACT REST]+1,null) " & _
"SELECT Non Empty (...) ON COLUMNS, " & _
"NON EMPTY (...) ON ROWS " & _
"From ...
"WHERE (..., " & _
"[Measures].[my_calc] ) " & _
"CELL Properties Value, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR,FONT_FLAGS"
вашу задачу можно решить используя таблицы подстановки. но реализовать функцию на vba не получится (в пользовательских функциях нельзя задавать значение ячейке ((() в который раз не могу вставить файл в сообщение (chrom). киньте в личку почту - вышлю файл. вместо этого лист с расчетом можно скрыть, на пользовательском листе сделать выпадающий список с периодами, по которым будет подтягиваться расчитанное значение.
Игорь, спасибо за ответ. Перебором не хотелось - из-за потери времени (очень долгая выгрузка исходных данных из источников). Кроме формул присутствует еще и форматирование. но видимо нет другого пути. Вопрос возник так как вручную формула проставляется на группе листов, макросом нет - чую делаю что-то не так.
Уважаемые коллеги, добрый день. Не могу найти решение следующего вопроса - нужны знания гуру теории excel: Макросом генерирую выгрузки одинаковых по своей структуре листов, собираю в одну книгу, далее мне нужно вставить два столбца с формулами. Столбцы добавляются на всех выделенных листах, но формула - только на активном. (При изменении формулы макросом выделение группы не слетает)
Код
Sheets(2).Activate
For I = ActiveWorkbook.Sheets.Count To 2 Step -1 'выделил нужные листы
Sheets(I).Select (False)
Next
Columns("AH:AI").Select : Selection.Insert Shift:=xlToRight 'вставил столбцы
Range("AH4").FormulaR1C1 = "=SUMIFS(R[-1]C19:R[-1]C30,R2C[-15]:R2C[-4],"">=""&DATE(YEAR(NOW()),1,1)&"""",R2C[-15]:R2C[-4],""<""&NOW()&"""")" 'вставил формулу
как вариант - перебор листов, но хотелось бы избежать этого.
Код
Sub AllWorkSheets()
Dim Arr() As String
Dim I As Long
ReDim Arr(Worksheets.Count) :Arr(0) = Worksheets.Count
For I = 1 To Worksheets.Count : Arr(I) = Worksheets(I).Name :Next I
For I = 1 To Arr(0) : Sheets(Arr(I)).Select : Application.Run "personal.xls!MarkCells" :Next I
End Sub
Подскажите пожалуйста, как можно вставить формулу макросом на группу выделенных листов без перебора (возможно ли это)
Вряд ли смогу дать ответ, но хотя бы укажу направление - функции куба работают с MS OLAP кубами, тут справка по кубмнож. Альтернатива кубмнож - сводная таблица с фильтрами или макросом из сводной тянуть нужные данные. Судя по вашей строке подключения - у вас подключение к таблице SQL Server, которая, возможно, используется каким-нибудь кубом. имхо с sql server лучше работать из access без excel заморочек. Можете копать в сторону использования ADO (на этом и на других форумах можете найти класс от Nerv для работы excel с базами данных).
Добрый день. Решение Вашего вопроса потребует использование макросов. Т.к. excel не поддерживает использование переменных в строке подключения к базе (версии до excel2010 точно, остальные не видел). Предполагаемый вариант решения: 1) Создать дополнительный лист с таблицей подключенной к базе (назовем его temp) 2) на отдельном листе получать необходимые данные для формирования нового среза (" с помощью КУБМНОЖ, КУБЭЛЕМЕНТ...") 3) запускать макрос, который формирует для временной таблицы новую строку подключения с новым запросом.
Скрытый текст
Код
Public Sub exec(mdx As String)
' Application.ScreenUpdating = False
' Application.Calculation = xlCalculationManual
On Error GoTo StandardErrorHandler
Const intFirstReportRow As Integer = 6 'NOTE: there must be at least 2 rows between the filter area and the table
Const strTemplateConnectionName As String = "AdventureWork" 'имя подключения на листе temp
Const blnShowDebugMessage As Boolean = False
' Clear existing report if necessary 'предполагается что лист temp уже активен
If ActiveSheet.ListObjects.Count > 0 Then
ActiveSheet.ListObjects(1).Delete
End If
Cells(intFirstReportRow, 1).Select
' Set up connection, filters, mdx query
Dim strCon As String, strMdx As String, strAllFilters As String, vntVisibleItems() As Variant, pf As PivotField
Dim blnIteratingSet As Boolean, strHierarchyNameForSetBeingIterated As String
strCon = ActiveWorkbook.Connections(1).OLEDBConnection.Connection 'номер подключения можно посмотреть по его имени в Immediate window
blnIteratingSet = False
strMdx = mdx 'ТУТ пример с mdx запросом, но может быть и любой ваш запрос
' Run the query
Dim rngStart As Range
Dim qtTable As QueryTable
Set rngStart = ActiveSheet.Cells(intFirstReportRow, 1)
Set qtTable = ActiveSheet.ListObjects.Add(SourceType:=xlSrcQuery, Source:=strCon, Destination:=rngStart).QueryTable 'тут таблице запросу присваивается новая строка подключения
' On Error GoTo BindingErrorHandler
With qtTable
.ListObject.ShowHeaders = True
.CommandText = strMdx 'новый запрос к базе
.CommandType = xlCmdDefault
.Refresh
End With
On Error GoTo StandardErrorHandler
StandardErrorHandler:
'Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAutomatic
'MsgBox "Error: " & err.Description, vbOKOnly, Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
Exit Sub
' Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAutomatic
End Sub
сохранение *xls в *csv =строка на выходе в кавычках, можно ли обойти?, если в исходной строке есть запятая, то вся строка заключается в двойные кавычки. Как-то это можно обойти?
Добрый день. Наверное многое зависит от машины и ПО, у меня Ваш вариант и код Johny выдают результаты без кавычек. попробуйте следующий вариант, может поможет:
Код
Dim r
Dim record As String
Open "C:\Users\SP\Desktop\1.csv" For Output As #1
For r = 13 To 23
record = "" & Range("B" & r & "") & ";" & Range("C" & r & "") & ""
Print #1, record
Next
Close #1
Из собственного опыта - наличие доступа к надстройке не дает большого преимущества. Нужно еще знать как к нему подключиться и как выполнить запрос (эту информацию наудивление трудно найти). Создав соединение с базой нужно знать 1с язык запросов, чтоб вытянуть запрос + сделать обработку вывода в таблицу. стандартные отчеты и так хорошо выгружаются, для создания специфических запросов лучше использовать конструктор запросов 1с. во вложении модуль для базы access для подключения к 1с через коннектор (долго искал по сети как это сделать) (переименуйте расширение из xls в bas). Успехов.