Excel | Personal experimental site

В защищенном файле Excel пропадают фильтры

26.09.2018 опубликовал Администратор в рубрике

Надоела ситуация, когда при очередном открытии документа Excel постоянно пропадают фильтры с защищенного листа. Естественно их не могут вернуть рядовые пользователи (пароль-то зачем им знать?)

Решил в очередной раз «по-гуглить». И вот что нарыл


http://access-excel.tips/excel-vba-protect-worksheet-with-password
https://www.planetaexcel.ru/techniques/5/64/
http://msoffice-nm.ru/faq/macros/filters.htm

Свел эти данные и получилось вот такое:

If Worksheets(3).AutoFilterMode = True Then
MsgBox «Автофильтр установлен»
Else
Sheets(«Лист1″).Unprotect Password:=»pass»
Sheets(«Лист1»).Range(«A1»).AutoFilter

Sheets(«Лист1″).Protect Password:=»pass», AllowFiltering:=True
End If

Вот как-то так. В общем-то от первой проверки с выводом сообщения можно и отказаться.

UPD:

Решено добавить опцию AllowFormattingCells:=True, т.к. понадобилось разрешение на условное форматирование в рамках поиска повторных записей (оно не всегда работает корректно)

UPD 2:

Дополню еще одним параметром «AllowFormatingRows» — разрешение на форматирование строк. Его нужно выставить ранее параметра форматирования ячеек, иначе макрос останавливает свою работу и не устанавливает защиту на лист. Кто знает в чем их логика?

Читать далее

Проверка введенных данных и контроль по дате создания

31.08.2018 опубликовал Администратор в рубрике

Понадобилось упростить задачу по работе с отчетами определенного вида.

На момент написания заметки сотрудники заносят каждый месяц в новый документ Excel данные по результатам обращений.

Записей очень много, при этом с 1 по 10е числа должны редактироваться 2 файла — текущий месяц и предыдуший. Также требуется отслеживать уникальность обращений (их номера, кот. берутся из другой системы)

Аналитика работает через PowerQuery и иногда им требуется найти информацию за несколько месяцев назад.

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

Давно мечтал их перевести на списки Sharepoint.

В связи с недостатком знаний в прошлом, не умел сотворить проверку столбцов, да и ресурсы были весьма ограничены на SP2010 + необходимость в надстройке Access — это геморой тот еще на 1500 машин.

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

И так, что было сделано:.

  1. Создан список площадок с районами (последние могут повторяться)
  2. Список услуг с исполнителем и номером услуги
  3. Рабочий список, где и вносят сотрудники информацию.

Загвоздка еще заключалась в том, что полное наименование услуги может достигать почти неограниченного количества символов (более 255). Т.е. приходится использовать мультистроку.

Самая главная в моем случае формула выглядит вот так:

=ИЛИ((И(((ЦЕЛОЕ(ЕСЛИ(Создано=»»;»Без даты»;ТЕКСТ(Создано;»MM»))))=(ЦЕЛОЕ(ЕСЛИ(date_otkaz=»»;»Без даты»;ТЕКСТ(date_otkaz;»MM»)))));(И((date_otkaz>=date_event);(date_otkaz<=Создано)))));(И(((ЦЕЛОЕ(ЕСЛИ(Создано=»»;»Без даты»;ТЕКСТ(Создано;»MM»)))-1)=(ЦЕЛОЕ(ЕСЛИ(date_otkaz=»»;»Без даты»;ТЕКСТ(date_otkaz;»MM»)))));(И((ДЕНЬ(Создано)>1);(ДЕНЬ(Создано)<10))))))

Данная проверка описывает создание в пределах предыдущего и текущего месяца. Изменение не проверялось.

UPD:

Решил реализовать подобный список на боевом разделе. Накидал поля практически один в один. Исключение один столбец, вместо выбора сделал подстановку. Но по нему проверка не производится (не касается). Но что я увидел? При добавлении элемента формула проверки работает и работает корректно, а при попытке изменить элемент выводит «Формула проверки вернула ошибку». Отмечу, что в тестовом разделе изменения происходят как и полагается, что соответствует поставленной задаче. Кто-нибудь знает в чем проблема?

UPD2:

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

Читать далее

Конвертер дат

22.01.2018 опубликовал Администратор в рубрике

Т.к. Sharepoint работает с датами как и Excel в виде чисел, пришлось искать в сети конвертер дат.

Делал проверку столбцов, вводя формулу =[Время начала]>43132, что значит «Дата начала не может быть назначена ранее 1 февраля 2018»

Т.о. был найден, на мой взгляд, достаточно удобный конвертер дат online

http://www.direct-time.ru/%D0%9A%D0%BE%D0%BD%D0%B2%D0%B5%D1%80%D1%82%D0%B5%D1%80_%D0%B4%D0%B0%D1%82/#

Читать далее

Избавляемся от конфликта имен _FilterDataBase

14.09.2017 опубликовал Администратор в рубрике

В ходе чтения данных из MS Excel старше версии 2010 с помощью PowerShell (версия неважна) появляется окно «Конфликт имен» ссылаясь на совпадение с именем _FilterDataBase.
Прочитав порядочное количество форумов, статей и прочего хлама в интернете, пришел к выводу, что виновата в этом автофильтрация. Т.о. было найдено одно из решений.
В макросах при открытии и закрытии документа прописал следующее:

 

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

Читать далее