Как в excel написать м2 - Учим Эксель

Вы больше не будете разбирать Excel как ранее

  • Конструктор запросов к неструктурированным данным.png

Согласитесь, было бы хорошо получить инструмент, который воспринимал бы запросы к данным со слабовыраженной и нестабильной структурой в том виде, в котором человек разъяснял бы другому человеку правила по которым он очами находит колонку "стоимость" в УПД. Ну, к примеру:

о стоимости мы буквально знаем, что это число, оно находится в колонке с заглавием содержащим слово "стоимость", и оно размещается меж наименованием и ставкой НДС

Сформулированный "запрос" содержит главные аспекты (тип, заглавие колонки) и "подстраховку" (размещение относительно остальных данных), которые не дозволят избрать что-то избыточное. При всем этом, как вы могли увидеть, таковой "запрос" не привязан к какому-то одному формату, при помощи него можно удачно отыскать стоимость и в УПД и в Затратной и в ТОРГ-12. И уж тем наиболее, там ничего не написано ни про номер колонки в которой стоит стоимость ни про номер строчки с которой начинаются продукты.

Но если так может человек, почему так не может 1С?

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

Практически, таковой "запрос" можно получить в виде фразы ГДЕ языка запросов 1С, построив необходимым (и автоматическим) образом фразу ИЗ. Фраза ВЫБРАТЬ особенного энтузиазма не представляет и будет статичной. Если "перевести" описание колонки Стоимость, данное выше, с людского на язык запросов 1С, получится приблизительно такое:

ГДЕ ТипЗначения(Слово) = Тип(Число) И СловаВыше ПОДОБНО ""%ЦЕНА%"" И СловаСлева ПОДОБНО ""%[А-Я]%"" И СловаСлева.Длина > 3 И ТипЗначения(СловаСправа) = Тип(Число)

Разберемся, в том, что здесь понаписано.

Главные характеристики

Во-1-х нужно осознать, что мы имеем дело с обыденным запросом, ну т.е. с его фразой "ГДЕ". Соответственно, для описания критерий нам доступны все способности языка запросов: сопоставления, равенства, манипуляции периодами, математические выражения, оператор "ПОДОБНО", и все другое, что лишь можно написать в обыкновенном запросе.

Во-2-х, для рассмотрения доступно само поле. О нем доступны последующие сведения:

  • в реквизите Слово.Значение хранится "незапятнанное" значение поля.
  • в реквизите Слово.ОригинальноеЗначение хранится значение поля в том виде, в котором оно было в изначальном экселе, без чистки.
  • в реквизитах Слово.НомерСтроки и Слово.НомерКолонки хранятся координаты позиции слова в начальной таблице.
  • Слово.Длина содержит длину строчки (для не строк — длину строкового представления соответственного значения).
Интересно почитать:  Excel выбор значения из выпадающего списка

Для наглядности и простоты написания, выражения "Слово.Значение" и "Слово" — эквивалентны, т.е. когда вы не уточняете какое имеете ввиду поле у таблицы "Слово" — будет подставлено поле по-умолчанию "Значение".

В-3-х, доступно все свита всякого поля. К примеру, если мы желаем обрисовать некое поле через его предшественника — ставку НДС, мы можем написать "СловаСлева ПОДОБНО "18%". Иной пример — описание поля через заголовок столбца: "СловаСверху ПОДОБНО "%ЦЕНА%". Ну и так дальше, всего может быть 8 вариантов воззвания к окружению, все описаны в справке. Каждое из окружающих слов содержит туже структуру его описания, что и само Слово (Значение, ОригинальноеЗначение, НомерСтроки, НомерКолонки, Длина). И также, при опускании ".Значение" — оно будет поставлено автоматом (т.е. записи "СловаСлева" и "СловаСлева.Значение" эквивалентны).

В-4-х, требуется понять последующую вещь: "Слева" — это не означает "конкретно в примыкающей слева ячейке". Это означает кое-где слева. Таковой подход помогает нам легче обращаться к наименованиям колонок (они же были бы и 3 и 10 строк вспять), также не иметь заморочек при объединениях ячеек. Но, если есть уверенность в конкретной близости каких-либо данных, постоянно можно написать "СловаСлева.НомерКолонки + 1 = Слово.НомерКолонки"

В-5-х, в любом направлении (на лево, ввысь. ) мы можем рассматривать не одно поле. В таком случае мы можем обзывать поля как желаем, основное — бросить префикс из того набора, который у нас есть. К примеру, при поиске количества желаем чтоб оно следовало за "шт" и "796": "СловаСлеваИмяЕдИзм = "ШТ" И СловаСлеваКодЕдИзм = 796". Естественно, если написать еще некое условие для синонима СловаСлеваКодЕдИзм — будет понятно, что это требование к тому же самому полю, а не к третьему.

Углубимся в технику

На техническом уровне, весь размер данных (Excel, табличный документ, Word, не принципиально) помещается в таблицу значений с колонками Значение, ОригинальноеЗначение, НомерСтроки, НомерКолонки, Длина, где любая запись соответствует одной ячейке. Дальше, таблица значений помещается в запрос и она соединяется сама с собой по правилам, в итоге которых любому слову (синоним Слово) получаются сопоставлены слова стоящие в той же строке слева от него (синоним СловаСлева), в той же строке справа от него (СловаСправа) в той же колонке выше него (СловаВыше) и в той же колонке ниже (СловаНиже). Области этих 4 соединений схематично показаны ниже, на первой схеме.

Интересно почитать:  Excel таблица для учета товара

Не считая того, еще есть 4 таблицы: представляющие все ячейки справа (ВсеСловаСправа) и все ячейки слева (ВсеСловаСлева), независимо от строчки — 2-ая схема. И все ячейки сверху (ВсеСловаВыше) и все ячейки снизу (ВсеСловаНиже) независимо от колонки — 3-я схема. Т.е. если прошлые таблицы представляли собой "линию" из ячеек, то эти таблицы — это поле ячеек. Соединения всех таблиц происходит по координатам ячейки в реквизитах НомерСтроки и НомерКолонки. И, естественно, реально объявляются во фразе ИЗ лишь те таблицы, которые употребляются во фразе ГДЕ.

Есть также доп вариант, для "условно-не-табличных" данных (к примеру, требуется вычленить из шапки документа номер, дату) — отлично работает принцип когда каждое слово по одному помещаются в таблицу (выходит, что каждое конкретное слово становится доступно для анализа раздельно от остального окружения в ячейке). За это отвечает реквизит РежимРаботы функции ТаблицаСловПоМассиву. Нумерация строк и колонок при всем этом остается как в оригинале. Так весьма отлично биться, к примеру, с датами (их пишут все кому как в голову взбредет: кто-то в одной ячейке, кто-то в 3-х, кто-то еще псевдо-печатное оформление оставляет в стиле "___")

Также отдельное внимание стоит направить на разборщика входящих данных (функция ОкультуритьТекст). Эта функция строит т.н. "незапятнанное" значение по уникальному значению, без нее весь банкет был бы неосуществим. Она работает так:

  • переводит все в верхний регистр
  • описывает соответствует ли значение одному из шаблонов даты, если да — пробует привести значение к типу дата.
  • описывает можно ли привести значение к числу. если да — приводит. при всем этом различные написания вроде "1 234.50", "1`234,50", "1.234,50" и т.п. будут обработаны удачно.
  • для строк — все знаки не считая букв, цифр и знаков "-", "/", "(", ")", подменяет на пробелы, удаляет пробелы в начале и конце строчки, удаляет повторяющиеся пробелы.

В итоге ее работы мы получаем т.н. "незапятнанное" значение, но, если нам все-таки захочется проанализировать оригинальное значение — оно сохраняется в реквизите ОригинальноеЗначение.

Интересно почитать:  Как задать область печати в excel 2007

Зрительная консоль запросов

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

  • в табличный документ консоли копи-пастим эксель
  • пишем текст собственного запроса
  • нажимаем выполнить — надлежащие запросу ячейки будут подсвечены

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

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

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

  • Думаем, какой столбец будет опорным. Это должен быть столбец, найти элементы которого можно с меньшей погрешностью. Я избрал стоимость. (многообещающе смотрелась ставка НДС, но ее нет в неких форматах)
  • Делаем запрос ко всем значениям опорного столбца. Для цены у меня таковой запрос: "ГДЕ СловаСлева ПОДОБНО ""%[А-Я]%"" И СловаСлева.Длина > 3 И ТипЗначения(СловаСправа) = Тип(Число) И СловаВыше ПОДОБНО ""%ЦЕНА%"" И ТипЗначения(Слово) = Тип(Число)"
  • Дальше, пишем по одному запросу к любому типу значений (один запрос — количество, 2-ой — наименование, 3-ий — сумма и т.д.), используя в их конструкции типа Слово.НомерСтроки В (&НомераСтрокОпорныхЗначений).
  • Если необходимо получить одно значение, а получить верно одно нереально — можно приоритезировать поисковые результаты по каким-то аспектам, к примеру, по номеру колонки либо длине строчки (при программном использовании доступна также установка фразы УПОРЯДОЧИТЬ ПО (то есть программное обеспечение — комплект программ для компьютеров и вычислительных устройств)) Вроде как не совершенно прекрасно выбирать наименование меж "шт" и "Туфли дамские" делая упор на длину строчки, но мне не встречалась ситуация, когда таковой аспект подводил.

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

Код на сто процентов открыт, запароленных и/либо обфусцированных участков кода нет. Писал на платформе 8.3.9.2233. Фортуны!

Ссылка на основную публикацию
Adblock
detector