Полезные приемы при работе с Microsoft Excel для пользователя Dynamics NAV

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

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

=ДЛСТР(A3)

сообщает количество символов в строке. Например, удобно проверять подходят ли предоставленные данные по формату (не слишком ли короткие или не слишком ли длинные).

=ЛЕВСИМВ(B2;5)

или

=ПРАВСИМВ(E5;7)

Отрезает слева или справа нужное количество символов. Например, можно оставить последние 7.

=СЦЕПИТЬ(”текст1″;А1; “текст2″)

Можно склеить несколько текстовых строк. Я этим пользую когда нужно сравнить две таблицы у которых составные ключи (например Документ Но и Строка Но).
Или можно с помощью данной функции добавить префиксы. Например «добить нулями» до нужной длины.
Пример. Нужно чтобы загружаемый код был длинной 11 символов, а на входе могут быть строки разной длины. Недостающую длину нужно заполнить ведущими нулями. Для решения задачи можно использовать такую формулу:
=(ПРАВСИМВ(СЦЕПИТЬ(”00000000000″;G2);11))

=ЗНАЧЕН(D7)

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

=ТЕКСТ(E2;”0,0″)

или

=ТЕКСТ(D3;0)

Преобразует текст в число (с одним знаком после запятой) или только целую часть.

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

=ОКРУГЛ(A3;2)

Округляет число до двух знаков после запятой.

=СУММ(K:K)

или

=СУММ(J2:J20)

Сумма по столбцу или сумма чисел в диапазоне.

Фильтрация (особенно автофильтры) и сортировка. Хотел написать сам, но нашел толковые статьи на сайте Microsoft, крайне рекомендую к прочтению:

Сводная таблица.
Прекрасный способ получить агрегированные данные. Частенько пользуюсь, чтобы из операций получить остатки (как рассказывал ранее – заказчик предоставляет операции для загрузки, а для сверки предоставляет остатки).
Про это расскажу на примере. Для примера нам нужны данные.
Можно список операций скопировать через буфер обмена, но для больших объемов рекомендую использовать прямое подключение к SQL Server.

Данные в Microsoft Excel полученные из Dynamics NAV

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

Сводная таблица на базе операций стоимости

В поле Значения также следует «перетащить» поле Cost Amount (Actual). Обратите внимание на формулу, которую Excel решил использовать в поле Значение, там должно быть Сумма, а не Количество.

Формулы в сводной таблице Microsoft Excel

Кстати с помощью сводных таблиц также удобно определять сколько раз используется некое значение. Например для таблицы Value Entry если поле Item No_ указать не только как Название Строк, но и как Значение с формулой Количество, то можно узнать сколько операций есть в таблице по каждому товару.

Сводная таблица для определения количества вхождений

Также может быть интересно указать поле Location Code в качестве названия столбцов – вот и получим альтернативу существующим матричным формам.

Матричная форма с помощью сводной таблицы

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

Сводная таблица на базе внешнего источника данных

Давайте посмотрим на сводную таблицу, созданную ранее. Чего в ней особенно не хватает?
На мой взгляд, в таблице не хватает названия товара.

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

Для этого ее нужно скопировать на тот же или другой лист Microsoft Excel. А затем объединить две таблицы по полю Товар Но. Этому действу очень помогает функция

=ВПР(A1;B:C,2,ЛОЖЬ)

Добавляем Описание в операции стоимости с помощью функции ВПР

В столбце C, я указал формулу, содержащую функцию ВПР. Функция ВПР принимает четыре параметра:

  • Искомое значение – что ищем - в моем примере это ссылка на ячейку с кодом товара в целевой таблице (например, А4);
  • Таблица – где ищем – в моем примере это ссылка на список товаров (например, E:F). Внимание, функция ВПР будет искать значение только по первому столбцу.
  • Номер столбца, из которого возвращать значения (например, я хочу получать значения поля Описания. Поле Описание идет вторым в таблице – E:F, поэтому и указано значение 2).
  • Интервальный просмотр – я всегда указываю ЛОЖЬ. Потому как ИСТИНА возвращает иногда странные значения.

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

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

Все это я помещаю в одну книгу Microsoft Excel. И дальше выполняю нехитрые манипуляции:

  1. Функция ВПР, чтобы найти клиентов, которые не были загружены в Dynamics NAV
  2. Фильтрация – для удобного отображения.

Сверка клиентов в двух базах данных с помощью функции ВПР

Слева (A:B) у меня данные из «Старой системы», справа (F:G) из Dynamics NAV. Даже невооруженным взглядом видно что количество записей различается (это условный пример имеющий очень косвенное отношение к процессу миграции данных).
Нужно проверить какие данные из старой системы не попали в новую. Так как в старой системе данных больше, то и проверять будем их. Добавим столбец C, где укажем формулу

=ВПР(B4;G:G;1;KJ:M).

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

Давайте рассмотрим еще один пример. На этот раз будем сравнивать остатки.

Сверка остатков товаров с помощью функции ВПР

В зеленых выносках я отобразил используемые формулы.
В первую очередь я посчитал общий остаток по старой и новой системе, для этого я использовал функцию СУММ. Если бы остатки сошлись, то на этом сверку можно заканчивать.
В приведенном примере остатки различаются (2062 <> 2022).
Тогда я воспользовался функцией ВПР, чтобы сопоставить остатки из старой системы с остатками в новой. Благодаря этой функции я вижу, что товары, у которых остаток был 0 в старой системе, в новую не перешли. К сожалению, результат #Н/Д не позволяет выполнять математические действия (поэтому в ячейке D19 вместо итоговой суммы отображается значение #Н/Д).
Чтобы решить эту проблему я использую функции

=ЕНД(D4)

и

=ЕСЛИ(выражение;истина;ложь)

Обработка ошибок при работе с функцией ВПР

В ячейке Е используется формула с применением обеих упомянутых выше функций. Читается данная формула следующим образом:
«Если ячейка D4 содержит значение #Н/Д, то следует вернуть значение 0, иначе вернуть значение ячейки D4».
Избавленный от ошибок столбец Е можно использовать для математических вычислений, например для расчета итогов (СУММА) или дельты (арифметический минус).
Полученный результат (столбец F) можно фильтровать, или как в приведенном примере использовать возможности Microsoft Excel по условному форматированию (выделил цветом ячейки, содержащие значения отличные от нуля)

Этой статьей заканчивается цикл посвященный Microsoft Excel. Надеюсь, что уверенное использование Microsoft Office повысит вашу удовлетворенность от использования продуктов Microsoft Dynamics. Кстати это и есть основная идея, заключенная в термин «Платформа Microsoft».

Метки:



Комментариев: 1

  1. Алексей Крюков пишет:

    В дополнение к полезным приёмам хотелось бы заметить, что вместо функции СЦЕПИТЬ удобнее использовать знак &.
    Например, следующие записи эквивалентны:
    =СЦЕПИТЬ(”текст1″;A1;”текст2″)
    =”текст1″&A1&”текст2″

    Я этой функцией довольно часто пользуюсь, когда нужно сделать фильтр для Dynamics NAV из присланного списка документов.

Оставьте свой отзыв!