MS, Libreoffice & Google docs

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

Плюсы Сводных таблиц:

1 Лёгкость создания отчетов по большому объему данных;

2 Простота редактирования и изменения вида;

3 Возможность группировать данные в диапазоны (например, даты объединить в кварталы или месяца, числа в интервалы);

4 Можно добавить поля с расчетами, которых в исходной таблице нет;

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

Всё это делается в пару кликов мыши.

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

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

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

Первое видео из серии Сводные таблицы в Excel, о том, как создать сводную таблицу, изменить вид, как группировать данные, как использовать фильтры в сводных, изменить источник исходных данных таблицы ⬇⬇⬇

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

https://github.com/navferty/NavfertyExcelAddIn

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

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

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

Dim strCCY As String, strRateCCY As String, strRateSource As String

Dim xmlDoc As MSXML2.DOMDocument

Set xmlDoc = New MSXML2.DOMDocument

xmlDoc.async = False

MSXML2 – это и есть упомянутый выше Microsoft XML parser, который нужно направить на сайт Банка России

strRateSource = "http://www.cbr.ru/scripts/XML_daily.asp?date_req="

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

Dim i As Long

i = 1

Dim strDate As String

Do While Not Range("a" & i) = ""

strDate = Format(Range("a" & i), "dd\/mm\/yyyy")

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

If xmlDoc.Load(strRateSource & strDate) <> True Then

MsgBox "Сайт ЦБ сейчас не в духе, попробуйте обратиться к нему позже..."

Exit Sub

End If

Если же загрузка прошла успешно, то начинается магия xPath. Сначала получим дату, к которой на самом деле привязан курс доллара. Она не всегда совпадает с той датой, на которую вы курс запросили. И поместим дату ЦБ в столбец «B»

Range("b" & i) = xmlDoc.selectNodes("//ValCurs")(0).Attributes(0).Text

"//ValCurs" – это и есть выражение XPath, которое может быть очень интересным и витиеватым, и которое позволяет добраться практически до любой точки xml файла. В вышеприведенном примере я взял дату валютирования из тега ValCurs.

А ниже выражение посложнее. С его помощью я нахожу валюту «доллар» среди множества других (у этой валюты ID=R01235) и прошу показать мне только курс этой валюты (там есть и другая информация: буквенный и цифровой коды валюты в соответствии с ISO 4217 и/или ОКВ, номинал, описание, - но нам нужен только курс).

strRateCCY = xmlDoc.selectNodes("//Valute[@ID='R01235']/Value")(0).Text

Далее я привожу текст с курсом к числу с учетом настроек символа разделителя разрядов.

Range("c" & i).Value = CdblLocaleIndependent(strRateCCY)

Функцию CdblLocaleIndependent в этом посте показывать не буду, пока желающих на нее посмотреть не будет достаточно.

Закругляемся с циклом и заканчиваем работу:

i = i + 1

Loop

MsgBox "Курсы сняты с сайта Банка России."

End Sub

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

https://github.com/navferty/NavfertyExcelAddIn