Какой столбец нужно обязательно указывать при загрузке услуг из excel

Обновлено: 06.07.2024

Загрузка данных из табличных документов, созданных в редакторе Microsoft Excel – довольно часто встречающаяся задача в работе любого программиста 1С. Программа эта широко распространена, любима пользователями и часто используется для хранения и обработки различного рода информации, будь то прайс-лист или данные по продажам за период.

Существует несколько различных методов работы с файлами Excel при загрузке данных в 1С, в том числе:

1. загрузка с помощью свойства Cells;

2. загрузка с помощью метода Range и последующим обращением к данным через метод GetValue();

3. загрузка с помощью метода Range и последующим обращением к данным через свойство Value;

4. загрузка с помощью технологии ADO [2] и использованием компоненты GameWithFire [3].

Рассмотрим вышеперечисленные методы подробнее.

1. Загрузка данных с помощью свойства Cells

Этот метод используется в обработке «ЗагрузкаДанныхИзТабличногоДокумента.epf», которая присутствует на диске ИТС, и основан он на обращении к объекту Excel.Application через технологию COM с последующим последовательным чтением данных в ячейках обращением к свойству Cells.

Значение = ExcelЛист.Cells(Row,Column).Text;

Собственно, здесь все достаточно просто:

а) подключаемся к Excel

б) получаем диапазон данных на листе:

в) последовательно считываем данные из файла:

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

2. Загрузка данных с помощью метода Range и последующим обращением к данным через метод GetValue()

Этот метод так же основан на возможностях объекта Excel.Application, но, в отличие от загрузки с использованием свойства Cells, позволяет получить сразу все значения в виде двумерного массива. Работает следующим образом:

а) подключаемся к Excel (аналогично методу 1);

б) определяем диапазон используемых ячеек (аналогично методу 1)

в) считываем данные с помощью метода Range(начальнаяЯчейка, конечнаяЯчейка). Здесь начальная и конечная ячейки – это элементы свойства Cells, о котором шла речь выше.

В результате, в переменной Диапазон мы получим значение типа COMSafeArray, который представляет собой объектную оболочку над многомерным массивом SAFEARRAY. Иначе говоря, мы получаем массив, который, как известно, является областью памяти. А с областью в оперативной памяти работа всегда быстрее.

При этом надо учитывать, что массив этот многомерный. Для нашего случая это будет двумерный массив.

г) последовательно получаем значения из массива с помощью метода GetValue(столбец, строка)

Данный метод работает значительно быстрее, чем чтение с помощью свойства Cells.

3. Загрузка данных с помощью метода Range и последующим обращением к данным через свойство Value

Этот метод отличается от предыдущего только тем, что вместо работы с объектом типа COMSafeArray мы сразу получаем набор значений в виде стандартного массива 1С (тип Массив).

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

а) подключаемся к Excel (как и в предыдущих случаях)

б) считываем данные используя свойство UsedRange и метод Выгрузить()

В результате, получаем двумерный массив типа Массив

в) последовательно получаем значения из массива

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

4. Загрузка данных с помощью технологии ADO и использованием компоненты GameWithFire

Данный способ использует технологию ADO [2], которая реализуется с помощью библиотеки ADOdb [4] и позволяет получать доступ к любым СУБД, реализующим технологию ODBC. Собственно, Excel попадает в данный список и, следовательно, мы можем получить данные, используя указанную технологию.

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

Ниже приведен листинг части кода, который демонстрирует данную возможность. Пример во многом использует материал, описанный в источнике [1].

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

Заключение

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

Замеры производились на файле Excel с числом строк 16 000 и числом колонок 20. Таким образом, общее число ячеек в области составило 320 000. Значения в таблице говорят сами за себя.

Таблица 1. Время загрузки данных из Excel в 1С при использовании различных методов

Обработка «Помощник загрузки данных» находится в меню «Лицевые счета», блок «Сервис». Предназначена для загрузки в программу данных из файлов, созданных в Excel.

Внешний вид обработки:

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

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

Реализована загрузка следующих данных:

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

Шаблон «Льготы граждан» - предназначен для загрузки информации по льготам. При загрузке этого шаблона выполняется поиск по Фамилии, имени и отчеству гражданина. Если гражданин не будет найден, то программа создаст нового гражданина с указанными данными. Гражданину присваивается категория льготы (если указанной в файле категории льготы не будет найдено, тогда категория будет создана), устанавливается дата начала предоставления льготы. Так же при необходимости можно указать данные о документе, на основании которого гражданину предоставлена льгота. Эти данные не являются обязательными для заполнения.

Примеры шаблонов:

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

Например, в программе уже создан регион «республика Карелия». Если при загрузке помещений будет указан этот же регион (написание не должно различаться), то второй раз он создан не будет.

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

Если программа выявит несовпадение, например, в колонке «Регион», будет создан дополнительный регион. Аналогично с населенными пунктами и улицами: если соответствие не будет установлено, то объекты будут созданы.

Возможность удаления данных.

С помощью обработки «Помощник загрузки данных» так же можно подготовить удаление данных из программы. Будут помечены на удаление все данные по лицевым счетам, приборам учета, гражданам и объектам учета вне зависимости от того, какой шаблон выбран в обработке в данный момент.

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

Перед удалением данных происходит двойная проверка. При установке флага «Включить возможность удаления данных» появится первое предупреждение. При нажатии на кнопку «Продолжить» на форме появится кнопка «Выполнить удаление».

После нажатия на кнопку «Да» в последней форме запускается процесс подготовки к удалению информации: будут установлены пометки на удаление.

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

Как вызвать функцию ВПР. Функция ВПР в Excel

В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.

Появляется диалоговое окно Вставка функции. В строке Поиск функции вводим ВПР. Нажимаем найти. По результатам поиска, в пункте Выберите функцию, появляется ВПР. Нажимаем на нее левой кнопкой мыши два раза или нажимаем ОК. Появляется непосредственно диалоговое окно функции ВПР – Аргументы функции.

Теперь перейдем непосредственно к вариантам применения функции ВПР.

Первый вариант использования функции ВПР.

Для примера возьмем две таблице. В одной Таблице №1 будет перечень с названиями конфет и будет указана их цена за кг. В другой, Таблица №2, тот же перечень, но с указанием их количества в кг. Наша задача добавить в Таблицу №2, в столбец Цена, цену конфет из Таблицы №1, чтобы в итоге получить стоимость. Названия конфет в разных таблицах находятся в разных местах, поэтому просто скопировать цену конфет с одной таблице в другую не получиться.

Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.

Вызываем функцию ВПР, как описано выше.

Аргументы функции. Функция ВПР в Excel.

Искомое_значение.

Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.

Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).

Таблица.

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

Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.

В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты

Номер_столбца.

Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.

Интервальный _просмотр.

В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.

Если мы поставим цифру один «1», функция будет осуществлять поиск не точного совпадения, а приближенного к нашему критерию поиска.

Вот как это выглядит все вместе.

Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.

Второй вариант использования функции ВПР.

У нас есть Таблица №1 и Таблица №2. Каждая таблица состоит из одного столбца. Для понимания алгоритма работы функции ВПР, в данном случае, таких простых таблиц достаточно. Столбцы содержат практически одинаковые данные. При этом, нам нужно сравнить их и узнать, какие данные есть в Таблице №2, но нет в Таблице №1.

Справа от Таблицы 2, в ячейку G3, вставляем функцию ВПР. Это расположение взято в качестве примера, можно использовать любой другой столбец и оформление.

В диалоговом окне, Аргументы функции прописываем следующие данные:

Искомое_значение. Это значение ячейки из Таблицы №2, наличие которой мы проверяем в Таблице №1. В нашем примере, это ячейка F3 (Значение 9).

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

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

Интервальный _просмотр. Здесь ставим ноль «0», так как хотим, чтобы функция искала точные совпадения.

Можно проверить с точностью наоборот. И найти какие данные есть в Таблице №1 но нет в Таблице № 2.

Обратите внимание. Функция ВПР в Excel.

Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.

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

Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.

Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).

Возможные ошибки.

Автоматическое заполнение ячеек также используют для продления последовательности чисел c заданным шагом (арифметическая прогрессия). Чтобы сделать список нечетных чисел, нужно в двух ячейках указать 1 и 3, затем выделить обе ячейки и протянуть вниз.

Эксель также умеет распознать числа среди текста. Так, легко создать перечень кварталов. Введем в ячейку «1 квартал» и протянем вниз.

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

Автозаполнение в Excel из списка данных

Ясно, что кроме дней недели и месяцев могут понадобиться другие списки. Допустим, часто приходится вводить перечень городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Вначале нужно создать и сохранить (в нужном порядке) полный список названий. Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки.

В следующем открывшемся окне видны те списки, которые существуют по умолчанию.

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

Жмем ОК. Список создан, можно изпользовать для автозаполнения.

Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.

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

Посередине выбирается нужный тип:

арифметическая прогрессия – каждое последующее значение изменяется на число, указанное в поле Шаг
геометрическая прогрессия – каждое последующее значение умножается на число, указанное в поле Шаг
даты – создает последовательность дат. При выборе этого типа активируются переключатели правее, где можно выбрать тип единицы измерения. Есть 4 варианта:

- - день – перечень календарных дат (с указанным ниже шагом)
  - рабочий день – последовательность рабочих дней (пропускаются выходные)
  - месяц – меняются только месяцы (число фиксируется, как в первой ячейке)
  - год – меняются только годы
  - автозаполнение – эта команда равносильная протягиванию с помощью левой кнопки мыши. То есть эксель сам определяет: то ли ему продолжить последовательность чисел, то ли продлить список. Если предварительно заполнить две ячейки значениями 2 и 4, то в других выделенных ячейках появится 6, 8 и т.д. Если предварительно заполнить больше ячеек, то Excel рассчитает приближение методом линейной регрессии, т.е. прогноз по прямой линии тренда (интереснейшая функция – подробнее см. ниже).
  Нижняя часть окна Прогрессия служит для того, чтобы создать последовательность любой длины на основании конечного значения и шага. Например, нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не удобно. Поэтому предварительно нужно выделить только ячейку с одним первым значением. Далее в окне Прогрессия указываем Расположение, Шаг и Предельное значение.
  
  Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.
  
  Автозаполнение чисел с помощью мыши
  
  Автозаполнение в Excel удобнее делать мышкой, у которой есть правая и левая кнопка. Понадобятся обе.
  
  Допустим, нужно сделать порядковые номера чисел, начиная с 1. Обычно заполняют две ячейки числами 1 и 2, а далее левой кнопкой мыши протягивают арифметическую прогрессию. Можно сделать по-другому. Заполняем только одну ячейку с 1. Протягиваем ее и получим столбец с единицами. Далее открываем квадратик, который появляется сразу после протягивания в правом нижнем углу и выбираем Заполнить.
  
  Если выбрать Заполнить только форматы, будут продлены только форматы ячеек.
  Сделать последовательность чисел можно еще быстрее. Во время протягивания ячейки, удерживаем кнопку Ctrl.
  
  Этот трюк работает только с последовательностью чисел. В других ситуациях удерживание Ctrl приводит к копированию данных вместо автозаполнения.
  
  Если при протягивании использовать правую кнопку мыши, то контекстное меню открывается сразу после отпускания кнопки.
  
  При этом добавляются несколько команд. Прогрессия позволяет использовать дополнительные операции автозаполнения (настройки см. выше). Правда, диапазон получается выделенным и длина последовательности будет ограничена последней ячейкой.
  
  Чтобы произвести автозаполнение до необходимого предельного значения (числа или даты), можно проделать следующий трюк. Берем правой кнопкой мыши за маркер чуть оттягиваем вниз, сразу возвращаем назад и отпускаем кнопку – открывается контекстное меню автозаполнения. Выбираем прогрессию. На этот раз выделена только одна ячейка, поэтому указываем направление, шаг, предельное значение и создаем нужную последовательность.
  
  Очень интересными являются пункты меню Линейное и Экспоненциальное приближение. Это экстраполяция, т.е. прогнозирование, данных по указанной модели (линейной или экспоненциальной). Обычно для прогноза используют специальные функции Excel или предварительно рассчитывают уравнение тренда (регрессии), в которое подставляют значения независимой переменной для будущих периодов и таким образом рассчитывают прогнозное значение. Делается примерно так. Допустим, есть динамика показателя с равномерным ростом.
  
  Для прогнозирования подойдет линейный тренд. Расчет параметров уравнения можно осуществить с помощью функций Excel, но часто для наглядности используют диаграмму с настройками отображения линии тренда, уравнения и прогнозных значений.
  
  Чтобы получить прогноз в числовом выражении, нужно произвести расчет на основе полученного уравнения регрессии (либо напрямую обратиться к формулам Excel). Таким образом, получается довольно много действий, требующих при этом хорошего понимания.
  
  Так вот прогноз по методу линейной регрессии можно сделать вообще без формул и без графиков, используя только автозаполнение ячеек в экселе. Для этого выделяем данные, по которым строится прогноз, протягиваем правой кнопкой мыши на нужное количество ячеек, соответствующее длине прогноза, и выбираем Линейное приближение. Получаем прогноз. Без шума, пыли, формул и диаграмм.
  
  Если данные имеют ускоряющийся рост (как счет на депозите), то можно использовать экспоненциальную модель. Вновь, чтобы не мучиться с вычислениями, можно воспользоваться автозаполнением, выбрав Экспоненциальное приближение.
  
  Более быстрого способа прогнозирования, пожалуй, не придумаешь.
  
  Автозаполнение дат с помощью мыши
  
  Довольно часто требуется продлить список дат. Берем дату и тащим левой кнопкой мыши. Открываем квадратик и выбираем способ заполнения.
  
  По рабочим дням – отличный вариант для бухгалтеров, HR и других специалистов, кто имеет дело с составлением различных планов. А вот другой пример. Допустим, платежи по графику наступают 15-го числа и в последний день каждого месяца. Укажем первые две даты, протянем вниз и заполним по месяцам (любой кнопкой мыши).
  
  Обратите внимание, что 15-е число фиксируется, а последний день месяца меняется, чтобы всегда оставаться последним.
  
  Используя правую кнопку мыши, можно воспользоваться настройками прогрессии. Например, сделать список рабочих дней до конца года. В перечне команд через правую кнопку есть еще Мгновенное заполнение. Эта функция появилась в Excel 2013. Используется для заполнения ячеек по образцу. Но об этом уже была статья, рекомендую ознакомиться. Также поможет сэкономить не один час работы.
  
  На этом, пожалуй, все. В видеоуроке показано, как сделать автозаполнение ячеек в Excel.
  
  Читайте также: