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

Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 14 След.
Надстройки для построения красивых диаграмм
 
попробуйте charticulator - можно загрузить свои данные, накидать график, выгрузить картинкой, или html для вставки в email рассылку, или экспортировать как кастомный визуал power BI.
А какие есть альтернативы Excel'ю в плане политики импортозамещения? И что из них поддерживает VBA&
 
Малую автоматизацию можно вести в 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.

Иногда это велосипед, но иногда очень может выручить.
Изменено: egonomist - 30.04.2022 09:08:22
Удобное внедрение языка программирования "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.

Подробнее по стеку и используемым библиотекам в моем докладе
Data Engineering «на коленке» - YouTube

p.s: свежие ссылки на mito:
How to Analyze Data Using Mito in Python | Towards Data Science
3 Python Tools Every Data Scientist Should Use | by Jake from Mito | trymito | Sep, 2021 | Medium
Изменено: egonomist - 15.11.2021 08:42:19
Удобное внедрение языка программирования "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 можно использовать для хранения настроечных таблиц.
Изменено: egonomist - 09.11.2021 19:32:45
С чего начать изучение макросов (VBA)
 
@exmacros - очень хороший канал в телеgе , для начинающих самое то.  
Как вы монетизируете свои знания excel + vba?
 
Расскажу о еще одном способе монетизации.
когда - то занимался машинным обучением. Отправили меня в Москву знакомиться с главным специалистом аналитического отдела. Он очень ценился благодаря своей супер формуле расчета кой-какого значения. Мне повезло - мне дали код этой формулы. Оказывается там куча фиктивных переменных, извлечение корня и возведение в степень выражений=1. В итоге рассчииывалась обычное скользящее среднее. Его зп в три раза превышала мою. Вот так монетизация.  
Как вы монетизируете свои знания excel + vba?
 
Удаленка невозможна, большая и очень инертная кампания. Куча источников и совсем отсутствует слой данных. Приходится руками собирать. О всех трудностях глубокой аналитики без прав локального админа и с ограниченными интернетом я уже писал когда-то. Конечно помимо макросов требуется экономическое образование и аналитический склад ума, но не в этом суть. Люди идут из других контор, бьют пяткой в грудь что могут все. А по факту цикл по ячейкам написать не могут.  Зп реально выше рынка, но в отсутствии предложения будем искать студента на грейд ниже.  
из excel пока можно работать со Скриптами  R/python/c++/ fortran.  остается лучшим пользовательских интерфейсом для ввода хотелось. Дальше вешать кнопку и запускать все что угодно.  
Как вы монетизируете свои знания excel + vba?
 
День добрый, смотрел в сторону freelance на международном рынке - везде перебивают индусы. Они готовы писать макросы за копейки. Причем у многих есть хорошие отзывы. Мне на работе за час больше платят.
на работе монетизировать-у нас в Самаре кадровый голод знающих vba. Два месяца ищу себе помощника 50-60к на руки - никого нет, кто бы знал как for... To.. Next  написать.  
Изменено: egonomist - 10.02.2020 10:20:58
Функция ближайший рабочий день на VBA
 
День добрый.
Для раскрытия темы выходных дней - во вложении макрос собирающий все выходные дни из Консультанского производственного календаря.
Каждый год скачиваю календарь в doc формате, дергаю даты, потом уже использую всевозможные формулы.

Сейчас появился еще один вариант, используя веб-запрос,  дажеR пакет сделали с его использованием.
Но пока непонятно как быстро и как долго будут обновлять данные о выходных днях.
Нечёткий поиск / Fuzzy Lookup: как повторить или подключиться через VBA
 
День добрый.

Всегда пользуюсь fuzzyvlookup функцией от Уважаемого Pharmaprofi.
В надстройке все модули открыты. Просто переношу модуль в свою книгу и использую как udf или в vba.

Еще раз огромное спасибо Pharmaprofi за труд.
Возможности разработки взаимодействия с БД (выбор инструментов), Javascript ИЛИ VBScript
 
День добрый.
Возможно это уже оффтоп, кратко по вопросу:
Не разрешено политикой безопасности.
Аналитика - это бизнесовая часть, не входит в иерархию ИТ-трудно лоббировать свои хотелки.
До вируса Петя можно было все,после ничего.
Служебками можно выбить установку по на свой комп,
Но результат твоей работы должен быть воспроизводим на пк других пользователей.

Маленькое дополнение к предыдущему посту:
Для каких задач используется:
1) Excel + R + sqlite - любые задачи обработки, хранения, визуализации данных. Пакеты data.table, dbplyr, tidyverse для обработки, ggplot2, shiny - графики, веб отчёты. Выполняемых код, находится на листе, его можно менять формулами, результаты работы сразу в Excel.
2) knime - парсинг json от рест запросов к веб сервиса,
ETL для бд, прогнозирование нагрузки контакт центра.
3) talend - обработка больших данных (более 10 млн. Строк), качественные тесты данных, join big data, Etl.

Для прочего BI ещё есть metabase.  
Изменено: egonomist - 15.11.2019 06:08:38
Возможности разработки взаимодействия с БД (выбор инструментов), Javascript ИЛИ VBScript
 
Добрый день. Давно просматриваю данную ветку - очень знакомая боль. Хочу поделиться своим опытом.

На всех предыдущих местах работы - ритейл, банковский сектор, нефтянка - обычному пользователю и главному специалисту не дают прав локального администратора. Нет возможности зарегистрировать 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 серверу.  
Мощность Power Query
 
день добрый,
обработку большого xml можно вести в Talend Data Integration Studio (бесплатное open source ПО) - легко можно создать любой ETL workflow.
job можно скомпилировать в jar и запускать по расписанию. Результат работы можно писать в любую БД.
Для себя сейчас открыл мощную связку Excel/Access (ввод данных) -> VBA->sqlite  (data storage)- не требует развертки сервера и прав администратора.
Перевод Dictionary в таблицу, Как преобразовать словарь в таблицу на листе
 
Уважаемые коллеги, вечер добрый.
Помогите пожалуйста с преобразованием словаря в обычную таблицу на листе.
Источник данных:
Веб сервис - 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 не подступал - забыл уж многое.
Есть ли будущее у 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.
Неактивны вычисляеме меры OLAP-куба
 
эту надстройку пробовали?
позволяет самому задавать вычисляемое поле, но только на уровне выводимых в сводную таблицу данных.
Если нужно создать новую вычисляемую меру, которая будет агрегироваться на всех узлах куба - тогда нужно запускать выполнение 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"
Задание выбор периода дисконтирования в модели DCF, Оптимизация модели DCF
 
удалось выложить файл с телефона
Задание выбор периода дисконтирования в модели DCF, Оптимизация модели DCF
 
вашу задачу можно решить используя таблицы подстановки.
но реализовать функцию на vba не получится (в пользовательских функциях нельзя задавать значение ячейке ((()
в который раз не могу вставить файл в сообщение (chrom). киньте в личку почту - вышлю файл.
вместо этого лист с расчетом можно скрыть, на пользовательском листе сделать выпадающий список с периодами, по которым будет подтягиваться расчитанное значение.
Изменено: egonomist - 24.05.2016 11:30:18
Трехмерный график
 
можно использовать другие программы для построения 3d графиков (не могу прикрепить файл, могу кинуть на мейл пример)
Изменено: egonomist - 06.05.2016 14:39:49
Дизассемблирование макроса, узнать логику кода
 
первый гуглинг по этому вопросу дал эту ссыль - может подойдет.
Получить адрес на файл убрав ThisWorkbook.Path
 
Код
x=Application.GetOpenFilename
y=ThisWorkbook.Path
ссылка=right(x, len(x)-len(y))
VBA изменение группы выделенных листов
 
Wow! То что нужно, Пытливый,огромное спасибо!
Код
Range("AH4").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(R[-1]C19:R[-1]C30,R2C[-15]:R2C[-4],"">=""&DATE(YEAR(NOW()),1,1)&"""",R2C[-15]:R2C[-4],""<""&NOW()&"""")"
заработало, видимо надо было сначала макрорекодер запустить )
Всем спасибо. вопрос закрыт.
VBA изменение группы выделенных листов
 
Игорь, спасибо за ответ.
Перебором не хотелось - из-за потери времени (очень долгая выгрузка исходных данных из источников).
Кроме формул присутствует еще и форматирование. но видимо нет другого пути.
Вопрос возник так как вручную формула проставляется на группе листов, макросом нет - чую делаю что-то не так.
VBA изменение группы выделенных листов
 
Уважаемые коллеги, добрый день.
Не могу найти решение следующего вопроса - нужны знания гуру теории 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) запускать макрос, который формирует для временной таблицы новую строку подключения с новым запросом.
Скрытый текст
сохранение *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
Библиотека 1CV82 COMConnector Tipe Library
 
Из собственного опыта - наличие доступа к надстройке не дает большого преимущества. Нужно еще знать как к нему подключиться и как выполнить запрос (эту информацию наудивление трудно найти). Создав соединение с базой нужно знать 1с язык запросов, чтоб вытянуть запрос + сделать обработку вывода в таблицу.
стандартные отчеты и так хорошо выгружаются, для создания специфических запросов лучше использовать конструктор запросов 1с.
во вложении модуль для базы access для подключения к 1с через коннектор (долго искал по сети как это сделать) (переименуйте расширение из xls в bas). Успехов.
Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 14 След.
Наверх