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 'запись во вторую ячейку второй строки

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

Отредактировал TotKtoVTanke 19 дней назад
+16

Простите, а файл перед закрытием ВСЕГДА сохраняется теперь? То есть даже не спросит "сохранить перед выходом?" Это же потенциально просто бомба. Кто-то поработает в файле в расчете его не сохранять, а он все сохранит.

Решение с записью в отдельный файл намного безопаснее

+7

Во костыли костыльные! А аудит чтения файлов на файл сервере не включить?

Вот шаги на англ. -


1. Navigate to the required file share → Right-click it and select "Properties".


Switch to the "Security" tab → Click the "Advanced" button → Go to the "Auditing" tab → Click the "Add" button.


Configure the following settings: Principal: "Everyone"; Type: "All"; Applies to: "This folder, subfolders and files"; Advanced Permissions: "List folder / read data" → Click "OK" three times.


2. Log on to your domain controller and run gpmc.msc → Create a new GPO and define its name → Go to “Computer Policy” → Click “Computer Configuration” → Choose “Windows Settings” → Click “Security Settings” and enable the following settings:

Local Policies → Audit Policy → Audit object access → Define → Success and Failures

Advanced Audit Policy Configuration → System Audit Policies → Object Access → Audit File System → Define → Success and Failures


Advanced Audit Policy Configuration → System Audit Policies → Object Access → Audit Handle Manipulation → Define → Success and Failures


Go to Event Log → Define and specify the following settings:


Maximum security log size: 4GB

Retention method for security log: “Overwrite events as needed”


3. Link the new GPO to an OU with file servers as follows: Go to "Group Policy Management" → Right-click the OU → Click "Link an Existing GPO" → Select the GPO that you created.


4. Force a Group Policy update on the selected OU: Go to "Group Policy Management" → Right-click the OU → Сlick "Group Policy Update".

Open Event Viewer → Search the Security Windows Logs for event ID 4663 with the string "Accesses: ReadData (or ListDirectory)" and review who read or attempted to read files on your file servers.

Detect Who Read a FIle with Native Auditing


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

раскрыть ветку 7
+3
Во-во. Читал, в голове только: "вы там совсем ебанулись?!" Ну что за пиздец. На vba... придумывать логирование... ААААА!
+2

Мне как пользователю экселя проще то что в посте, а то что выше написано похоже больше на эльфийский

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

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

раскрыть ветку 2
0
Не сомневаюсь, что скопипастить - легче, чем описать процесс, но мало кто поймет, о чем тут вообще идет речь. Суть - настроить на файловой шаре, где хранится файл, аудит доступа к нему. Все можно сделать штатными средствами Винды без всяких макросов на VBA. Только мне не очень понятно - зачем вообще вешать аудит на чтение, если в файле не КТ? А если КТ, то почему в виде эксельки на шаре?
раскрыть ветку 1
0

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

+9
На планете Excel много полезных статей, не плохо бы указать ссылку на автора оригинальной статьи по заметнее, чем в середине текста.
раскрыть ветку 1
+4

Там есть скриншот с именем автора, а ссылка указана в конце, я ни в коем случае не претендую на авторство статьи

+8
Спасибо, полезная информация. Сохранил.
+2

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

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

Любой пароль в экселе не надёжный и взламывается за 60 секунд :)

+3
Проще логировать в блокнот, он позволит пользовать файл на сети и спокойно делать записи от нескольких пользователей одновременно.
раскрыть ветку 2
+3

Как это сделать? Можете описать?

раскрыть ветку 1
+8
В vba есть несколько способов делать записи в блокнот, я пользовался объектом FileSystemObject в классах которого есть стримы, то бишь потоки, которые могут работать в режиме добавления строк в блокнот, что позволяет избежать взаимной блокировки при работе нескольких пользователей. В остальном логика та же, создаёте событие в книге/листе вроде предложенного открытия/закрытия в статье и вместо записи на лист делаете запись на блокнот, который может лежать в сети, в скрытой папке и т.д., а когда нужно - в ексель легко вставляются данные из блокнота, можно опять таки и силами vba
+1
Какой уже раз убеждаюсь, что Excel умеет почти все.. Гениальное изобретение!
За статью отдельное спасибо!
+1

Открыть LibreOffice  или закачать в Google Docs, и привет хитрожопому автору.

раскрыть ветку 14
+7

Вы знаете, мне однажды пользователь позвонил с дикими воплями и орал что мы (ИТшники) куда-то дели его почту! Сквозь обвинения я честно сначала подумала, что почтовый ящик с exchange удалили, пока пыталась понять что случилось, он орал как потерпевший и сулил анальные кары от начальника. В итоге оказалось, что он удалил ярлык с рабочего стола.

А теперь мякотка: это не пожилой человек, по голосу парень моего возраста, 25-27 лет. Т.е. этот человек как-то на компьютере писал курсачи, лабораторки, контрольные и защитил диплом.

Так что это сработает в 90% случаев

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

Security through obscurity - хреновый подход.


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

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

0
Всё так, как вы описали.
Сейчас работаю с двумя обалдуями лет по 35 и одним "старпером". Так вот эти два дебила то файл сохранить не могут, то пару строчек в Excel добавить, а виноват - я! А старпер спрашивает, интересуется, учится как то сделать, как это добавить.
Короче, думаю, мне можно не бояться остаться без работы.
+3

Не у всех же есть LibreOffice или вообще Интернет на работе

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

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

-2

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

раскрыть ветку 6
+1
В либреофисе отображается время открытия и пользователь?
раскрыть ветку 1
+1

В либре файл откроется с игнорированием ВБА.

0

Приветствую. Есть вопрос, на который гугл мне не ответил. Или ответил, но я его не понял....

Цель: создать файл Excel, при открытии которого на листе будет доступна для редактирования только ВТОРАЯ строка. Остальные строки на листе (первая и все остальные, начиная с третьей), заполненные при предыдущих открытиях, недоступны для редактирования и выделены сереньким таким, невзрачненьким цветом.

Если необходима еще одна строчка для редактирования - добавлять кнопкой "Добавить строку". При этом предыдущая, полностью заполненная, становится третьей, серой и недоступной для редактирования. Если не полностью заполнена, то не становится и требует ввода данных.

Помогите кто чем может, сами мы не местные....тьфу ты...помогите с макросом плиз.

0

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

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

В скачанном файле лог будет продолжать вестись.

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

И кто его прочитает?

0
Норм. пригодится для контроля за будущим подопечным))
-5

какие-то троллейбусы из хлеба изобретаете, вместо того чтобы пользоваться google sheets

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

обиделись(

ещё комментарии
Похожие посты
366

ВПР и числа-как-текст

Обычные числа и числа-как-текст (т.е. числа, которые только выглядят как числа, а, по-сути, являются текстом) - это причина многих проблем и сложностей при работе с данными в Microsoft Excel. Одна из подобных ситуаций - использование функции ВПР (VLOOKUP) для поиска и подстановки, когда в исходных данных есть эти пресловутые числа в текстовом формате.


Рассмотрим классический пример - подстановку цен из прайс-листа в таблицу заказов по совпадению артикулов:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Как и множество других функций и инструментов Excel, ВПР считает числа как текст не равноценными этим же числам в нормальном виде, поэтому выдаёт ошибку #Н/Д, сигнализируя, что искомое значение не обнаружено, хотя визуально оно, вроде бы, есть.


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


Примечание: Различить нормальные и текстовые числа не всегда легко. Иногда на таких ячейках появляется зелёный уголок-индикатор, иногда - нет. В этой статье, для наглядности, я буду выравнивать числа-как-текст по левому краю, а нормальные числа - по правому.


Вариант 1. Числа-как-текст в искомых значениях


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


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

- умножения или деления на 1

- прибавления или вычитания 0

- двойного знака минус перед артикулом (равносильно двойному умножению на -1)


Значение артикула от выполнения такой безобидной математической операции никак не изменится, но сам факт её выполнения заставит Excel воспринимать артикул именно как число. А значит и ВПР найдет текстовые значения без проблем:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Вариант 2. Числа-как-текст в таблице, где ищем


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

Самым простым и компактным вариантом будет приклеивание к артикулу пустой строки:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

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


Вариант 3. Пропадание начальных нулей


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

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Вариант 4. Числа-как-текст вперемешку с числами в обеих таблицах


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

Звучит страшно, но решается легко - нужно просто скомбинировать первый и второй способы, вложив их в функцию ЕСЛИОШИБКА (IFERROR). Эта функция прокачает обе версии ВПР - "текстовую" и "числовую" - и выдаст ту, которая не приводит к ошибке #Н/Д:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Вот и всё - и никаких больше ошибок :)

Источник

Показать полностью 4
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

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

Взято отсюда

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

Надстройка для MS Excel (часть 2)

Всем привет!

В прошлом посте я представил на суд общественности свой open-source проект - надстройку для MS Excel, с набором полезных функций. Пост был встречен очень тепло, многие пикабушники оставили пожелания о добавлении новых функций, а несколько человек написали мне с предложениями подключиться к разработке. Спасибо @1041618 за редизайн - мы обзавелись новыми иконками в едином стиле и поддержкой серой и тёмной тем, а также учитываем в локализации язык пакета MS Office.

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Исходный код проекта и инструкции по установке и использованию опубликованы на GitHub под лицензией MIT (неограниченное право на использование, копирование, изменение).


В этом посте я хотел бы продолжить описание функций надстройки, уверен - многим они покажутся весьма полезными.

Транслитерация кириллицы в латинские буквы


Конвертирует содержимое текстовых ячеек, содержащих символы кириллицы, в латинские символы (по стандарту ICAO doc 9303), за идею спасибо @negotivko

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Подсветка дублей


Раскрашивает разными цветами группы одинаковых значений:

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

«Размерживание» объединенных ячеек с их заполнением


При разбиении объединенных ячеек стандартной функцией Excel все ячейки области, кроме левой верхней, остаются пустыми. Но теперь можно заполнить их значением исходной ячейки:
Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Экспорт таблицы в markdown


Markdown - это удобный язык разметки, используемый при форматировании текстов во многих системах: wiki (Confluence), GitHub, Gitlab, Reddit, Stack Exchange, OpenStreetMap и множество других. Функция копирования таблицы в markdown помещает в буфер обмена отформатированную таблицу с заголовками, и Вы легко можете вставить ее в свой документ:

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Проверка значений в выделенных ячейках (числа, даты, ИНН ЮЛ/ФЛ и т.д.)


Можно проверить, например, список ИНН организаций на корректность (по контрольным цифрам в номере):

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Кроме того, есть две функции по работе с XML-файлами. Они не относятся напрямую к функциональности Excel, но иногда в них возникает потребность:

Сформировать пример XML файла на основе XSD-схемы


Если у вас есть файл XSD, который содержит xml-схему, Вы можете сформировать образец XML-файла на основе этой схемы:

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

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


Проверить XML по XSD-схеме


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

Установка надстройки


Для установки надстройки выберите последнюю по времени успешную сборку пайплайна NavfertyExcelAddIn - Publish и скачайте опубликованные файлы:

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

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


Для установки надстройки нужно запустить файл .vsto


Хотел бы предупредить об одном ограничении: при вызове функций надстройки (как COM-надстроек, так и VBA) в MS Excel очищается стек последних действий пользователя для отмены (Undo). Я работаю над тем, чтобы обойти это ограничение хотя бы для возможности отмены выполнения самой функции, но пока что будьте внимательны при запуске функций - отменить действие можно будет, только закрыв книгу без сохранения.


Более подробная инструкция по установке, а также инструкции по использованию и исходный код - на странице проекта в гитхабе:

https://github.com/navferty/NavfertyExcelAddIn


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


Если Вы .NET-разработчик и хотите присоединиться к работе над проектом, пишите мне на почту (указана в профиле гитхаба), заводите issue и пулл-реквесты.

Если Вы только изучаете платформу .NET, и хотите поучаствовать - не стесняйтесь! Читайте исходный код, задавайте вопросы, если что-то непонятно - буду рад объяснить и поделиться знаниями =)

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

Надстройка для MS Excel

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

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Исходный код проекта и инструкции по установке и использованию опубликованы на GitHub под лицензией MIT (неограниченное право на использование, копирование, изменение).


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

Ниже приведу краткое описание некоторых функций.

Конвертация чисел, форматированных как текст


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


Ниже пример преобразования чисел при помощи надстройки. Столбец B для примера заполнен формулой "=A2+1", которая демонстрирует, является ли значение слева числом:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Переключение регистра текста


В MS Word есть удобная функция, доступная по Shift+F3, которая переключает регистр выделенного текста (в последовательности "Sentence case" -> "lowercase" -> "UPPERCASE"). Иногда такой функции не хватает и в Excel, но надстройка восполняет этот пробел:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Очистка текста от пробельных символов


Позволяет очистить текст от пробелов в начале и конце значения, а также от повторяющихся пробелов и переносов строки в середине текста:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Интерактивный поиск ячеек, в которых произошла ошибка вычисления


Показывает список всех ячеек с ошибкой (например, "#Н/Д"), и позволяет быстро перемещаться к выбранной ячейке:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

И другие функции:


- Подсветка дублей (разными цветами группы одинаковых значений)

- «Размерживание» объединенных ячеек с их заполнением

- Снятие пароля с защищённой книги и листов

- Экспорт таблицы в markdown

- Проверка значений в выделенном диапазоне ячеек (числа, даты, корректный ИНН ЮЛ/ФЛ и т.д.)


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


- Сформировать пример XML файла на основе XSD-схемы

- Проверить XML по XSD-схеме


Установка надстройки


Для автоматической сборки установочных файлов настроена сборка в Azure.

Выберите последнюю по времени успешную сборку пайплайна NavfertyExcelAddIn - Publish и скачайте опубликованные файлы:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Важный момент: при установке надстройки Excel "запоминает" путь к папке, из которой она была установлена, и в будущем установка обновлений будет возможна только из этой папки, в противном случае нужно будет воспользоваться "установкой и удалением программ" через Панель управления Windows.


Для установки надстройки нужно запустить файл .vsto. Разумеется, установка возможно только при наличии установленного MS Excel =)


Если всё сделано правильно, то Вы увидите новую вкладку при следующем запуске Excel:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Если Вы .NET-разработчик и хотите присоединиться к работе над проектом, пишите мне на почту (указана в профиле гитхаба), заводите issue и пулл-реквесты.

Если Вы только изучаете платформу .NET, и хотите поучаствовать - не стесняйтесь! Читайте исходный код, задавайте вопросы, если что-то непонятно - буду рад объяснить и поделиться знаниями =)

Еще раз ссылка на проект (там же инструкции по использованию и установке):

https://github.com/navferty/NavfertyExcelAddIn

Показать полностью 6
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

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
619

Сравнение скорости работы функции ВПР

Сравнение проведено Николаем Павловым на таблице в 500.000 и 600 строк. Тестируемые функции:

1. ВПР

2. ВПР с выделением столбцов целиком

3. ИНДЕКС и ПОИСКПОЗ

4. СУММЕСЛИ

5. СУММПРОИЗВ

6. ПРОСМОТР

7. Новая функция ПРОСМОТРХ

8. Запрос Power Query

- Итоговая таблица и выводы

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Это книга Excel с одним листом, где расположены две таблицы: отгрузки (500 000 строк) и прайс-лист (600 строк).

Задача - подставить цены из прайс-листа в таблицу отгрузок. Для каждого способа будем вводить формулу в ячейку С2 и копировать вниз на весь столбец, замеряя время, которое потребуется Excel, чтобы просчитать весь столбец из полумиллиона ячеек. Полученные значения, безусловно, зависят от множества факторов (поколение процессора, объем оперативной памяти, текущая загрузка системы, версия Office и т.д.), но нам важны не конкретные цифры, а, скорее, их сравнение друг с другом. Важно понимать прожорливость каждого способа и их ограничения.


Способ 1. ВПР

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Здесь участвуют следующие аргументы:

B2 - искомое значение, т.е. название товара, который мы хотим найти в прайс-листе

$G$2:$H$600 - закреплённая знаками доллара (чтобы не сползала при копировании формулы вниз) абсолютная ссылка на прайс

2 - номер столбца в прайс-листе, откуда мы хотим взять цену

0 или ЛОЖЬ - переключение в режим поиска точного соответствия, когда любое некорректное название товара (например, ФОНЕРА) в столбце "B" в таблице отгрузок приведёт к появлению ошибки #Н/Д как результата работы функции.

Время вычисления = 4,3 сек.

Способ 2. ВПР с выделением столбцов целиком

Многие пользователи, применяя ВПР, во втором аргументе этой функции, где нужно задать поисковую таблицу (прайс), выделяют не ограниченный диапазон ($G$2:$H$600), а сразу столбцы G:H целиком. Это проще, быстрее, позволяет не думать про F4 и то, что завтра прайс-лист может быть на несколько строк больше. Формула в этом случае выглядит тоже компактнее:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

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

Время вычисления = 14,5 сек.

Способ 3. ИНДЕКС и ПОИСКПОЗ

Следующей после ВПР ступенью эволюции для многих пользователей Excel обычно является переход на использование связки функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). Выглядит эта формула так:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Здесь:

Функция ИНДЕКС извлекает из заданного в первом аргументе диапазона (столбца $H$2:$H$600 с ценами в прайс-листе) содержимое ячейки с заданным номером. А номер этот, в свою очередь, определяется функцией ПОИСКПОЗ, у которой три аргумента:

- Что нужно найти - название товара из B2

- Где мы это ищем - столбец с названиями товаров в прайсе ($G$2:$G$600)

- Режим поиска: 0 - точный, 1 или -1 - приблизительный с округлением в меньшую или большую сторону, соответственно.


Формула выходит чуть сложнее, но, при этом имеет несколько ощутимых преимуществ перед классической ВПР, а именно:

- Не нужно отсчитывать номер столбца (как в третьем аргументе ВПР).

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

По скорости, однако же, этот способ проигрывает ВПР почти в два раза:

Время вычисления = 7,8 сек.

Если же, вдобавок, полениться и выделять не ограниченные диапазоны, а столбцы целиком:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

... то результат получается совсем печальный:

Время вычисления = 28,5 сек.

Способ 4. СУММЕСЛИ

Если нужно найти не текстовые, а именно числовые данные (как в нашем случае - цену), то вместо ВПР вполне можно использовать функцию СУММЕСЛИ (SUMIF). Изначально она задумывалась как инструмент для выборочного суммирования данных по условию (найди и сложи мне все продажи кабелей, например), но можно заставить её искать нужный нам товар и в прайс-листе. Если грузы в нём не повторяются, то суммировать будет не с чем и эта функция просто выведет искомое значение:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Здесь:

- Первый аргумент СУММЕСЛИ - это диапазон проверяемых ячеек, т.е. названия товаров в прайсе ($G$2:$G$600).

- Второй аргумент (B2) - что мы ищем.

- Третий аргумент - диапазон ячеек с ценами $H$2:$H$600, числа из которых мы хотим просуммировать, если в соседних ячейках проверяемого диапазона есть искомое значение.


Очевидным минусом такого подхода является то, что он работает только с числами. Также этот способ не удобен, если прайс-лист находится в отдельном файле - придется всё время держать его открытым, т.к. функция СУММЕСЛИ не умеет брать данные из закрытых книг, в отличие от ВПР, для которой это не проблема.


В плюсы же можно записать удобство при поиске сразу по нескольким столбцам - для этого идеально подходит более продвинутая версия этой функции - СУММЕСЛИМН (SUMIFS). Скорость вычислений же, при этом, весьма посредственная:

Время вычисления = 12,8 сек.

При выделении столбцов целиком, т.е. использовании формулы вида =СУММЕСЛИ(G:G; B2; H:H) всё ещё хуже:

Время вычисления = 41,7 сек.

Способ 5. СУММПРОИЗВ

Этот подход сейчас встречается не часто, но всё ещё достаточно регулярно. Обычно так любят извращаться пользователи старой школы, ещё хорошо помнящие те времена, когда в Excel было всего 255 столбцов и 56 цветов :)


Суть этого метода заключается в использовании функции СУММПРОИЗВ (SUMPRODUCT), изначально предназначенной для поэлементного перемножения нескольких диапазонов с последующим суммированием полученных произведений. В нашем случае, вместо одного из массивов будет выступать условие, а вторым будут цены:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Выражение ($G$2:$G$600=B2), по сути, проверяет каждое название груза в прайс-листе на предмет соответствия искомому значению (ФАНЕРА ПР). Результатом каждого сравнения будет логическое значение ИСТИНА (TRUE) или ЛОЖЬ (FALSE), что в Excel интерпретируется как 1 и 0, соответственно. Последующее умножение этих нулей и единиц на цены оставит в живых цену только того товара, который нам, в данном случае, и нужен.


Эта формула является, по сути, формулой массива, но не требует нажатия обычного для них сочетания клавиш Ctrl+Shift+Enter, т.к. функция СУММПРОИЗВ поддерживает массивы уже сама по себе. Возможно, по этой же причине (формулы массива всегда медленнее, чем обычные) такой скорость пересчёта такой формулы - не очень:

Время вычисления = 11,8 сек.

К плюсам же такого подхода можно отнести:

- Совместимость с любыми, самыми древними версиями Excel.

- Возможность задавать сложные условия (и несколько)

- Способность этой формулы работать с данными из закрытых файлов, если добавить перед ней двойное бинарное отрицание (два подряд знака "минус"). СУММЕСЛИМН таким похвастаться не может.


Способ 6. ПРОСМОТР

Ещё один относительно экзотический способ поиска и подстановки данных, наравне с ВПР - это использование функции ПРОСМОТР (LOOKUP). Только не перепутайте её с новой функцией ПРОСМОТРХ (XLOOKUP) - про неё мы поговорим дальше особо. Функция ПРОСМОТР существовала в Excel начиная с самых ранних версий и тоже вполне может решить нашу задачу:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Здесь:

- B2 - название груза, которое мы ищем

- $G$2:$G$600 - одномерный диапазон-вектор (столбец или строка), где мы ищем совпадение

- $H$2:$H$600 - такого же размера диапазон, откуда нужно вернуть найденный результат (цену)


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

- Эта функция требует обязательной сортировки прайс-листа по возрастанию (алфавиту) и без этого не работает.

- Если в таблице отгрузок искомое значение будет написано с опечаткой (например, АГЕДОЛ вместо АГИДОЛ), то функция ПРОСМОТР выдаст не ошибку #Н/Д, а цену для ближайшего предыдущего товара:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

При работе с неидеальными данными в реальном мире это гарантированно создаст проблемы, как вы понимаете.

Скорость же вычислений у функции ПРОСМОТР (LOOKUP) весьма приличная:

Время вычисления = 7,6 сек.

Способ 7. Новая функция ПРОСМОТРХ

Эта функция пришла с одним из недавних обновлений пока только пользователям Office 365 и пока отсутствует во всех остальных версиях (Excel 2010, 2013, 2016, 2019). По сравнению с классической ВПР у этой функции есть масса преимуществ (упрощенный синтаксис, возможность искать не только сверху-вниз, возможность сразу задать значение вместо #Н/Д и т.д.) Формула для решения нашей задачи будет выглядеть в этом случае так:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Если не брать в расчёт необязательные 4,5,6 аргументы, то синтаксис этой функции полностью совпадает с её предшественником - функцией ПРОСМОТР (LOOKUP). Скорость вычислений при тестировании на наши 500000 строк тоже оказалась аналогичной:

Время вычисления = 7,6 сек.

Почти в два раза медленнее, чем у ВПР, вместо которой Microsoft предлагает теперь использовать ПРОСМОТРХ. Жаль.

И, опять же, если полениться и выделить диапазоны в прайс-листе целыми столбцами:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

... то скорость падает до совершенно неприличных уже значений:

Время вычисления = 28,3 сек.

А если на динамических массивах?

Прошлогоднее (осень 2019) обновление вычислительного движка Microsoft Excel добавило ему поддержку динамических массивов (Dynamic Arrays). Это принципиально новый подход к работе с данными, который можно использовать почти с любыми классическими функциями Excel. На примере ВПР это будет выглядеть так:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Разница с классическим вариантом в том, что первым аргументом ВПР здесь выступает не одно искомое значение (а формулу потом нужно копировать вниз на остальные строки), а сразу весь массив из полумиллиона грузов B2:B500000, цены для которых мы хотим найти. Формула при этом сама распространяется вниз, занимая требуемое количество ячеек.


Скорость пересчета в таком варианте меня, откровенно говоря, ошеломила - пауза между нажатием на Enter после ввода формулы и получением результатов почти отсутствовала.

Время вычисления = 1 сек.

Что интересно, и новая ПРОСМОТРХ, и старая ПРОСМОТР, и связка ИНДЕКС+ПОИСКПОЗ в таком режиме тоже были очень быстрыми - время вычислений не больше 1 секунды! Фантастика.


А вот олдскульные подходы на основе СУММПРОИЗВ и СУММЕСЛИ(МН) с динамическими массивами работать отказались :(


Что с умными таблицами?

Обрадовавшись фантастическим результатам, полученным на динамических массивах, я решил вдогон попробовать протестировать разницу в скорости при работе с обычными и "умными" таблицами. Я имею ввиду те самые "красивые таблицы", в которые вы можете преобразовать ваш диапазон с помощью команды Форматировать как таблицу на вкладке Главная (Home - Format as Table) или с помощью сочетания клавиш Ctrl+T.


Если предварительно превратить наши отгрузки и прайс в "умные" (по умолчанию они получат имена Таблица1 и Таблица2, соответственно), то формула с той же ВПР будет выглядеть как:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Здесь:

[@Груз] - ссылка на ячейку B2, означающая, в данном случае, что нужно взять значение из той же строки из столбца Груз текущей умной таблицы.

Таблица2 - ссылка на прайс-лист


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


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

Время вычисления = 1 сек.

У меня есть подозрение, что дело тут не в самих "умных" таблицах, а всё в том же обновлении вычислительного движка, т.к. на старых версиях Excel такого прироста в скорости на умных таблицах я не помню.


Бонус. Запрос Power Query

Замерять, так замерять! Давайте, для полноты картины, сравним наши перечисленные способы еще и с запросом Power Query, который тоже может решить нашу задачу. Кто-то скажет, что некорректно сравнивать пересчёт формул с механизмом обновления запроса, но мне, откровенно говоря, просто самому было интересно - кто быстрее?

Итак:

1. Превращаем обе наши таблицы в "умные" с помощью команды Форматировать как таблицу на вкладке Главная (Home - Format as Table) или с помощью сочетания клавиш Ctrl+T.

2. По очереди загружаем таблицы в Power Query с помощью команды Данные - Из таблицы / диапазона (Data - From Table/Range).

3. После загрузки в Power Query возвращаемся обратно в Excel, оставляя загруженные данные как подключение. Для этого в окне Power Query выбираем Главная - Закрыть и загрузить - Закрыть и загрузить в... - Только создать подключение (Home - Close&Load - Close&Load to... - Only create connection).

4. После того, как обе исходные таблицы будут загружены как подключения, создадим ещё один, третий запрос, который будет объединять их между собой, подставляя цены из прайса в отгрузки. Для этого на вкладке Данные выберем Получить данные / Создать запрос - Объединить запросы - Объединить (Get Data / New Query - Merge queries - Merge):

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

5. В открывшемся окне выберем исходные таблицы в выпадающих списках и выделим столбцы, по которым произойдет связывание:

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

После нажатия на ОК мы вернемся в окно Power Query, где увидим нашу таблицу отгрузок с добавленным к ней столбцом, где в каждой ячейке будет лежать фрагмент прайс-листа, соответствующий этому грузу. Развернем вложенные таблицы с помощью кнопки с двойными стрелками в шапке столбца, выбрав нужные нам данные (цены):

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

7. Останется выгрузить готовую таблицу обратно на лист с помощью уже знакомой команды Главная - Закрыть и загрузить (Home - Close&Load).


В отличие от формул, запросы Power Query не обновляются автоматически "на лету", а требуют щелчка правой кнопкой мыши по таблице (или запросу в правой панели) и выбору команды Обновить (Refresh). Также можно воспользоваться командой Обновить все (Refresh All) на вкладке Данные (Data).

Время обновления = 8,2 сек.

Итоговая таблица и выводы

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

Сравнение скорости работы функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Само-собой, у каждого из нас свои предпочтения, задачи и тараканы, но для себя я сформулировал выводы после этого тестирования так:

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

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

- Экзотические способы из прошлого типа СУММПРОИЗВ и СУММЕСЛИ - в топку. Они работают очень медленно и, вдобавок, не поддерживают динамические массивы.

- Динамические массивы и умные таблицы - это будущее.

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

Ад Условного Форматирования в Excel

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


Этот пост будет полезен для тех, кто использует условное форматирование и сталкивался с проблемой, когда удалив/переместив/добавив лишь одну строку сбивались правила УФ и появлялся ад. Для примера рассмотрим простую таблицу продаж:

Ад Условного Форматирования в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

Для наглядности к таблице добавлены три правила условного форматирования:

Ад Условного Форматирования в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

Первое правило делает синие гистограммы на столбце с суммами сделок.

Второе - подсвечивает желтым ячейки с именами менеджеров, которые не выполнили план, т.е. сумма их сделки меньше, чем зелёная ячейка H2.

Третье - делает нижнюю границу всей строки красной, если день меняется на следующий, т.е. дата в текущей строке не равна дате в следующей.


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

Ад Условного Форматирования в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

Красная линия между 2 и 3-м марта почему-то исчезла, а наше правило условного форматирования для разделения дат развалилось на два, причем одно из них с ошибкой #ССЫЛКА (т.е. не работает), а другое применяется к двум не смежным диапазонам A2:E8 и A10:E29 (не ко всей таблице!).

Шикарно, правда?!


Теперь представим, что Кирилл Краснов повторил свою сделку в Тольятти с магазином "Лента" (строка 25) и вам нужно внести эти данные в таблицу.

Как вы поступите?

Скорее всего, как любой нормальный человек, вы скопируете 25-ю строчку и вставите её в конец таблицы, верно?

Ад Условного Форматирования в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

Ага, и получите в наследство вот такой бардак в правилах условного форматирования:

Ад Условного Форматирования в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

Excel зачем-то продублировал те же правила для добавленной строки вместо того, чтобы просто растянуть диапазон в поле Применяется к (Applied to).


Ну, и на десерт вставим пустую строку в середину таблицы, между 4 и 5-й строчками:

Ад Условного Форматирования в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

В списке правил условного форматирования это приведёт к появлению еще одного дубликата и раздроблению диапазона уже существующего 5-го правила на кучу фрагментов:

Ад Условного Форматирования в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

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


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


Как же всё исправить?

Способ 1. Вручную


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


Для этого делаем следующее:

1. Выделяем в нашей таблице все строки кроме первой.

2. Удаляем все правила условного форматирования с выделенных ячеек через Главная - Условное форматирование - Удалить правила - Удалить правила из выделенных ячеек (Home - Conditional formatting - Clear rules - Clear rules from selected cells).

3. Выделяем первую строку, жмём кнопку-кисточку Формат по образцу на Главной (Home - Format Painter) и выделяем все остальные строки, копируя на них формат с первой.


Способ 2. Макросом


Если есть ощущение, что подобную процедуру вам придётся проделывать ещё не раз, то имеет смысл автоматизировать весь процесс с помощью макроса. Для этого:

1. Жмём сочетание клавиш Alt+F11 или на вкладке Разработчик кнопку Visual Basic (Developer - Visual Basic).

2. В открывшемся окне редактора макросов добавляем в нашу книгу новый модуль через меню Insert - Module.

3. Вставляем в созданный пустой модуль наш макрос:

Sub Fix_СF_Hell()

'создаем ссылки на диапазоны

Set rngAll = Selection

Set rngRow1 = Selection.Rows(1)

Set rngRow2 = Selection.Rows(2)

Set rngRowLast = Selection.Rows(rngAll.Rows.Count)

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

Range(rngRow2, rngRowLast).FormatConditions.Delete

'копируем форматы с первой строки на все остальные

rngRow1.Copy

Range(rngRow1, rngRowLast).PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

End Sub

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

И всё будет хорошо :)


P.S.

Не забудьте сохранить файл в формате с поддержкой макросов (xlsm).

Если нужно применять этот макрос в других файлах, то имеет смысл поместить его в Личную Книгу Макросов (Personal Macro Workbook).


Здоровья вам и вашим близким) материал взят отсюда

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

Пишем парсер зарплат headhunter в Excel

Задача: получить зарплаты некой профессии, например, "Программист JavaScript"

Пишем парсер зарплат headhunter в Excel Excel, Макрос, Парсер, Видео, Длиннопост

Решение:

Пишем макрос в Excel

Sub JS_HH()

- начало макроса

Dim URL As String

- объявляем строковую переменную URL

URL = "https://hh.ru/search/vacancy?area=1&area=2019&clusters=true&employment=full&enable_snippets=true&items_on_page=100&no_magic=true&schedule=fullDay&search_field=name&text=Программист JavaScript&only_with_salary=true&from=cluster_compensation&showClusters=true"

- URL с нужным запросом и параметрами


Dim IE As Object
Dim ieDoc As Object

- объявляем объекты IE (браузер) и ieDoc (код страницы)

Set IE = CreateObject("InternetExplorer.Application")

- запускаем InternerExpolorer в фоновом режиме

IE.navigate URL

- переходим в браузере по нужному нам URL

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

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

Set ieDoc = IE.Document

- присваиваем объекту ieDoc содержание страницы IE

i = 1

- присваиваем счётчику значение 1

Set detail_elements = IE.Document.getElementsByTagName("span")
- получаем массив html-тегов "span"

For Each detail_element In detail_elements

If detail_element.getAttribute("data-qa") = "vacancy-serp__vacancy-compensation" Then

- ищем в массиве элементов "span" теги со значением атрибута "data-qa" = "vacancy-serp__vacancy-compensation"

Пишем парсер зарплат headhunter в Excel Excel, Макрос, Парсер, Видео, Длиннопост
Sheets("JS HH").Cells(i, 1) = detail_element.innerText
- заполняем ячейки на листе JS HH текстовым значением тега "span", подходящего по условию, начиная с ячейки A1
i = i + 1

-прибавляем счётчик

End If

- конец условия

Next detail_element

- конец цикла ForEach

IE.Quit

- закрываем браузер IE

DoEvents

- ожидание выполнения прошлого действия

End Sub

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

Готово!

Обрабатываем результаты:

Пишем парсер зарплат headhunter в Excel Excel, Макрос, Парсер, Видео, Длиннопост

использую несколько вложенных ЕСЛИ, ПОИСК, ЗНАЧЕН и ПСТР)

=ЕСЛИ(ЕОШИБКА(ПОИСК("от";'JS HH'!A1));ЕСЛИ(ЕОШИБКА(ПОИСК("-";A1));;ЕСЛИ(ЕОШИБКА(ПОИСК("руб";A1));ЕСЛИ(ЕОШИБКА(ПОИСК("USD";A1));ЗНАЧЕН(ПСТР(A1;1;5))*ЗНАЧЕН(ПСТР('курс доллара'!$A$80;1;2));ЗНАЧЕН(ПСТР(A1;1;5))*ЗНАЧЕН(ПСТР('курс доллара'!$A$77;1;2)));ЕСЛИ(ЕОШИБКА(ЗНАЧЕН(ПСТР(A1;1;7)));ЗНАЧЕН(ПСТР(A1;1;6));ЗНАЧЕН(ПСТР(A1;1;7)))));ЕСЛИ(ЕОШИБКА(ПОИСК("руб";A1));ЕСЛИ(ЕОШИБКА(ПОИСК("USD";A1));ЗНАЧЕН(ПСТР(A1;4;5))*ЗНАЧЕН(ПСТР('курс доллара'!$A$80;1;2));ЗНАЧЕН(ПСТР(A1;4;5))*ЗНАЧЕН(ПСТР('курс доллара'!$A$77;1;2)));ЕСЛИ(ЕОШИБКА(ЗНАЧЕН(ПСТР(A1;4;7)));ЗНАЧЕН(ПСТР(A1;4;6));ЗНАЧЕН(ПСТР(A1;4;7)))))

Источник

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

Приблизительный поиск с помощью функции ВПР

Продолжаем раскрывать тему ВПР. В этот раз мы рассмотрим, как рассчитать доплаты к окладам сотрудников за выслугу лет или добавочные бонусы к зарплате.

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Если сотрудник проработал в компании меньше года – он не получает ничего. Если проработал от года до двух – получает 10% доплаты. Если от двух до трёх – 15%. Если от трёх до пяти – 25% и т.д. Максимальный бонус в 100% полагается тем, кто работает в компании больше 10 лет.


Выделяем первую ячейку (G2), куда будем вводить функцию ВПР, на вкладке «Формулы» нажимаем «Вставить функцию». В категории «Ссылки и массивы» (Lookup and Reference) находим ВПР (VLOOKUP) и жмём ОК. В появившемся окне вводим аргументы для функции:

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Искомое значение – стажа сотрудника, для которого мы определяем бонус.

Таблица – сама таблица бонусов. Не забываем нажать клавишу F4, чтобы сделать ссылку абсолютной.

Номер столбца – порядковый номер столбца в таблице бонусов, откуда мы берем размер доплаты (у нас всего два столбца и номер, очевидно, 2).

Интервальный просмотр – этот аргумент нужно задать равным 1, чтобы Excel производил поиск ближайшего наименьшего числа в первой колонке таблицы. Для точного поиска используется значение 0.


Вот ещё примеры использования функции ВПР:

Найти нужную скидку в таблице скидок, если размер скидки зависит от количества купленного товара или его стоимости (от 1 до 5 шт. – скидки нет, от 6 до 10 шт. – скидка уже 3%, свыше 11 шт. – скидка 5% и т.д.):

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Определить цену билета для пригородной зоны, если известно, до какой станции (на какое расстояние) едет пассажир:

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Определить, на какой стадии выполнения проекта мы на данный момент находимся:

Приблизительный поиск с помощью функции ВПР Excel, Впр, Таблица, Полезное, На заметку, Длиннопост
Показать полностью 4
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
168

Ответ на пост «Функция ВПР в Excel» 

Отличный гайд, но есть неточности.

- ИСТИНА - поиск приблизительного соответствия.

Это, строго говоря, неправда. Хоть то же самое написано на сайте office.microsoft.com, но это всё равно неправда.


Значение "ИСТИНА" параметра "Тип поиска" означает, что ВПР выполнит бинарный поиск и вернёт то, что найдёт. Если массив отсортирован по возрастанию, то это действительно будет ближайшее "снизу" значение (например, для числа 123 это будет число 122, а для текста "абв" это будет "абб", при условии, конечно, что эти значения есть в массиве поиска). Если же массив не отсортирован или отсортирован не по возрастанию - алгоритм бинарного поиска либо вернёт ошибку "#Н/Д", либо всё-таки что-то найдёт. Скорее всего, совсем не то, что вы искали (даже если искомое значение есть в массиве!). Дело в том, что, во-первых, ВПР не проверяет, отсортирован массив или нет, а во-вторых, он не проверят действительно ли найденное алгоритмом бинарного поиска значение совпадает с тем, что искалось.


Функция ВПР выдаёт ошибку #Н/Д если:
...
2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск, не отсортирована по возрастанию наименований.

Это тоже неправда. Как я писал выше, ВПР может что-то найти даже в несортированном массиве.


Зачем вообще нужен алгоритм бинарного поиска в ВПР?


Дело в том, что бинарный поиск работает намного, намного быстрее, чем "обычный" (O(log n) против O(n)). Особенно эта разница будет заметна на больших массивах данных. Но пользоваться им надо с осторожностью. Чтобы отсечь неправильно найденные значения (по причине проблем с сортировкой или из-за отсутствия искомого значения в массиве), можно воспользоваться приёмом под названием "двойной ВПР":


=ЕСЛИ(
ВПР(Искомое_значение; Первый_столбец_таблицы; 1; ИСТИНА) = Искомое_значение;  ВПР(Искомое_значение; Таблица_где_ищем; Номер_столбца_результатов; ИСТИНА);
НД()
)

Т.е. сначала мы проверяем, что ВПР находит то, что нужно, а только затем возвращаем найденное. Скорость работы больше обычного ВПР в 10-100 (sic!) раз. Такой разброс скорее всего связан с тем, насколько хорошо у Excel получается оптимизировать ваш "обычный" поиск.

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
1617

Функция ВПР в Excel

Многим знакома функция ВПР в Excel, которая ищет заданное значение в столбце указанной таблицы, и если находит, то выдаёт значение из требуемого столбца той же строки, где было совпадение.

=ВПР(Искомое_значение; Таблица_где_ищем; Номер_столбца_результатов; Тип_поиска)

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

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


Допустим, у нас имеются две таблицы – Заказы и прайс-лист:

Функция ВПР в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

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


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

- подставить из штатного расписания данные о сотруднике (адрес, оклад, телефон) по его ФИО;

- подставить из каталога продукции подробную информацию о товаре по его артикулу;

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

- и так далее.


Выделяем первую ячейку (D3), куда будем вводить функцию ВПР, на вкладке «Формулы» нажимаем «Вставить функцию». В категории «Ссылки и массивы» (Lookup and Reference) находим ВПР (VLOOKUP) и жмём ОК

Функция ВПР в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

Появится окно ввода аргументов для функции:

Функция ВПР в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

Заполняем поля по очереди:

- Искомое значение – наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа (слово «Вода» из ячейки B3).

- Таблица – таблица, из которой берутся искомые значения, т.е. наш прайс-лист. Чтобы при копировании функции вниз на весь столбец ссылка на прайс не сбилась, ее нужно сделать абсолютной, нажав клавишу F4.

- Номер_столбца – порядковый номер (не буква!) столбца в прайс-листе, из которого берём значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно, нам нужна цена из столбца с номером 2.

- Интервальный_просмотр – ЛОЖЬ (0) или ИСТИНА (1):

- ЛОЖЬ КЛАДИ - поиск точного соответствия. Если товар отсутствует в прайс-листе или написан с ошибкой, то функция выдаст ошибку #Н/Д.

- ИСТИНА - поиск приблизительного соответствия. Функция попытается найти товар с максимально похожим наименованием и выдаст цену для этого товара.

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

После ввода всех аргументов нажимаем ОК и протягиваем введенную функцию на весь столбец.


Функция ВПР выдаёт ошибку #Н/Д если:

1. Включен точный поиск (Интервальный просмотр=0) и искомого наименования нет в Таблице.

2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск, не отсортирована по возрастанию наименований.

3. Форматы ячеек первого столбца и образцовой таблицы отличаются (числовой и текстовый). Можно использовать функции Ч и ТЕКСТ для преобразования форматов данных.

4. Функция не может найти нужного значения, потому что в коде есть пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ для их удаления:

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));$G$3:$H$19;0)

=VLOOKUP(TRIM(CLEAN(B3));$G$3:$H$19;0)

Для подавления сообщения об ошибке #Н/Д в тех случаях, когда функция не может найти точного соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR). В результате любые ошибки, создаваемые ВПР, заменятся на нули или пустые строки:

Функция ВПР в Excel Excel, Таблица, Полезное, На заметку, Длиннопост

Итого получаем, 5 минут на заполнение формулы и куча сэкономленного времени и нервов, в которое имитируем бурную деятельность и читаем Пикабу)) Всем здоровья, берегите себя!

Дополнение к посту: #comment_166519497

Показать полностью 3
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
378

Подсветка дат за 30 и более дней

Продолжение моего поста Таблица контроля сроков с подсветкой , где подробно указано как создать таблицу с подсветкой дат.

Здесь я покажу формулу для выделения дат за 30 и более дней.

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

1. Создаём таблицу в Excel.

2. Создаём текущую обновляемую дату. Наводим курсор на удобное для вас место и вводим формулу: =СЕГОДНЯ()

3. Чтобы подсветить даты за 3 месяца вперёд выделяем всю таблицу Excel (без шапки) и выбираем на вкладке ГлавнаяУсловное форматирование Создать правило. В открывшемся окне задаём последний тип правила: Использовать формулу для определения форматируемых ячеек и вводим в строку следующую формулу: =$H1<СЕГОДНЯ()+120

Не забываем в графе «Формат» выбрать цвет заливки.

4. Аналогичным способом, для подсветки дат за 2 месяца вперёд, в новое правило вводим: =$H1<СЕГОДНЯ()+90

5. Также подсвечиваем даты за 1 месяц вперёд: =$H1<СЕГОДНЯ()+60

6. Даты меньше текущей даты подсветим с помощью формулы: =$H1<$L$2

7. Для пустых ячеек вводим: =ЕПУСТО($H1).

В итоге у вас должно получиться так:

Подсветка дат за 30 и более дней Excel, Таблица, Контроль, Срок, Полезное, Лайфхак

Также для ещё большего удобства можно создать фильтр, где имеется сортировка по цвету.

1402

Небольшой макрос для ворда

Пока проходил производственную практику возникла необходимость отредактировать около 300 документов в одной папке. Это были доп. соглашения к ТД, но не суть.

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


Выглядит это так:

*ниже приложу ссылку для скачивания*

Небольшой макрос для ворда Microsoft Word, Макрос, Vba, Длиннопост, Microsoft

Путь до папки копируется прямо из проводника:

Небольшой макрос для ворда Microsoft Word, Макрос, Vba, Длиннопост, Microsoft

С пунктами "что заменить" и "на что заменить" я думаю все понятно.

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


Вот как выглядит сам код:


Private Sub CommandButton1_Click()
Dim s As String, fldr As String
fldr = TextBox1.Value & "\"
s = Dir(fldr & "*.doc")
Do While s <> ""
With Documents.Open(fldr & s)
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = TextBox2.Value
.Replacement.Text = TextBox3.Value
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
If CheckBox1.Value = True Then Call idle(TextBox4)
ActiveDocument.Save
.Close
End With
s = Dir
Loop
MsgBox ("Замена завершена!")
End Sub

И код таймера задержки:

Public Sub idle(n As Single)
Dim t As Single
t = Timer + n
DoEvents
Do While Timer < t
Loop
End Sub

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


Сделал наглядный пример. Создал несколько документов в папке с текстом:

Небольшой макрос для ворда Microsoft Word, Макрос, Vba, Длиннопост, Microsoft

Заменяем:

Небольшой макрос для ворда Microsoft Word, Макрос, Vba, Длиннопост, Microsoft

После замены:

Небольшой макрос для ворда Microsoft Word, Макрос, Vba, Длиннопост, Microsoft

Документ с макросом вы можете скачать по ссылке: https://yadi.sk/d/58JkrdIXtCTtx

*Работает на версиях MS Office 2010 и 2013. Также необходимо разрешить запуск макросов в настройках безопасности:

Небольшой макрос для ворда Microsoft Word, Макрос, Vba, Длиннопост, Microsoft

Надеюсь, что этот небольшой макрос поможет кому-то сэкономить время и силы при работе с документами :)

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