63

Пишем автоматически обновляемую смету в Excel VBA

Задача: сделать смету на ремонтно-строительные работы с автоматическим обновлением цен на материалы

Решение: пишем макрос в Excel

Sub getprice()

Название макроса

Set ws = ThisWorkbook.Worksheets("список")

Присваиваем переменной ws значение листа "список" текущей рабочей книги (текущая - к которой прикреплён модуль VBA)

TotalRow = ws.UsedRange.Rows.Count

Присваиваем переменной TotalRow значение количества использованных строк листа ws

For i = 1 To TotalRow - 1

Начало цикла, начиная с 1 до количества использованных строк - 1 (-1 так как дальше мы адресуем ячейки с переменной i + 1)

TempString = "=VLOOKUP(A" & i + 1 & ",каталог!$H$1:$I$24605,2,0)"

Присваиваем переменной TempString значение формулы ВПР из листа каталог. В макросах используются только английские формулы. В формуле используется переменная, обозначающая номер строки (А1, А2 и т.д.)

ws.Cells(i + 1, 2).Formula = TempString

Заполняем ячейки, начиная с B2 листа ws значением переменной TempString

Next i

Конец цикла

Set IE = CreateObject("InternetExplorer.Application")

Присваиваем переменной IE объект InternetExplorer

For i = 1 To TotalRow - 1

Начало цикла

URL = ws.Cells(i + 1, 2).Value

Присваиваем переменной URL значение ячейки, начиная с ячейки B2 (ВПР ссылки из каталога)

IE.navigate URL

Переходим браузером по URL

Do Until (IE.readyState = 4 And Not IE.Busy)
DoEvents
Loop

Ожидаем загрузку страницы

Set ieDoc = IE.Document

Присваиваем переменной ieDoc значение HTML-кода загруженной страницы

Set detail_elements = IE.Document.getElementsByTagName("span")

Присваиваем массиву detail_elements значение массива всех элементов с тегом "span"

For Each detail_element In detail_elements

Начало цикла

If detail_element.getAttribute("class") = "retailPrice" Then

Проверка условия - равен ли атрибут элемента "class" значению "retailPrice"

ws.Cells(i + 1, 3) = detail_element.innerText

Если условие истинно, записываем внутренний текст элемента в ячейку, начиная с ячейки C2. Этот код считывает цену из интернет-каталога.

End If

Конец условия

Next detail_element

Конец цикла

Next i

Конец цикла

IE.Quit

Закрываем браузер

For i = 1 To TotalRow - 1

Начало цикла

TempString = "=VALUE(C" & i + 1 & ")"

Присваиваем переменной TempString значение формулы ЗНАЧЕН

ws.Cells(i + 1, 4).Formula = TempString

Записываем в ячейки, начиная с D2, формулу из переменной TempString

Next i

Конец цикла

MsgBox "Обновление данных завершено"

Сообщение для удобства

End Sub

Конец макроса

Источник

Дубликаты не найдены

+2

@1Irina1985, у Вас очень мужской голос на видео, но это фиг с ним - мы в свободном мире живем)
Фиг с ней с картавостью (или это глюки видео? О.о), но прошу Вас, не умирайте на видео. Приходится вслушиваться изо всех сил, чтобы услышать, что Вы говорите.
Если  хотите снимать видео по своим постам, обязательно поработайте над голосом и подачей информации.
И спасибо за новую информацию, я много нового узнал)

раскрыть ветку 1
0

Видео записывала не я. По поводу звука хз, может с программой что то у человека.

+1

Хммм, это что ж, можно парсить страницы сайтов и по-условиям вставлять какие-то данные в эксель? Это же охуенно! А если там логиниться нужно? А если там javascript? А ие в фоне открывается или как полноценное окно?

раскрыть ветку 1
+1

Авторизацию думаю можно сделать, VBA+IE вроде как поддерживают заполнение форм, клики. Насчёт JS не знаю, надо пробовать. IE полноценный, просто скрыт, можно включить отображение.

+1
На vba же вроде тоже есть библиотека с запросами по http, вместо физического парсинга браузером можно получать html код страницы по запросу url и уже его парсить
раскрыть ветку 3
0

А в чем принципиальная разница?

И вы уверены, что у вас без эмуляции браузера сайт нормально прогрузится?))

раскрыть ветку 2
+1
Любой вэб сервер по запросу статичного адреса отдает одинаковый шаблон, разбавленный разве что данными из кук, поэтому, что вы грузите сайт в браузере, что делаете к нему http запрос - html страницы/ответа будет один
раскрыть ветку 1
+1

Можно использовать и русские формулы. Нужно использовать свойство ячейки formulalocal

раскрыть ветку 1
0

Не знала, спасибо.

+1
Ловите бухгалтера!
раскрыть ветку 5
+2

Я аналитик))

раскрыть ветку 4
+2

Ловите аналитика!

+2
Это ваш эксель, если знать все его возможности, прям магия вне хогвартса какая-то))
раскрыть ветку 2
0
@editors, срочно нужен тег Яндекс.Дзен 
раскрыть ветку 1
+1

Куда торопимся?

Похожие посты
268

Редизайнер таблиц в Excel

Не секрет, что большинство пользователей Excel, создавая таблицы на листах, думают в первую очередь о собственном комфорте и удобстве. Так рождаются на свет красивые, со сложными "шапками", пестрые и громоздкие таблицы, которые при этом совершенно нельзя ни отфильтровать, ни отсортировать, а про автоматический отчет сводной таблицей лучше и не думать вообще.


Рано или поздно пользователь такой таблицы приходит к мысли, что "пусть будет не так красиво, зато можно работать" и начинает упрощать дизайн своей таблицы, приводя его в соответствие с классическими рекомендациями:

- простая однострочная шапка, где у каждого столбца будет свое уникальное название (имя поля)

- одна строка - одна законченная операция (сделка, продажа, проводка, проект и т.д.)

- без объединенных ячеек

- без разрывов в виде пустых строк и столбцов


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

из такой таблицы

Редизайнер таблиц в Excel Excel, Макрос, Vba, Таблица, Полезное, На заметку, Длиннопост

сделать

Редизайнер таблиц в Excel Excel, Макрос, Vba, Таблица, Полезное, На заметку, Длиннопост

В терминах баз данных нижнюю таблицу обычно называют плоской (flat) - именно по таким таблицам лучше всего строить отчеты сводных таблиц (pivot tables) и проводить аналитику.


Преобразовать двумерную таблицу в плоскую можно при помощи простого макроса. Откройте редактор Visual Basic через вкладку Разработчик - Visual Basic (Developer - Visual Basic Editor) или сочетанием клавиш Alt+F11. Вставьте новый модуль (Insert - Module) и скопируйте туда текст этого макроса:

Sub Redesigner()

Dim i As Long

Dim hc As Integer, hr As Integer

Dim ns As Worksheet

hr = InputBox("Сколько строк с подписями сверху?")

hc = InputBox("Сколько столбцов с подписями слева?")

Application.ScreenUpdating = False

i = 1

Set inpdata = Selection

Set ns = Worksheets.Add

For r = (hr + 1) To inpdata.Rows.Count

For c = (hc + 1) To inpdata.Columns.Count

For j = 1 To hc

ns.Cells(i, j) = inpdata.Cells(r, j)

Next j

For k = 1 To hr

ns.Cells(i, j + k - 1) = inpdata.Cells(k, c)

Next k

ns.Cells(i, j + k - 1) = inpdata.Cells(r, c)

i = i + 1

Next c

Next r

End Sub

После этого можно закрыть редактор VBA и вернуться в Excel. Теперь можно выделить исходную таблицу (полностью, с шапкой и первым столбцом с месяцами) и запустить наш макрос через Разработчик - Макросы (Developer - Macros) или нажав сочетание Alt+F8.


Макрос вставит в книгу новый лист и создаст на нем новый, реконструированный вариант выделенной таблицы. С такой таблицей можно работать "по полной программе", применяя весь арсенал средств Excel для обработки и анализа больших списков.


Также есть второй вариант для работы с большими таблицами

Sub Redesigner()

Dim inpdata As Range, realdata As Range, ns As Worksheet

Dim i&, j&, k&, c&, r&, hc&, hr&

Dim out(), dataArr, hcArr, hrArr

hr = Val(InputBox("Сколько строк с подписями данных сверху?"))

hc = Val(InputBox("Сколько столбцов с подписями данных слева?"))

Set inpdata = Selection

If inpdata.Rows.Count <= hr Or inpdata.Columns.Count <= hc Then Exit Sub

Set realdata = inpdata.Offset(hr, hc).Resize(inpdata.Rows.Count - hr, inpdata.Columns.Count - hc)

dataArr = realdata.Value

If hr Then hrArr = inpdata.Offset(0, hc).Resize(hr, inpdata.Columns.Count - hc).Value

If hc Then hcArr = inpdata.Offset(hr, 0).Resize(inpdata.Rows.Count - hr, hc).Value

ReDim out(1 To Application.CountA(realdata), 1 To hr + hc + 1)

Set ns = Worksheets.Add

For i = 1 To UBound(dataArr, 1)

For j = 1 To UBound(dataArr, 2)

If Not IsEmpty(dataArr(i, j)) Then

k = k + 1

For c = 1 To hc: out(k, c) = hcArr(i, c): Next c

For r = 1 To hr: out(k, c + r - 1) = hrArr(r, j): Next r

out(k, c + r - 1) = dataArr(i, j)

End If

Next j, i

ns.Cells(2, 1).Resize(UBound(out, 1), UBound(out, 2)) = out

End Sub

Редизайн таблиц сэкономит кучу времени, нервов и сил, чтобы в оставшееся время почитать пикабу))

Взято отсюда

Показать полностью
88

Курсы валют с сайта ЦБ на VBA (с автообновлением)

В комментариях к прошлому посту возникла жалоба одного пользователя Excel на отсутствие рабочих примеров макросов, которые загружают курсы валют с сайта ЦБ, и при этом формулы обновлялись бы автоматически, подтягивая актуальный курс на текущую дату. Там же в комментариях набросал вариант решения (вот готовый пример), но подумал, что отдельный пост на эту тему поможет и другим пользователям, столкнувшимся с подобной задачей.

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Итак, на сайте ЦБ РФ находим официальные API для загрузки динамики курсов (на случай, если нам потребуется загружать курс на определенную дату в прошлом):

HTTP GET-запрос вида

http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=02/03/20...

где R01235 - это код валюты (доллар США). Список кодов валют для указания в запросе также доступен.

API возвращает данные в XML-формате, и для нас это даже удобнее, чем JSON - так как в VBA есть поддержка XML, а для парсинга JSON нужны сторонние библиотеки. Вот пример ответа:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Как видим, мы получили список курсов на каждую дату в интервале, с указанием кода валюты - именно то, что нам нужно.


Теперь попробуем сделать такой запрос из VBA. Для начала подключим в нашей книге ссылку на зависимость MSXML 6.0 (если у вас доступна другая версия, ничего страшного - могут немного поменяться имена типов, в целом логика такая же)

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Заодно добавим ссылку на Microsoft Scripting Runtime - оттуда мы сможем использовать тип Dictionary для хранения соответствий кодов валют (USD -> R01235).

Итак, создадим простую функцию, которая отправит запрос и загрузит ответ в XML-документ (код примера я выложил на гитхабе: https://github.com/navferty/CBR-VBA-Currencies ):

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Кстати, неплохо расписан механизм XmlHttpRequest с примерами авторизации тут: https://codingislove.com/http-requests-excel-vba/


Как видим, у нас есть XML-документ, по которому мы можем пройти в цикле, извлекая значения курса валюты на каждую дату. Давайте попробуем это сделать! Но для начала, добавим класс для того, чтобы хранить эти значения. Нам нужны три свойства (property) - код валюты (строка), дата и собственно значение (для сумм желательно использовать тип decimal). Добавим class module:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Свойства можно добавлять командой Insert -> Procedure... , это автоматически сгенерирует геттеры и сеттеры. Можно обойтись и просто публичными полями - но публичный доступ через свойства позволяет управлять доступом к данным, и вообще считается хорошим тоном =)


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

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Но вернёмся к нашей задаче. Итак, создадим коллекцию, которую будем наполнять записями типа CurrencyRecord:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Как видно на скриншоте, мы обходим элементы, которые содержат курсы на каждую дату (можно немного переделать обращение к resultXmlDocument.LastChild.ChildNodes с использованием XPath - для более наглядной навигации по XML документу).


Убедившись, что запрос и обработка ответа работают, перепишем нашу функцию для запроса валюты по ее коду, добавив словарь с соответствием кодов валют, а также конструирование пути с запросом с учётом даты начала/окончания и кода валюты:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Также я поменял CurrencyCode при создании записи в цикле - вместо "R01235" запись будет содержать общеизвестный код валюты "USD").


RequestGetXml - вспомогательная функция, которая загружает XML-документ с помощью HTTP-запроса:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Проверим, что вся функция работает как ожидается, добавив временную процедуру для теста:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Теперь добавим публичную функцию, которую можно будет использовать в формулах на листе (так называемую UDF - user-defined function):

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

С помощью функции DateAdd определяем интервал дат для загрузки - от вчера до сегодня. Получив две записи, возьмём значение из последней:

Set currItem = col.Item(col.Count) ' не забываем, что в VBA нумерация с 1


Обратите внимание на второй аргумент - volatileArg. Его значение не используется, но он понадобится для того, чтобы заставить Excel пересчитывать значение при изменении значения этого аргумента в формуле ячейки:

=GetTodayCurrency("USD"; NOW())

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


Теперь проверим, что при открытии книги будет выполнена загрузка курса:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Как мы видим, в окне Immediate есть сообщение об успешной загрузке курса - сразу после открытия книги. Тем не менее, такое же сообщение будет появляться и при любом изменении на листе - Excel будет пересчитывать значение формулы. Поэтому если у Вас в книге будет много ячеек с вызовом этой функции, лучше добавить кэш - например, статическую переменную, в которой будет словарь (Dictionary). Ключом может выступать сочетание кода валюты и даты, значение - собственно CurrencyRecord. Это поможет избежать множества одинаковых запросов на сайт ЦБ. Если кому-то будет интересно - напишите, добавлю его в комментарии или отдельным постом =)


Спасибо всем кто дочитал, предлагайте в комментариях варианты доработки и улучшения предложенного решения! За идею поста спасибо @AlexeyE30 !

Напоследок упомяну о надстройке для MS Excel - я недавно писал о ней. Проект с открытым исходным кодом, опубликован под свободной лицензией MIT (неограниченное право на использование, копирование, изменение).

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Инструкции по установке и описание функций можно найти тут: Надстройка для MS Excel


Страница проекта на гитхабе:

https://github.com/navferty/NavfertyExcelAddIn

Показать полностью 11
87

VBA Excel - вывести формулы в ячейки

Была задача по переносу вычислений из экселя. Чтобы не лезть и не смотреть формулу в каждой ячейке я написал небольшой макрос, который их выводит.

Ниже выделенного диапазона на 10 строк выводятся все формулы и значения из заполненных ячеек.

Получается вот такая штука, которую гораздо проще разобрать и перенести

VBA Excel - вывести формулы в ячейки Excel, Vba, Макрос

Сам макрос:

Sub DrawFormulas()
For Each Cell In Selection
CellFormula = Cell.Formula
If Left(CellFormula, 1) <> "=" Then CellFormula = "=" + CellFormula
If Trim(CellFormula) <> "=" Then Cell.Offset(Selection.Rows.Count + 10).Value = Cell.Address + CellFormula
Next
End Sub

592

Отслеживание входа пользователей в книгу Excel

Как понятно из заголовка, мы сделаем так, чтобы открытие рабочего файла на общем сетевом диске не осталось бесследным. Макрос будет фиксировать на отдельном (скрытом) листе имя пользователя, открывшего файл, а также дату-время открытия и закрытия файла.


Этап 1. Создаем "Лог"

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

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Этап 2. Макросы фиксации входа-выхода

Теперь добавим макросы для записи на лист Лог даты-времени и имен пользователей при открытии и закрытии книги. Для этого нужно открыть редактор Visual Basic с помощью сочетания Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) и найти в левом верхнем углу панель Project (если она не отображается, то включить ее можно сочетанием клавиш Ctrl+R):

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Двойным щелчком откройте модуль ЭтаКнига (ThisWorkbook) и вставьте туда пару наших макросов для обработки событий открытия и закрытия книги:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим дату-время выхода из файла

If lastrow>1 Then Worksheets("Лог").Cells(lastrow, 3) = Now

'сохраняемся перед выходом

ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим имя пользователя и дату-время входа в файл

Worksheets("Лог").Cells(lastrow + 1, 1) = Environ("USERNAME")

Worksheets("Лог").Cells(lastrow + 1, 2) = Now

End Sub

Попробуйте открыть-закрыть этот файл пару раз и убедитесь, что на лист Лог попадает ваше имя пользователя (логин входа в Windows) и дата-время:

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Этап 3. Улучшаем надежность

Можно было бы скрыть лист Лог и на этом остановиться, но есть одно "но": если у пользователя, который открывает нашу книгу, макросы разрешены по умолчанию либо он сам их разрешает, нажав в окне предупреждения на кнопку Включить содержимое, то все в порядке:

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Но что если пользователь не разрешит выполнение макросов или они отключены у него по умолчанию? Тогда наши макросы отслеживания выполняться не будут и фиксации имени и даты не произойдет :( Как же заставить пользователя разрешить использование макросов?

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

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Суть в том, чтобы по умолчанию скрыть в книге все листы кроме этого, а рабочие листы с данными отображать с помощью специального макроса. Если пользователь не разрешил выполнение макросов, то он увидит в книге только один лист с предупреждением. Если же макросы разрешены, то наш макрос обработки события открытия книги скроет лист с предупреждением и отобразит листы с данными. Чтобы пользователь сам не отобразил их - используем суперскрытие вместо обычного скрытия листов (параметр xlSheetVeryHidden вместо обычного False).

Чтобы реализовать все описанное, слегка изменим наши процедуры в модуле ЭтаКнига (ThisWorkbook)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим дату-время выхода из файла

If lastrow > 1 Then Worksheets("Лог").Cells(lastrow, 3) = Now

'скрываем все листы, кроме листа ПРЕДУПРЕЖДЕНИЕ

Worksheets("Предупреждение").Visible = True

For Each sh In ActiveWorkbook.Worksheets

If sh.Name = "Предупреждение" Then

sh.Visible = True

Else

sh.Visible = xlSheetVeryHidden

End If

Next sh

'сохраняемся перед выходом

ActiveWorkbook.Save

End Sub


Private Sub Workbook_Open()

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим имя пользователя и дату-время входа в файл

Worksheets("Лог").Cells(lastrow + 1, 1) = Environ("USERNAME")

Worksheets("Лог").Cells(lastrow + 1, 2) = Now

'отображаем все листы

For Each sh In ActiveWorkbook.Worksheets

sh.Visible = True

Next sh

'скрываем листы ПРЕДУПРЕЖДЕНИЕ и ЛОГ

Worksheets("Предупреждение").Visible = xlSheetVeryHidden

Worksheets("Лог").Visible = xlSheetVeryHidden

End Sub

Чтобы просмотреть скрытый Лог откройте редактор VisualBasic (Alt+F11), выделите лист на панели Project и измените его видимость на панели Properties, используя свойство Visible:

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Если пользователи настолько продвинутые, что знают про суперскрытые листы и могут их отобразить через редактор Visual Basic или нарушить работу наших макросов, то можно дополнительно поставить пароль на просмотр и изменение макросов. Для этого щелкните правой кнопкой мыши по имени файла в панели Project (строка VBAProject (blackbox.xls)), выберите команду VBA Project Properties и включите флажок Lock project for viewing и задайте пароль на вкладке Protection:

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Теперь точно никто не уйдет безнаказанным.


Интересные поправки в макрос из комментария источника:

Вместо поиска последней занятой строки в таких случаях я обычно вставляю новую строку сразу после "шапки". Все предыдущие строки сдвигаются вниз, конечно. В результате немного упрощается код, сортировка получается обратная, "новые сверху", что позволяет быстрее просматривать последние записи, не прокручивая лист. При открытии - вставляется строка, её номер всегда будет 2. При закрытии файла в строку 2 добавляется время. Переполнение этого стека маловероятно, но и проверку такого переполнения проводить не нужно, просто отрезать строки ниже запланированного количества записей, без проверки.

Private Sub Workbook_Open()

Worksheets("Реестр изменений").Rows("2:2").Insert Shift:=xlDown 'вставляем между строками 1 и 2 новую строку

Worksheets("Реестр изменений").Rows("501:501").Delete Shift:=xlUp 'удаляем строку 501 (реестр на 500 строк)

Worksheets("Реестр изменений").Cells(2, 1) = Environ("USERNAME") 'запись в первую ячейку второй строки

Worksheets("Реестр изменений").Cells(2, 2) = Now 'запись во вторую ячейку второй строки

Показать полностью 5
158

Макрос для выделения дубликатов разными цветами

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

Достаточно выделить диапазон, задать цвет заливки, - и все повторяющиеся (или, наоборот, уникальные) значения будут выделены.

Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.

В этом случае, без макросов не обойтись. Нажимаем сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставляем новый пустой модуль через меню Insert - Module и копируем туда код этого макроса:

Sub ВыделитьДубликатыРазнымиЦветами()

On Error Resume Next

' массив цветов, используемых для заливки ячеек-дубликатов

Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _

9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213)

Dim coll As New Collection, dupes As New Collection, _

cols As New Collection, ra As Range, cell As Range, n&

Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange)

If Err Then Exit Sub

ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False

For Each cell In ra.Cells ' запонимаем значение дубликатов в коллекции dupes

Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value)

If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value)

Next cell

For i& = 1 To dupes.Count ' заполняем коллекцию cols цветами для разных дубликатов

n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1

Next

For Each cell In ra.Cells ' окрашиваем ячейки, если для её значения назначен цвет

cell.Interior.color = cols(CStr(cell.Value))

Next cell

Application.ScreenUpdating = True

End Sub

Теперь можно выделить любой диапазон с данными на листе и запустить макрос с помощью сочетания клавиш Alt+F8 или через кнопку Макросы (Macros) на вкладке Разработчик (Developer).

Макрос для выделения дубликатов разными цветами Excel, Макрос, Vba, Полезное, На заметку

Плюсом этого макроса является то, что он не виснет при выделении всего листа (или столбца) целиком и не вылетает с ошибкой, если найдено более 55 дубликатов. Макрос взят здесь

Показать полностью 1
58

VBA Excel - массовая безопасная замена

Решил поделиться ещё одним полезным отрывком кода. Была задача массовой замены в документе одних значений на другие, при этом они могли пересекаться (изменилась нумерация) и обычная последовательная замена приводила к хаосу. В результате получилась вот такая процедурка для замены в 2 прохода.

Sub SafeReplace(TargetRange As Range, ReplaceRules As Range)
' безопасный массовый поиск и замена
' TargetRange: где ищем, ReplaceRules: правила замены, первый столбец - что найти, второй - на что заменить
For i = 1 To ReplaceRules.Rows.Count ' for each
TargetRange.Replace _
What:=ReplaceRules.Cells(i, 1), Replacement:="!SafeReplace" + CStr(i) + "!", _
MatchCase:=False
Next ' замена 1 проход
For i = 1 To ReplaceRules.Rows.Count ' for each
TargetRange.Replace _
What:="!SafeReplace" + CStr(i) + "!", Replacement:=ReplaceRules.Cells(i, 2), _
MatchCase:=True
Next ' замена 2 проход
End Sub
с форматированием - на pastebin.com

Пример использования: на вкладке ReplaceRules есть таблица замены

VBA Excel - массовая безопасная замена Excel, Vba, Замена

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

Sub ReplaceSelected()
Application.ScreenUpdating = False
Dim ReplaceRulePos As Range
Set ReplaceRulePos = Worksheets("ReplaceRules").Range("A1").CurrentRegion.Offset(1, 0) ' смещение на 1 строку, без заголовка
Call SafeReplace(Selection, ReplaceRulePos.Resize(ReplaceRulePos.Rows.Count - 1)) ' изменить размер области, чтобы последний пустой ряд не обрабатывался и вызвать автозамену
Application.ScreenUpdating = True
End Sub
45

VBA Excel - выбор документа для обработки

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

VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост

В listbox выводится список открытых документов или можно открыть файл через стандартный диалог открытия.

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

VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост
VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост
VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост
VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост
VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост

Как вы видите, я не заморачивался с названиями.

Код:

Public SrcName

Private Sub CommandButton1_Click()

SrcName= ""

If ListBox1.ListIndex >= 0 Then

SrcName= ListBox1.List(ListBox1.ListIndex)

UserForm1.Hide

End If

End Sub

Private Sub CommandButton2_Click()

SrcName= ""

UserForm1.Hide

End Sub

Private Sub OpnButton_Click()

iOpen = Application.Dialogs(xlDialogOpen).Show

If iOpen = True Then

SrcName= ActiveWorkbook.Name

UserForm1.Hide

Else

MsgBox "отмена", vbCritical, ""

Exit Sub

End If

End Sub

Private Sub UserForm_Activate()

SrcName= ""

ListBox1.Clear

NoShow = ThisWorkbook.Windows(1).Caption

For i = 1 To Application.Windows.Count

If Application.Windows(i).Caption <> NoShow Then ListBox1.AddItem (Application.Windows(i).Caption)

Next ' enum windows

End Sub

Пикабу сожрал все отступы, это не я!

Пример использования:

Dim SrcWB As Worksheet
UserForm1.Show
If UserForm1.SrcName= "" Then Exit Sub
Windows(UserForm1.SrcName).Activate
Set SrcWB = ActiveWorkbook

P.S. Баянометр считает, что эксель на 41% похож на клубничку. Мне кажется, что он недалёк от истины.

Показать полностью 5
148

О пользе продвинутого знания Ёкселя и о программировании

Надо внести в онлайновую базу, по работе, 180 записей. Каждая из 15 полей. Обычно мы это делали через CTRL+C/CTRL+V из Ёкселя, куда данные вбивались заранее. Т.к. обычно вносилась одна запись в неделю-две, редко больше.- А тут целых 180, причём надо, как обычно, "вчера".

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

===

Где-то минут за 40 неспешной и творческой работы наваял простенький конвертер, не выходя из Ёкселя:

- страница с настройками и константами

- страница, куда копипастится блок из рабочего файла Ёкселя, который надо экспортнуть

- страница с результатами, которая тупо сохраняется в CSV.

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

===

Затем за пару прогонов через утилиту перегнал в XML, подписал ЭЦП и загнал на сайт. Ошибок: 0.

- Это заняло у меня ещё минут 15, вместе с проверкой исходных данных и результата, подписью ЭЦП и проверки, что всё успешно залилось и открывается онлайн.

===

Стал считать, сколько набивал бы вручную. Я работаю практически только с клавы, поэтому на копипаст 1 поля уходит, пускай, 1 секунда.

- Т.е. CTRL+C ALT+TAB CTRL-V TAB ALT-TAB Right... и так много раз подряд.

- Это 15*180=2700 секунд или 45 минут непрерывной, неотрывной работы.

- Это без учёта необходимости кликнуть в браузере "Добавить запись" и в конце "Сохранить" и промотать колонки в Ёкселе, т.к. их порядок не совпадает с порядком полей на сайте.

- И без учёта возможных косяков при копипащении.

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

===

Ну и да, я НЕНАВИЖУ монотонную работу. Для меня и 10 записей подряд внести напряжно. Так что сидел бы я пару рабочих дней точно...

===

Потом прикинул, сколько времени это заняло бы у секретаря, которая вобще-то и должна набивать эту базу.

- И которая из клавиатурных комбинаций знает, видимо, только ESC, да и то не уверен. И копировать/вставлять/переключать окна умеет только мышкой.

- Попробовал смоделировать её скорость работы (мышкой) - получилось секунд 10 на одно поле. Т.е. 7.5 часов, опять же - непрерывной, - работы.

- Сколько в процессе будет косяков в базе и сколько данных из других ячеек будет "случайно" вставлено в Ёксель - тут я просто до судорог боюсь представить, со своей педантичностью.

===

Мораль простая. По возможности, изучайте программирование в целом и формулы MS Excel в частности.

- Это реально ОЧЕНЬ помогает в работе.

- И ОЧЕНЬ экономит нервы.

Показать полностью
75

Разработка IoT-шлюза на базе Raspberry CM3+

Разработка IoT-шлюза на базе Raspberry CM3+ Raspberry pi, Интернет вещей, Промышленность, Программирование, Автоматизация, Вагинальный контроллер, Длиннопост

Рисунок 1 — Плата устройства

Большой опыт работы в сфере промышленной автоматизации и АСУТП, казалось бы, должен способствовать тому, что со временем уже много всего видел и много всего знаешь. Но не тут-то было. Оказывается, иногда могут возникать задачи и проекты, которые трудно реализовать стандартными средствами. Так под один крупный проект по мониторингу и управлению в «облаке» одного небезызвестного завода N требовалось найти подходящее железо. Однако оказалось, что в России по требованиям помехозащищенности устройства и открытости системы ничего подходящего не существует. Попытка заказать идеально подходящее нам устройство из-за рубежа провалилась, поскольку на территорию нашей страны оборудование с пометкой «IoT» весьма трудно ввести в промышленных масштабах. Другие же поставщики не устроили сроками доставки в 12 недель при небольших объемах и ценой. Поэтому в голове родилась и плотно осела мысль о создании своего устройства. Причем такого, чтобы оно было универсальным и подходило не только конкретно под этот один проект, а под множество других. В итоге от момента зарождения идеи, подбора поставщиков и корпуса, разработки платы, её отладки и тестирования, написания инструкций и технической документации прошло весьма много времени. Но зато теперь я держу с легким трепетом в руках полностью законченное и рабочее устройство, и могу заявлять, что мы это сделали!

Почему именно Raspberry?


Raspberry Pi – это небольшой и дешевый универсальный микрокомпьютер, гибко настраиваемый под любые задачи. С 2014 года он выпускается как самостоятельный вычислительный модуль Compute Module, то есть из привычной платы с различными интерфейсами и разъемами осталось только самое важное: процессор, ПЗУ и ОЗУ. Такое исполнение позволяет использовать это устройство для любых мыслимых и немыслимых задач, все лишь упирается в возможности фантазии для создания обвязки вокруг модуля. Стоит также заметить, что с момента выхода в свет первой версии устройства вышло уже три версии модулей, а после выхода Raspberry Pi 4, вероятно, стоит ожидать еще и четвертую версию в скором времени. Всё это говорит о том, что разработчики активно развивают свой продукт, увеличивают его мощность и быстродействие, и что их устройство пользуется определенной популярностью у людей. Эта популярность не беспочвенна: за всё это время они зарекомендовали себя как надежные устройства, способные решать задачи различного уровня в любых условиях, даже в космосе. Также программировать на Raspberry Pi условно просто, они обладают большим количеством интеграторов по всему миру.


Открытая операционная система Linux позволяет устанавливать на устройство абсолютно любое программное обеспечение в зависимости от требуемой задачи. Например для решений в области умного дома возможны стыковки с OpenHab, Home Assistant, iRidiumMobile, NodeRed и др. Для промышленности возможна установка SCADA-систем, таких как CODESYS, Rapid SCADA, OpenSCADA с возможностью использовать устройство как шлюз для передачи данных на верхний уровень по протоколам MQTT, http, REST API или CoAP. Также возможна интеграция с различными облачными сервисами.

Что по интерфейсам?

Разработка IoT-шлюза на базе Raspberry CM3+ Raspberry pi, Интернет вещей, Промышленность, Программирование, Автоматизация, Вагинальный контроллер, Длиннопост

Рисунок 2 — Вид платы сверху и снизу

Устройство в минимальном исполнение поддерживает следующие интерфейсы:


- RS485 х 2;

- RS232 х 1;

- CanBus х 1;

- 1-Wire х 1;

- USB х 1;

- Ethernet х 1;

- SMA x 2;

- SIM х 1;

- miniPCIe х 2;

- HDMI 4k х 1;

- MicroUSB х 1;

- MicroSD х 1;

- GPIO х 1.

- LED х 1 (программируемый);


Вышеописанные интерфейсы позволяют внедрить устройство практический в любой проект. А дополнительные аппаратные модули для установки в слоты Mini PCI-e от сторонних производителей решают проблему с наличием связи и интернета у устройства. Такой путь с установкой модулей связи нами был выбран не случайно, поскольку наличие USB-адаптеров (так называемых «свистков») является не очень надежным и качественным вариантом, а установка промышленных роутеров по типу Robustel R2000-3P является дорогостоящим (около 12 т.р.). Поэтому мы остановились на установке двух разъемов под модули Mini PCI-e, которые можно использовать по собственному желанию:


3G, LTE, GPRS модуль (HUAWEI MU709s-2, цена: 2,5 т.р.);

Wi-Fi модуль с возможностью подключения к нему направленной антенны;

NB-IoT модуль;

LoraWan модуль для построения сети «интернет вещей».


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

Дополнительные решения

- Аппаратный watchdog;

- Аппаратные часы реального времени;

- Энергонезависимая память EEPROM;

- Металлический корпус и крышки (алюминий 3 мм);

- Диапазон питания 9-36 В;

- Температурный диапазон -25...+80°C (по документам, тесты еще не проводились).

Разработка IoT-шлюза на базе Raspberry CM3+ Raspberry pi, Интернет вещей, Промышленность, Программирование, Автоматизация, Вагинальный контроллер, Длиннопост

Рисунок 3 — Устройство AntexGate в корпусе

Показать полностью 2
189

Компьютерный мастер. Часть 52. Машенька VS Excel, смертельная битва

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


Вариант№1 «О «девочке» Машеньке и чуде заморском «Excel» зовущимся»

Вариант№2 «Поле битвы I5-4200/16GB/SSD»

Вариант№3 «Научи Машу, таблички делать, она всю систему и повесит)))»


В «черном-черном» городе, была одна «серая компания», работали в ней творческие люди, и всю свою работу они писали в тетрадочки огромные.

Долго ли мало времени прошло неведомо, но стала та компания развиваться, и вот собрал всех директор и говорит.

- Мы же передовая компания?

- Передовая!!!!!!(хором кричат менеджеры)

-Так чего же мы всё бумагу мараем. Слышал я есть чудо заморское EXCELем зовется, так давайте самого смышлёного отправим на курсы шестимесячные, освоить его, кто пойдёт?

-(все хором) Конечно Машенька, она молодая толковая, из неё точно толк выйдет.


И прошла Машенька, курс длительный, в университете серьёзном, диплом красивый получила, и учёт весь в Excel наладила… И наступила благодать, смышлёные менеджеры, каждый в свою табличку всё заносят, а Машенька потом все данные вместе собирает, и всем удобство большое.

И минуло так три года, и с каждым годом стала Машенька всё больше жаловаться, что компьютер у неё совсем плох, и excel сломался тормозит и виснет, и сжалился директор, и купил ей моноблок новый современный на процессоре шустром INTEL CORE I5…

Минул ещё год и стала Машенька и моноблок это проклятый жаловаться, что тормозит чертяга, и дал директор  команду АПГРЕЙД сделать, памяти оперативной добавить, чудо техники SSD диск установить, чтобы Машенька и дальше удобство творила в своём EXCEL.

Год ещё прошёл, и опять беда нарисовалась, висит проклятый EXCEL, данные теряет, всё компанию в расходы вгоняет да убытки несет.


На этом сказочке конец, кто слушал молодец.

Далее наступает суровая реальность и к делу подключается MASTER095, т.е. Я.


Получаю я нового клиента с кучей проблем и в том, числе что у ведущего менеджера постоянные жалобы на зависания excel, смотрю комп мощный «I5-4200/16GB/SSD» ресурсов хватает.

Прошу Машу показать, как висит excel, не может, говорит «сегодня не висит», я чищу систему, убираю все ошибки, прогоняю все тесты, смотрю температуры, на всякий случай переустанавливаю excel полностью.


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

Приезжаю опять всё работает идеально, думаю, что за хня, на всякий случай меняю версию Excel с 2013 на 2016.


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

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


И вижу я примерно такую картину:

Компьютерный мастер. Часть 52. Машенька VS Excel, смертельная битва Компьютерный мастер, Менеджер, Excel, Всё гениальное просто, Автоматизация, Длиннопост

Сразу приходит в в голову идея, проверить редактирование файла этого файла на других компьютеров, и  файл вешает любой комп включая свеже купленный директорский на I7-9700. При копировании данных excel просто на 100% загружает проц, сжирает всю оперативку и Бинго комп висит.


А теперь расскажу, что в этом файле не так.

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

Компьютерный мастер. Часть 52. Машенька VS Excel, смертельная битва Компьютерный мастер, Менеджер, Excel, Всё гениальное просто, Автоматизация, Длиннопост

2) Второе на что надо обратить внимание, это количество листов(это внизу там где фамилии Иванов,Петров,Сидоров) - их оказалось 90+шт, по числу сотрудников, за эти 5 лет в компании поработавших…

Надо понимать, что EXCEL обрабатывает листы, подобно вкладкам в браузере. т.е. каждый лист(а тем более если там формулы, которые пересчитываются онлайн или при сохранении) выделяется ресурс процессора и оперативная память, и 100 листов повесят самую мощную систему… В excel, нельзя делать документ, в котором 50-100 листов… лучше сделать 10 документов по 10 листов.



По итогам решение проблемы

1)Заставил менеджеров убрать всю красоту, и оставить раскраску только ячеек где есть данные.

2)Удалили из актуального файла, листы всех уволившихся сотрудников, это в половину сократило количество листов в документе

3)Получившийся документ с разбили на два файла один по фамилиям от А до И, а второй от К до Я, и временно это проблему решило.

4)Ну и главное решили уйти от excel, и заказать ERP систему автоматизации, для фиксации всех работ и автоматического расчета зарплат и формирования отчетов.



По ERP автоматизации сделаю отдельный пост как закончим, пока честно это п..ц. Крупные компании говорят, что бюджет меньше миллиона их не интересует, многие запрашивают ТЗ, но в итоге сливаются, такое ощущение, что деньги на этом рынке никому не нужны… Средние компании (за бюджет 200-400 т.р.) насылают кучу менеджеров разводил, которые без подробного тех.задания просят 50% оплаты вперед. Есть куча одиночек, которые «мамой клянутся» что сделают всё за 100т.р., но показать примеры своих работ не готовы…. В итоге за месяц сузили круг исполнителей, до 4-ех средних компаний с портфолио, и готовых взяться за бюджет 200т.р.

Показать полностью 2
747

Автоматизация Excel с помощью VBA на примере графика отпусков

(Офисной оптимизации пост (теперь уже с примерами))


В прошлом своём посте (где рассматривал, что есть VBA в Excel и зачем это может пригодиться) целых 137 человек подписалось на меня, в комментариях были призывы к каким-нибудь примерам использования VBA, да и обещал я @Tiafreed подкинуть материалов для ВКР, так что набросал за ночь простенький (в сотню строк кода без использования массивов, классов и т.д.) файлик в Excel с VBA модулем. Пост разделю условно на две части: для пользователей, кому интересно просто посмотреть как выглядит, что делает, плюс скачать, поиграться и для продвинутых пользователей, кому интересно как это работает и как настроить подобное под себя. Цель поста - показать возможности VBA (частично), предложить интересный вариант реализации достаточно распространённой задачи по расчёту отсутствия сотрудников.

Если формат поста зайдёт, то в следующий раз набросаю пример, как формировать Word документы из списка данных в Excel, используя шаблон и пользовательскую форму (и не используя ублюдскую рассылку ИМХО).

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

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

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

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

*Все персонажи вымышлены, совпадения случайны


Дальше идём на другой лист, нажимаем кнопку

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

После чего идут расчёты какое-то время (у меня это где-то половина секунды)

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

Машина рапортует нам об успешном завершении своей миссии, идём смотреть, что вышло.

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

(Да, дизайнер я говно (как и VBA кодер в общем-то, но не об этом)) оформить самому никогда не сложно, главное, что на каждый день для каждого человека машина посчитала отпуска (можно любое другое отсутствие), вычислила цифры, разбила по отделам и отразила это графически, идём на главную

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

Тут всё ещё проще, кнопка для запуска, табличка с примитивными расчётами (формула МАКС) и график на 366 дней который можно с лёгкостью оформить самому и с помощью которого отлично видны провалы и пики нагрузок. Нажатием на выпадающий список, мы выбираем отдел по которому выводятся данные. Вот и всё, просто и удобно. Набросал за пару ночных часов. Сразу предупреждаю, что я это не предлагаю, как готовый продукт (успешное бизнес-решение ваших кадровых проблем), просто накидал маленький пример и делюсь им с вами, потому ответственность за его использование и обслуживание не несу, но если есть желание доработать его в своих целях, готов подсказать и помочь. Да, если возникла ошибка, вероятнее всего, что формат даты/числа нарушен, защиту от дурака не ставил, ибо цели чисто демонстрационные, но если вдруг мой косяк (протестить нет возможности) перезалью и ссылку в комментарии кину. Вот сам файл (на свой страх и риск :D, никаких гарантий, что будет работать). https://yadi.sk/d/lsRdKL8wQ42FFw (и не забываем включить макросы)

Сразу отвечу на вопрос - нахрена на VBA можно же на формулах? Да, можно, но так динамично (легко добавлять/убирать людей/отделы), наглядно и расчёт каждого дня формулами будет очень сильно грузить проц, а так мы считаем лишь раз, когда кнопку нажимаем.



Тэкс. Теперь вторая часть, в принципе, дальше можно не читать, так, для очистки совести её пишу. Кому интересно, как это работает или как вообще выглядит VBA на практике. Всё просто, в основе лежит вот эта строка (в ней мы будем искать колонку с датой отпуска и уже в ней работать)

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

Сначала мы подготавливаемся, что-то где-то очищаем, что-то добавляем (всё в общем-то закомментил) и сортируем строки по отделам

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

Потом запускаем цикл перебора строк с сотрудниками, в этом цикле для каждого работника мы проверяем, является ли он началом нового отдела, если да, то делаем разделитель, если нет - кладём болт и идём дальше, дальше рассчитываем отпуска, каким образом? Берём дату начала и ищем её в строке с датами, находим (или не находим и крашимся, если закосячили, не стал пилить защиту от дурака), берём эту ячейку как точку начала, прибавляем количество дней отпуска, отнимаем один (ибо включительно) и это наша точка окончания, объединяем эти ячейки, окрашиваем, в этих столбцах делаем простые расчёты (+1 к каждому дню и перерасчёт процентовки). После прохода по всем персонажам просто копируем полученные цифры на главную страницу, чтобы подставлять их в график. Всё, почти.

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост
Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

И простейший обработчик для выпадающего списка - просто вставляем в строку из которой берёт данные график данные из нужной нам строки. Рассчитываем её как номер строки начала (у нас 22) + номер элемента выпадающего списка (нумерация идёт с нуля у listindex)

Автоматизация Excel с помощью VBA на примере графика отпусков Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Ms Office, Длиннопост

P.S. Знаю что говнокод, так что буду рад конструктивной критике, чему-то подучиться у более знающих. В свою защиту скажу, что код писался на халяву, без обработчика ошибок, заплаток, защиты от выстрелов в ноги, ограничений действий, фиксирования диапазона вводимых данных, добавления полосы загрузки и всё такое, чисто для демонстрации. Спасибо, если дочитал это до конца.

Показать полностью 10
1032

Не только финансовая система может держаться на Excel

(Офисной оптимизации пост, точнее, об одном из инструментов этой самой оптимизации)


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

Не только финансовая система может держаться на Excel Excel, Программирование, Офис, Оптимизация, Visual Basic, Макрос, Microsoft Word, Ms Office, Длиннопост

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

Сами по себе формулы - очень мощный инструмент, на умении их использовать вывезло столько оптимизаторов, не владеющих программированием, что, думаю, нет еще офиса, где какой-нибудь местный Кулибин в обеденный перерыв не замутил еще какую-нибудь узкоспециализированную считалку для отдела. Но, работая с большими массивами возникает ряд проблем в использовании формул: относительность (ты получаешь не строку информации в базе данных, а динамичный результат вычисления) и оптимизация. Да, оптимизация на нескольких тысячах строк с десятком колонок и, допустим, парой связанных таблиц, это беда. Такая связка на i3 4гб оперативы просто будет повергать бедный офисный комп в ужас, заставляя его терять сознание при каждом пересчете и вылетать, если ты нарушил священный ритуал пятиминутного сохранения (знал я одного мужика, у него была такая формульная портянка, что сохранял базу он лишь два раза в день, перед обедом и уходя домой, ибо на сохранение уходило минут 20, ненавидели мы его все, ибо, уходя в отпуск, он оставлял это чудище кому-то из нас). Специфика работы была такова, что интернета у нас не было, а стороннее ПО нельзя - пользуйтесь чем дали. Окей, но автоматика же нужна, без неё никуда, потому пришлось использовать то, что есть и открывается это:

Не только финансовая система может держаться на Excel Excel, Программирование, Офис, Оптимизация, Visual Basic, Макрос, Microsoft Word, Ms Office, Длиннопост

Разработчикам показалось мало создать мощный инструмент формул, они решили впаять в и так могущественное ПО целый язык программирования (точнее его диалект) VBA (Visual Basic for Application), возможность подключать модули с других языков, использовать API (хоть и работает это ИМХО через жопу) и встроенную среду разработки (а это означает, что вообще ничего не надо качать, если у Вас есть офис, значит все что нужно, чтобы стать мамкиным программистом уже есть). Но что нам это даёт? Огромные (ну это как посмотреть) возможности для разработки ПО, преимущественно узкоспециализированного, без использования чего-либо кроме Excel; базы данных? О чём речь, Excel - это и есть БД (то ещё извращение, но для утонченных можно связать с Access или Sql), возможность проводить расчёты (циклы, тонкие переборы, фильтрация) над большими массивами информации в кустарных условиях, использовать встроенные библиотеки для работы с другими приложениями (самое важное - MS Word), возможность наконец применить Visual Basic, который ты учил лет 20 назад, а он нигде так и не пригодился, ну и самое главное, научиться основам программирования, если ты что-то шаришь, но твои лучшие успехи - верстка шаблонов сайтов на HTML, CSS с вкраплениями PHP. Также это нам даёт возможность кодить на ведре (прям совсем ведре-ведре). VB хоть и относится к ООП, но де-факто работа в нём редко сводится к пользовательским классам, всяким тонкостям и т.д., в основном он ощущается как скриптовый язык, работа приходит к чему:

Не только финансовая система может держаться на Excel Excel, Программирование, Офис, Оптимизация, Visual Basic, Макрос, Microsoft Word, Ms Office, Длиннопост

накидать разных элементов управления (это если у нас пользовательская форма, если просто код, то вообще просто запихать всё в модуль) и привязать к ним обработчики событий. Вкурил в циклы, условные конструкции, чутка простого синтаксиса и на рабочий телефон уже звонят из Майкрософт и предлагают тебе перейти к ним (Нет). Но не буду вдаваться в разработку ведь не об этом пост, что из этого можно получить на практике? Рабочие программы с базами данных и пользовательским интерфейсом (чтобы упростить и ограничить взаимодействие пользователя с данными) или скрипты для обработки целой тонны упорядоченной (а может и нет, слава условным конструкциям) информации. Например (набросал за минуту, палками не бить):

Не только финансовая система может держаться на Excel Excel, Программирование, Офис, Оптимизация, Visual Basic, Макрос, Microsoft Word, Ms Office, Длиннопост

Немного возни и у нас есть пользовательская форма со списком сотрудников и полями, которые надо заполнить, на выходе по нажатию кнопки мы получаем готовую вордовскую справку. Для этого нужен лист с данными сотрудников, лист список справок и шаблон MS Word. Готово, вы бог офисной оптимизации. И так можно многое: отчёты, справки, товарные чеки, письма, документы разные, приказы целые, любые действия с информацией.

Сразу предупрежу всех, кто уже нацелился писать "на кой хрен ты раскопал этот старый кусок говна на заре 30го десятилетия 21го века". Пост чисто информативный, это не гайд, не самоучитель, может кому интересно, на прорыв в IT сфере ни разу не претендует, это раз, есть в нашей стране места, где развитие этой сферы отстаёт как раз на эти 20 лет, это два, ну и просто, может кому понадобится, может кто-то захочет на работе чему-то подучиться.
Какие минусы? Оптимизация всё равно сосёт бибу (но не такую, как формулы), безопасность тоже, чисто теоретически можно использовать криптографическое шифрование БД и расшифрование в ходе работы, но я не проверял, можно ли легко вскрыть защиту самого VBA проекта, да и оптимизация пососёт ещё большую бибу (да и вообще, кому это надо, ребят, это же Excel), ну а стандартную Excel защиту листов можно вскрыть обычным архивом и блокнотом. Также, недостатком я считаю ряд ограничений среды, по типу того, что без API не работает прокрутка колёсиком мыши, стабильность - excel иногда любит чудить. Совместимость - отдельные танцы с бубном для x64 и x32 (но это если используете сторонние API, модули). Ну и объяснять людям, как разрешить запуск макросов :D.
Спасибо, если дочитал этот длинный (и наверное скучный) пост до конца, если вдруг кому стало интересно могу написать ещё много чего, например, как написать сапёр на Excel, как сделать различные простенькие, но очень нужные офисные программки, как научиться этому (но.. зачем?), как использовать макрорекордер и много чего ещё, связанного с Excel. А ведь это всё ещё цветочки, есть люди, которые целые стратегические пошаговые игры в ячейках писали на том же VBA.

P.S. Если ты профи, знаешь больше и лучше меня, и видишь, что я в чём-то неправ - поправь, буду рад.

Показать полностью 3
618

Автоматизация предприятий как она есть на самом деле

Гендир: Мы купили автобус, потому что производитель заверил, что он быстро едет и мы все поместимся. Теперь мы быстро доберёмся до цели.

Главный продажник: Дык а чего мы едем вокруг то? Цель там, а мы куда?

Прог: Ну просто дорога вокруг же и надо по ней ехать жеж...

Главный продажник: Что за ересь? Соседи вон напрямик же едут! Ты, прог, сделай так что бы и мы могли к цели двигаться, а не вокруг.

Прог: Но у них же трактор на гусеницах. Ну ладно, сейчас тут и тут поправим, это прикрутим, это подогнём... Вот, вроде всё. Как и просили, теперь тоже можем напрямик ехать.

Гендир: Я не понял, а что мы со скоростью таракана едем? Изготовитель обещал 80 км/ч!

Программист: Ну тут ведь это... гусеницы поставили, а они ж того..

Гендир: Что ты тут мне лапшу вешаешь? Производитель же заверил, что быстро ехать будем. Это ты просто ничего не умеешь и надо спеца пригласить!!!!

Спец-автоматизатор: Оооо, друзья, это же извечная проблема всех контор. Посмотрите как живут настоящие счастливчики. За скромную плату мы вам прикрутим пропеллер и будете летать как все настоящие конторы на вертолёте.

Гендир: Что-то я не понял, а что за фигня сверху крутится, шумит, а мы теперь ещё и провода с деревьями должны объезжать?

Автоматизатор: Так известное дело! Просто ваш программист же понаворотил всякой ерунды, вот разбега и не хватает для взлёта.

Главбух: А на прошлой работе мы тележку прицепили сзади и собирали грибы по по пути. И было так классно! Давайте программиста попросим тележку прицепить? Или лучше настоящих спецов пригласить из майкрософта?

6219

Автоматизация на почте

Устроился на работу сотрудником почты. На окраине города стоит полупустое здание, там начальство решило сделать архив. Рабочие привезли туда огромную груду мешков с письмами, которые требовалось отсортировать и занести в базу. Процесс таков: имеется пачка писем, обвязанная шпагатом. У каждой пачки есть накладная со штрихкодом, в котором зашифрована информация по этой пачке. Нужно 1) нажать в программе кнопку "сканировать штрихкод" 2) провести сканером по накладной 3) подождать 10-30 секунд пока программа расшифрует ШК 4) нажать кнопку "внести данные" 5) подождать 30-90 секунд 6) написать на пачке диапазон номеров, которые программа присвоила этим письмам (10-15 сек) 7) нажать кнопку "закрыть накладную" (5-10 сек), выйти назад в меню 8) следующий цикл.


Как видите, все эти ожидания плюс время на нажатие кнопок отнимает много времени. Во время 10-30 и 30-90 секундных перерывов можно занять себя открывая очередной мешок в другом конце комнаты и вытаскивая новые пачки, однако приходится бегать туда сюда и жать кнопки. Тогда я написал скрипт, автоматически определяющий когда закончился предыдущий шаг (распознавание экрана), жмущий нужные кнопки, а также назначил горячие клавиши, которые быстро выходят в меню и начинают новый цикл. Также я наловчился писать цифры загодя на основе диапазонов предыдущих пачек и количества писем в текущей. Теперь процесс выглядит так: сканирую ШК, иду занять себя другими делами (вскрытие мешков, написание цифр на следующих накладных, складирование готовых), программа в это время сама проходит полный цикл, возвращаюсь, сканирую следующий ШК и т.д. Скорость работы приблизилась к теоретическому пределу: теперь все упиралось в скорость работы программы, ускорить которую я не мог. Но этого вполне хватало, программа постоянно что-то обрабатывала, а я выполнял другие работы.


Также были бракованные накладные или пачки писем вообще без накладных. Приходилось брать каждое письмо 1) сканировать его ШК 2) клик "ввести данные" 3) 1-2 секунды программа думает 4) вылезает окно с характеристиками письма, тут ничего не делаем, все письма одинаковые, жмем "сохранить письмо" 5) следующий цикл

Как видите, опять много ненужных движений, нужно откладывать сканер в сторону, тянуться к мышке, два раза жать в разные места на экране. Моментально был написан скрипт, автоматизирующий всю эту лабуду, теперь процесс свелся к "пикнул письмо сканером (сканер издает звук при считывании), жду 2 секунды, пикаю следующее письмо" и т.д. Работа ускорилась в 2-4 раза, да и сил тратится гораздо меньше.


Но мне и этого показалось мало. Мало приятного в двухсекундном пялении в монитор. Открыл блокнот, занес туда штрихкоды писем, каждое письмо требует треть-полсекунды. Затем пишется еще один скрипт, который заносит в программу из блокнота по письму каждые 2 секунды, а я иду пить чай (а писем очень много, 10-30 тысяч, их накопилось достаточно за три года). Или не иду. Принес с собой ноутбук, пока скрипт работает над другой пачкой писем, открываю на ноуте блокнот и вношу туда следующую партию. Скорость работы выросла еще больше.


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


Мои посты по автоматизации


Автоматизация с AutoHotKey

https://pikabu.ru/story/avtomatizatsiya_rutinyi_s_autohotkey...


Распознавание экрана в AutoHotKey https://pikabu.ru/story/avtomatizatsiya_rutinyi_s_autohotkey...

Показать полностью
172

Домашняя бухгалтерия, или Javascript от не программиста | Часть [0]

Доброго времени суток вам, пикабушники. Решил поделиться с вами личным рецептом как легко и без напряга вести домашнюю бухгалтерию, записывая все покупки, при этом не тратя много времени и сил.
если кому интересно - прошу под кат, остальные проходим мимо.

Коммент для минусов прилагается.

Домашняя бухгалтерия, или Javascript от не программиста | Часть [0] Javascript, Программирование, Android, Субд, Excel, Длиннопост, Бухгалтерия, Моё

Какое-то время назад появилась необходимость вести домашнюю бухгалтерию и собирать статистику по тому какие продукты покупаются, как часто, за сколько, где и т.п., с целью оптимизации расходов и планирования домашнего бюджета.
Но после нескольких неудачных попыток реализации проекта средствами Excel, Access и т.п., пришел к пониманию нескольких ключевых моментов:
1) Решение должно быть на мобильной платформе (т.е. никакого ПК)
2) Действия должны быть максимально простыми и удобными (т.е. чтобы все можно было делать на ходу в пару кликов)
3) Действия не должны занимать много времени

Подробно рассмотрев все вышеперечисленные пункты, я остановил свой выбор на реализации проекта на платформе Andrioid, но к сожалению изучив весь Play Market осознал что готовых решений на данную тему там или нет, или есть, но чем-то они меня не устраивают. В основном в большинстве случаев это было - не гибкость, т.е. невозможность изменять некоторые пункты под свои конкретные нужды.
Вариант написать свою программу под дройда, к сожалению, тоже был забракован, т.к. я не имею опыта разработки для дройда, а свободного времени очень мало.
Но, в итоге, решение было таки найдено!

Шаг[0] - Получение данных из чека
С переходом России на систему онлайн касс и электронных чеков появилась отличная возможность не переписывать данные из чека, а копировать все с сайта ФНС. Для этих целей было скачено приложение, при помощи которого можно легко отсканировать QR код на чеке и получить электронную версию чека. Выглядит это примерно так:

Домашняя бухгалтерия, или Javascript от не программиста | Часть [0] Javascript, Программирование, Android, Субд, Excel, Длиннопост, Бухгалтерия, Моё

Далее, нажав "Выделить всё" и "Копировать" мы получаем все что нужно.

Шаг[1] - Преобразование и структурирование данных
Получение данных из чека, как оказалось, самая простая и незатейливая часть всего процесса. Дальше идёт танцы с бубнами.
После довольно продолжительных поисков в Play Market было найдено приложение - простая СУБД для дройда, с возможностью создания форм для заполнения и сохранения данных в виде таблиц, которые в последствии можно экспортировать в Google Drive в виде excel таблицы, и в котором есть возможность создавать довольно сложные скрипты и триггеры по средством JavaScript.
Для полноценной работы и удобства пришлось создать в этом приложении 3 библиотеки:
1) Чеки - для данных из чеков.

Домашняя бухгалтерия, или Javascript от не программиста | Часть [0] Javascript, Программирование, Android, Субд, Excel, Длиннопост, Бухгалтерия, Моё

В форме для заполнения всего три поля:
"Магазин" - Текстовое поле, где нужно выбрать один из существующих пунктов, или создать новый нажав "+"
"Координаты" - соответственно координаты магазина. Это нужно для большей точности, так как иногда в разных магазинах одной сети ассортимент немного различается.
"Чек" - поле куда нужно вставить данные скопированные из приложения проверки чеков ФНС.
После сохранения формы все выглядит таким образом:

Домашняя бухгалтерия, или Javascript от не программиста | Часть [0] Javascript, Программирование, Android, Субд, Excel, Длиннопост, Бухгалтерия, Моё

После сохранения добавляются поля:
"Адрес" - Подтягивается автоматически по координатам
"ИНН" - Подтягивается скриптом из чека
"ФН" - Подтягивается скриптом из чека
"ФД" - Подтягивается скриптом из чека
"ФПД" - Подтягивается скриптом из чека
"ККТ" - Подтягивается скриптом из чека
"Итого" - Подтягивается скриптом из чека
"Товары" - Подтягивается скриптом из чека

Так же после сохранения срабатывает триггер, который импортирует данные из чека в две другие библиотеки:
"Товары" - справочник товаров
"Покупки" - таблица с покупками

Вот так выглядит экран редактора скриптов:

Домашняя бухгалтерия, или Javascript от не программиста | Часть [0] Javascript, Программирование, Android, Субд, Excel, Длиннопост, Бухгалтерия, Моё

После импорта данных в библиотеке "Покупки" всё выглядит примерно таким образом:

Домашняя бухгалтерия, или Javascript от не программиста | Часть [0] Javascript, Программирование, Android, Субд, Excel, Длиннопост, Бухгалтерия, Моё

Каждую запись можно открыть и тогда будет возможность просмотра данных по покупке более подробно:

Домашняя бухгалтерия, или Javascript от не программиста | Часть [0] Javascript, Программирование, Android, Субд, Excel, Длиннопост, Бухгалтерия, Моё
Домашняя бухгалтерия, или Javascript от не программиста | Часть [0] Javascript, Программирование, Android, Субд, Excel, Длиннопост, Бухгалтерия, Моё

Все данные товара, если товар есть в справочнике подтягиваются скриптом в момент импорта. Если товар новый то придется заполнять самим. Но так как товары покупаются примерно одни и те же, то со временем необходимость заносить руками данные отпадёт.
Экран редактирования записи, кстати, выглядит подобным образом:

Домашняя бухгалтерия, или Javascript от не программиста | Часть [0] Javascript, Программирование, Android, Субд, Excel, Длиннопост, Бухгалтерия, Моё

В случае корректировки каких-либо данных по товару в карточке покупки, данные в справочнике тоже обновляются скриптом.

В последствии, все данные из трёх библиотек - "Чеки", "Покупки" и "Товары" можно легко синхронизировать с Google Drive, где они будут доступны для просмотра и редактирования в виде Excel таблиц.
Для примера, таблица синхронизирована с библиотекой "Товары":
https://docs.google.com/spreadsheets/d/1jghXNRnxEgMop6qzA0pE...

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

Если пост кому-то будет интересен и не утонет в минусах, то в следующий раз расскажу обо всём подробнее...

Показать полностью 8
3767

Excel в качестве 3D-движка

Да, Excel. Та офисная программа с таблицами. Некто скрывающийся под ником "C Bel" написал на ней простенькую бродилку.


"Документ" поддерживает:


• генерацию лабиринтов

• рендеринг в реальном времени

• освещение

Подробнейший разбор (на английском): https://www.gamasutra.com/blogs/CBel/20180213/308549/3D_engi...


P.S. Это далеко не первый случай, когда Excel используют в качестве игрового движка. До этого народным умельцам удалось воссоздать его мощностями... X-Com.

181

Ответ на пост Excel в excel

Послезавтра релиз, дел полно, сижу листаю Пикабу и натыкаюсь на такой пост

http://pikabu.ru/story/excel_v_excel_5008626


Посидел, пораскинул мозгами: "А надо ли мне это сейчас?" Решено - надо.

Я же программист, да из захотелось отвлечься от громоздких корпоративных разработок.

С перерывами и отвлечениями на работу чай за часик накидал консольную программку на C#. Работает в Office 2013.

Программа спартанская, в ней нет никаких проверок: если неправильный формат картинки - она падает, если картинка не найдена  - она падает и т.д. И еще, чтобы ее запустить, должны быть установлены инструменты разработчика Office, которые ставятся при установки офиса.

Ответ на пост Excel в excel Excel, Комунечегоделать, Гифка, Тыжпрограммист, Программирование

Перевод картинки в Excel занял примерно 50 минут. Держа в голове, что по работе много всего делать, я в это время основательно ничего не делал. Кажется кто-то скоро получит по шапке.

Код лежит тут:

https://github.com/bildeyko/ExcelPic

а exe файлик с картинками и полученным xlsx файлом в архиве тут:

https://github.com/bildeyko/ExcelPic/releases/tag/v1


Если кто-то захочет запустить, делается это так: в консоли, перейдя в директорию с  программой, ввести ExcelPic.exe 1.bmp

1263

Автоматизация рутины с AutoHotKey

Здравствуйте! Сегодня я хочу познакомить вас с замечательным инструментом автоматизации под названием AutoHotKey!

С ним вы сможете:

1) Программировать действия мышкой, нажатия клавиш;

2) Быстро заполнять однотипные бланки, формы;

3) Ставить на горячие клавиши заклинания, кастовать их в течение миллисекунд после перезарядки (что бывает критично);

4) Автоматизировать какие-либо рутинные действия и идти пить кофе.


Сразу предупрежу - писать скрипты поначалу очень капризное занятие. Будут такие ситуации, что код почему-то не работает, и вам придется думать полдня где вы совершили ошибку. Листать документацию. Просить помощи в интернете. Проверять синтаксис.

Однако вложенное время окупается с лихвой. При достаточном освоении AutoHotKey сэкономит вам кучу времени и нервов.


В данном посте я дам лишь описание конкретных примеров применения скриптинга на практике и расскажу про те подводные камни, которые не описаны в документации.


Итак, начнем. Ссылка на программу:

https://autohotkey.com/download/ahk-install.exe (3 Мб)


Написание кода ведется в файлах *.ahk, которые редактируются обычным блокнотом.

Создайте текстовый файл и измените его расширение.

Автоматизация рутины с AutoHotKey Autohotkey, Автоматизация, Программирование, Офисные будни, Образование, Длиннопост

Затем откройте его с помощью блокнота. Теперь вы готовы к работе!

Перейдем к практике.

Напишите


F1:: Send Здравствуйте{!}


Сохраните изменения. Запустите скрипт двойным кликом. Встаньте на какое-нибудь текстовое поле и нажмите F1. Первый опыт скриптинга у вас уже есть.

!, #, ^, +  это служебные знаки. Для того, чтобы напечатать их, нужно оформить их скобками.

Для выключения скрипта нажмите на его иконке возле часов, затем Exit.


Краткое введение закончено, дальше пойдут лишь небольшие советы.


1) Добавьте к скрипту

F12:: Suspend, Toggle

, чтобы ставить/снимать его с паузы клавишей F12


2) Чтобы горячая клавиша не блокировалась скриптом и выполняла также свою обычную функцию, пишите ~.

~F3:: Send {Down}


3)

1::

Send {Tab 5}привет{Enter}  ;пять раз таб, привет, энтер

Click 100, 100  ;клик по заданным координатам, отсчитывается слева-сверху

Click, 2  ;клик два раза

Click right  ;клик правой кнопкой

Send {Down}{Left}{Up}

Send {PgUp}{PgDn}

Send {Space}{Del}

Send {b down}

Sleep, 1000  ;зажать b, ждать 1 секунду, отпустить

Send {b up}  

Return  ;конец списка команд, вызываемых кнопкой "1"


Полный список клавиш смотрите в документации. Пуск -> AutoHotKey -> AutoHotKey HelpFile -> Mouse and Keyboard -> Send


4)

^F1::  ;при нажатии на Ctrl+F1

MouseGetPos, X, Y  ;выдаст текущие координаты курсора

MsgBox, %X%, %Y%

Clipboard = %X%, %Y%  ;и скопирует их в буфер обмена

Return


Обратите внимание - по умолчанию координаты отсчитываются от левого верхнего угла активного окна.

Можно заставить скрипт отсчитывать координаты от левого верхнего угла экрана, предварительно прописав:


CoordMode, Mouse, Screen


Вернуться обратно:


CoordMode, Mouse, Window


5) Модификаторы: "^" = Ctrl, "+" = Shift, "!" = Alt.

Send !{Tab}  ;Alt+Tab

Send +{Home}

Send ^{vk56}  ;Ctrl+V


Почему именно {vk56}, а не {v}? Потому что ^{v} будет работать только при английской раскладке, при русской пришлось бы написать ^{м}.

Однако без всего это можно обойтись, обратившись к клавише через ее виртуальный код.


Список наиболее часто используемых виртуальных кодов:


Send ^{vk43} ;Ctrl+C

Send ^{vk56} ;ctrl+V

Send ^{vk58} ;ctrl+X

Send ^{vk41} ;ctrl+A

Send ^{vk5A} ;ctrl+Z

Send ^{vk53} ;ctrl+S

Send ^{vk51} ;ctrl+Q

Send ^{vk57} ;ctrl+W

Send ^{vk52} ;ctrl+R

Send ^{vk42} ;ctrl+B

Send ^{vk4E} ;ctrl+N

Send ^{vk4C} ;ctrl+L


Аналогично, если вы хотите сделать буквенную клавишу горячей, лучше обратиться к ней через ее виртуальный код:


vk56::Send {Click}  ;посылает клик при нажатии на "м" или "v".


Полный список виртуальных кодов клавиш: http://www.kbdedit.com/manual/low_level_vk_list.html


6) Быстро разворачиваем фотографии правильной стороной.

В стандартной утилите просмотра фотографий Windows есть опция "Развернуть картинку"

Автоматизация рутины с AutoHotKey Autohotkey, Автоматизация, Программирование, Офисные будни, Образование, Длиннопост

NumPad1:: Send ^{,}

NumPad2:: Send ^{.}


Обратите внимание, что эти клавиши работают только при английской раскладке.

Английские точка с запятой расположены справа от клавиши "m" и являются отдельными клавишами. Русская точка и запятая находятся слева от шифта и объединены в одну клавишу.

И еще: чтобы нажать Numpad1 и 2, должна гореть лампочка NumLock.


7)

~1::

Loop,

{

If A_Cursor=Unknown

Click

Sleep, 20

If GetKeyState("2","p")=1

Break

}

F12::Suspend, Toggle


При нажатии кнопку 1 скрипт будет посылать клики, если заметит, что курсор принял форму руки. Нажатие кнопки 2 (и непродолжительное ее удерживание) разрывает цикл.

Осторожно, если вы наведете мышь на ссылки браузера при активном цикле, он вам их откроет!


Таким образом я автоматизировал прохождение флеш игры Deanimator.

Достаточно навести мышкой на зомби, и профессор сам стреляет, да еще и со скоростью пулемета!

http://www.de-animator.com/deanimator.swf

Автоматизация рутины с AutoHotKey Autohotkey, Автоматизация, Программирование, Офисные будни, Образование, Длиннопост

8)

1::

MouseGetPos, xpos, ypos

MouseClick, left, 130, 70, , 0 ; [WhichButton , X, Y, ClickCount, Speed, D|U, R]

MouseMove, xpos, ypos, 0

Return


Скрипт кликает карточку горохострела вверху экрана и вовзвращает курсор на место.

Цифры 0 в обоих операторах отвечают за скорость перемещения курсора, то есть мгновенно.

Можно закрепить за клавишей 1 горохострел, за 2 - подсолнух и т.д., и быстро без напряга сажать растения.

(координаты 130, 70 вам, скорее всего, придется поменять. Воспользуйтесь пунктом 4)

Автоматизация рутины с AutoHotKey Autohotkey, Автоматизация, Программирование, Офисные будни, Образование, Длиннопост

9) Допустим, вам нужно поставить звездочку или другой символ в конце каждой строки.

Автоматизация рутины с AutoHotKey Autohotkey, Автоматизация, Программирование, Офисные будни, Образование, Длиннопост

NumPad1::

Send {*}{Up}{End}

Return


Ставим звездочку, переходим вверх, идем в конец строки. Готово!

Если нужно удалить символ в конце каждой строки, пишем:


NumPad1::

Send {Backspace}{Up}{End}

Return


Если нужно удалить два символа:


NumPad1::

Send {Backspace 2}{Up}{End}

Return


10) Нужно щелкать мышью как можно чаще? Не проблема!

Автоматизация рутины с AutoHotKey Autohotkey, Автоматизация, Программирование, Офисные будни, Образование, Длиннопост

~1::

While GetKeyState("1","p")=1

{

Click

Sleep 20

}

Return


Данный скрипт посылает клики каждые 20 мс при нажатой клавише "1".

Или можно так. Посылаем клики до тех пор, пока не будет нажата клавиша "2".


1::

While GetKeyState("2","p")=0

{

Click

Sleep 20

}

Return



Ладно, на этом пока все. Будут вопросы - обращайтесь!

Можно даже сделать так: пишите какие типичные рутинные операции нужно разобрать, и они появятся в следующем посте!


P.S.

/*

Многострочный комментарий.

Для выключения участков кода

*/

Показать полностью 5
Похожие посты закончились. Возможно, вас заинтересуют другие посты по тегам: