Включить быструю загрузку данных excel

Обновлено: 05.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С при использовании различных методов

Совет Чтобы упросить, сформированы ли данные на этом плане с помощью Power Query, выберем ячейку с данными и, если появится вкладка ленты Контекст запроса, данные будут загружены из Power Query.

Знать, в какой среде вы сейчас? Power Query хорошо интегрирован с пользовательским интерфейсом Excel, особенно при импорте данных, работе с подключениями и редактировании таблиц, Excel таблиц и именовых диапазонов. Чтобы избежать путаницы, важно знать, в какой среде вы находитесь, в какой Excel или Power Query в любой момент времени.

Знакомые Excel, лента и сетка

Лента редактора Power Query и предварительный просмотр данных

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

Переименование я вкладок на таблицах Ямы лучше переименовывать по своему смыслу, особенно если их много. Особенно важно пояснить разницу между данными и данными, загруженными из редактора Power Query. Даже если у вас всего два листа: с таблицей Excel , которая называется Лист1,и запросом, созданным путем импорта таблицы Excel Таблица1,ее можно легко запутать. Всегда имеет смысл изменить названия ярлыков по умолчанию на более понятное. Например, переименуйте Лист1 в Таблицу данных и Таблицу1 в Таблицу запросов. Теперь понятно, какая вкладка с данными, а какая вкладка с запросом.

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

Создание запроса из импортируемых данных

Это самый распространенный способ создания запроса.

Выберем ячейку в данных и выберите запрос> Изменить.

Создание пустого запроса

Вы можете начать с нуля. Это можно сделать двумя способами.

Выберите Данные> Получить данные >из других источников > пустой запрос.

Выберите Данные> получить данные >запустить редактор Power Query.

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

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

Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как> "Получить данные" на Excel ленте.

Выберите Последние источники, чтобы выбрать источник данных, с помощью который вы работали. Эта команда выглядит так же, как> последние источники на Excel ленте.

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

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

Загрузка запроса из редактора Power Query

В редакторе Power Query сделайте следующее:

Чтобы загрузить на таблицу, выберите Главная> Закрыть & Загрузить > Закрыть & загрузить.

Чтобы загрузить данные в модель данных, выберите Главная > закрыть & Загрузить > Закрыть & Загрузить в.

В диалоговом окне Импорт данных выберите добавить эти данные в модельданных.

Совет Иногда команда "Загрузить в" неатривна или отключена. Это может произойти при первом создании запроса в книге. В этом случае нажмите кнопку Закрыть & Загрузить, на новом > запросы данных & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите загрузить в . Кроме того, на ленте редактора Power Query выберите Запрос > Загрузить в.

Загрузка запроса из области "Запросы и подключения"

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

В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.

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

Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).

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

Изменение запроса на Excel данных

Чтобы изменить запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить.

Изменение запроса в области "Запросы & подключения"

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

В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.

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

Изменение запроса в диалоговом окне "Свойства запроса"

В Excel выберите Data > Data & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства ,выберите вкладку Определение в диалоговом окне Свойства и нажмите кнопку Изменить запрос.

Совет Если вы работаете с запросом на > данных, в диалоговом окнеСвойства выберите вкладку Определение, а затем — Изменить запрос.

Модель данных обычно содержит несколько таблиц, расположенных в связи. Запрос загружается в модель данных с помощью команды Загрузить, чтобы отобразить диалоговое окно Импорт данных, а затем в поле Добавить эти данные в режим данныхl. Дополнительные сведения о моделях данных см. в дополнительных сведениях о том, какие источники данных используются в модели данных книги,Создание модели данных в Excelи Создание таблиц с помощью нескольких таблиц.

Чтобы открыть модель данных, выберите Power Pivot > Управление.

В нижней части окна Power Pivot выберите вкладку нужной таблицы.

Подтвердим, что отображается правильная таблица. Модель данных может иметь много таблиц.

Обратите внимание на имя таблицы.

Чтобы закрыть окно Power Pivot, выберите файл> Закрыть. Чтобы освободить память, может потребоваться несколько секунд.

Выберите > подключения & свойства >Запросы, щелкните запрос правой кнопкой мыши и выберите изменить.

Завершив внесение изменений в редакторе Power Query, выберите файл> Закрыть & загрузить.

Запрос на этом и в таблице в модели данных обновляются.

Загрузка запроса в модель данных занимает необычно много времени

Если вы заметили, что загрузка запроса в модель данных занимает намного больше времени, чем загрузка на лист, проверьте действия Power Query, чтобы узнать, фильтруется ли текстовый столбец или структурированный столбец списка с помощью оператора Contains. Это действие приводит Excel повторно прогонять весь набор данных для каждой строки. Более того, Excel не могут эффективно использовать многопрочитанные выполнения. В качестве обходного решения попробуйте использовать другой оператор, например Равно или Начинает с.

Корпорация Майкрософт знает об этой проблеме и находится в стадии исследования.

Вы можете загрузить Power Query:

На один из них. В редакторе Power Query выберите Home> Close & Load > Close & Load (&).

В модель данных. В редакторе Power Query выберите Home> Close & Load > Close & Load To.

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

Вы также можете динамически переопределять стандартные параметры запроса с помощью диалогового окна Импорт, которое отображается после выбора параметра Закрыть & Загрузкав.

Глобальные параметры, которые применяются во всех книгах

В редакторе Power Query выберите Параметры > параметры и параметры >запроса.

В диалоговом окне Параметры запроса в левой части в разделе GLOBAL выберите Загрузка данных.

В разделе Загрузка запроса Параметры по умолчанию сделайте следующее:

Выберите Использовать стандартные параметры загрузки.

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

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

Параметры книги, которые применяются только к текущей книге

В диалоговом окне Параметры запроса в левой части в разделе ТЕКУЩАЯ КНИГА выберите Загрузка данных.

Выполните одно или несколько из указанных ниже действий:

В области Обнаружение типоввыберите или сберем для обнаружения типов столбцов и их заглавных колонок для неструктурированных источников.

По умолчанию они обнаруживаются. Если вы предпочитаете формировать данные самостоятельно, отоставьте этот параметр.

В области Связивыберите или сберем создать связи между таблицами при первом добавлении в модель данных.

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

В области Связивыберите или отоберете update relationships when refreshing queries loaded to the Data Model (Связи) при обновлении запросов, загруженных в модель данных.

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

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

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

См. также

Excel для Интернета включает в себя технологию Power Query (также называемую функцией & преобразования), которая обеспечивает больше возможностей при работе с импортируемыми источниками данных. Вы можете просматривать запросы и связанные с ними сведения в области задач Запросы.

Примечание: Следите за дальнейшими объявлениями об улучшениях Excel интеграции с Веб-сайтом и Power Query.

Просмотр области задач "Запросы"

Убедитесь, что вы в режиме правки (выберите Просмотр> редактирование).

Выберите Запросы > данных.

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

В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.

Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.

И вот под катом вы можете найти подробности всего этого великолепия возможностей.

Совместимость и технические подробности

Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).

Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.

Как оно работает

После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.

В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.

Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.

Возможности Power Query

У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.

Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.

Объединение файлов лежащих в папке

Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка

Текстовые функции

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

Числовые функции

К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:

Арифметические операции
Возводить в степени, вычислять логарифмы, факториалы, корни
Тригонометрические операции
Округлять до заданных значений
Определять четность и т.д.

Функции для работы с датами, временем и продолжительностью

К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:

Автоматическое определение формата вписанной даты (в excel c этим большая боль)
Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.

Unpivot — Pivot

В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка

Операция Merge — смерть ВПР

Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.

Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.

Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка

Подключение к различным базам данных. Query Folding.

Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.

А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.

Язык программирования “М”

Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.

На каждое действие, которое мы выполняем с данными в графическом интерфейсе Power Query, в скрипт у нас пишется новая строчка кода. Отражая это, в панели с последовательностью действий (область 3), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка

Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:

Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.

Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api

Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.

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

Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.

Еще раз про повторяемость и про варианты применения

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

Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.

разбираю семантику для Толстых проектов,
Делаю частотные словари,
Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
Делаю аудит контекстной рекламы на данных из K50 статистика,
И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды

Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.

Пару слов про локализацию

На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.

Excel файл как Внешний источник данных 14
Подключимся и загрузим из файла Excel данные в таблицу значений 1С. Сделать теперь это очень просто. 1. В конфигурации добавляет новый объект метаданных типа " Внешние источники данных" и назовем его просто " Excel" . https://helpf.pro/uploads/img Активация открытой книги Excel для корректировки 0
Нужно было получить для изменения уже открытую книгу Excel, а если она закрыта, то открыть и изменять. Ниже код, который данную задачу решает. Попытка Эксель = Неопределено; Книга = Неопределено; Лист = Неопределено; Состояние(" Выполняется Быстрая функция чтения данных с листа Excel 9
При чтении файла Excel я использую вариантный массив. Он позволяет быстро получить ВСЮ таблицу листа в память, а также получать данные массива целыми колонками. Тем самым время на чтение области файла Excel в таблицу значений сокращается в десятки ра Вставить формулу, функцию в ячейку Excel 0
Ячейка = Лист.Cells(1,8); Ячейка.Formula=" =SUM(R11C:R C)" ; Процедура Сформировать() Перем Кат,Шаб; Ехсел=СоздатьОбъект(" Excel.Application" ); Книга=Ехсел.Workbooks; Если ФС.ВыбратьФайл(0,Кат,Шаб," Выбери файл Еxcel" ,," *.xls" ,)=1 Тогда Выбрать Остатки и Обороты по указанным счетам, отбор по субконто и выгрузка результата в EXCEL 0
Запрос=Новый Запрос; Запрос.Текст icon-exclamation-sign"> Посмотреть все результаты поиска похожих

Еще в этой же категории

Как сформировать документ в Word из 1С? (Active Document) 14
ВНИМАНИЕ! При использовании кодов описанных ниже, уберите символ _ Давайте рассмотрим на примерах как это делается! =) Мне известно 2 способа: 1. Путем замены шаблонного текста в макете Wordа. 2. Путем вставки текста с помощью закладок в Wo Как в excel сохраненный из 1С вставить новый лист? 13
Я думаю, все сталкивались с однолистовым excel файлом из 1С, у которого на первый взгляд, нет возможности добавить листы. Все не так страшно - их просто скрыли или, что чаще всего - даже не отображали (обусловлено созданием файла из стороннего прил Экспорт и Импорт данных 1С - Microsoft Excel 12
Экспорт Процедура ОсновныеДействияФормыЭкспорт(Кнопка) - выгружает из указанного справочника данные в режиме экспорта в файл в формате Microsoft Excel с указанным именем, используя преобразование данных к типу число и типу строка; Процедура Основ Преобразование XLS в MXL 10
Конвертация таблиц из Excel в MXL - скачать Converter Excel v MXL: Для 8.х - Для 7.7 - // Выбор файла Microsoft Excel Procedure FileNameStartChoice(Element, StdProcessing) StdProcessing = False; DialogFile = New FileDialog(FileDialogMode. Выгрузка данных в Excel с установкой разных параметров 9
// Процедура выгружает из указанного справочника данные в режиме экспорта в файл в формате Microsoft Excel // с указанным именем, используя преобразование данных к типу число и типу строка; Процедура ОсновныеДействияФормыЭкспорт(Кнопка) Попытка Посмотреть все в категории Работа с Microsoft Office и OpenOffice

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

Включаем вкладку “Разработчик”

Далее вызываем редактор нажав Alt+F11 (если у вас не вызывается можно включить вручную)

Создаётся новый макрос module1 в который нам нужно внести следующие строки (пункт 2) :

Sub DeleteAllTextBox()

Dim oSh As Shape

For Each oSh In ActiveSheet.Shapes

Сохраняем данные изменения и подтверждаем(пункты 3,4,5).

Закрываем данное окно и уже в самом Excel применяем этот макрос перейдя во вкладку

“Разработчик” и выбрав “Макросы” - “DeleteAllTextBox”

___________________________________________________________________________________

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

Надеюсь кому-то пригодится, всем спасибо за внимание

MS, Libreoffice & Google docs

466 постов 12.7K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

данные в документ excel вставляются листами или столбцами/строками из файлов, созданных в ИНФИН, 1С, SAP и других подобных ERP-системах, а также с других книг excel. <. > Успешно практикующий бухгалтер, может копировать некоторые столбцы годами.

И заработала книга быстро и неправильно.

Когда нужно вырыть котлован подгоняют бульдозер, а не ковыряют лопатой. Когда размеры таблиц превышают 100 Мб Excel - неправильный инструмент

Благодарю за информацию. Пошла отключать автомат

А перед этим всем - включить надстройку Inquire и сделать Clean excess cell formating на всех листах.

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

Привет, даёшь занятия по excel?

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

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

А может люди практикующие такие таблицы в 2020 должны страдать, и в идеале банкротиться/увольняться? Не понимаю этих тенденций помогать утопающему тонуть медленнее. В чем смысл?

Как-то познакомился с человеком, который составлял аналог рейтинг ЭЛО для футбольных команд( европейские чемпионаты + сборные). Хобби у него было такое. Данные вбивал за последние пару десятилетий.
Я посмотрел эту эксельку, она весила >150mb и отрывалась минут 15.

у меня на компе лет 10 была простенькая игрушка стрелялка встроенная в экселевский файл. как это было сделано? до сих пор интересно)

Так вот откуда эти объекты берутся.

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

Консолидация Данных в Excel Уроки Excel

В этом видео показана консолидация данных в эксель.

Случаи из практики 105

— У моей девушки очень своеобразное чувство юмора, - с унылым видом произнес клиент. – Причем я подобрал самое литературное слово для описания того бреда что она делает.

— Это сильно мешает вашим отношениям?

— Ну, как сказать? Вроде бы да, а вроде и нет - просто нам очень весело вместе, но когда дело доходит до всего что находится вокруг, то тут все становится все хуже и хуже.

— Расскажите поподробнее, пожалуйста.

— Подробнее? Я попробую… Взять что ли ее прошлогоднюю выходку: едем мы к моей матери в гости, я за рулем, полсалона занимают стройматериалы, а Лена сидит сзади, на пассажирском сидении. Светофор загорается красным, я останавливаюсь, а рядом, на соседней полосе стоит полицейский экипаж. Тут моя умница, чуть-чуть приоткрывает окно и, высунув руки наружу, начинает кричать что ее похитили и хотят изнасиловать. Мужики среагировали мгновенно – похватали пушки, выскочили из машины, один взял меня на прицел, а второй оббежал сзади и выволок наружу. Словно попал в ГТА, только на месте того чувака, которого выкидывают из машины – оказался я.

— Чем закончился этот инцидент?

— Ну, полицейские не оценили юмора и нам пришлось очень долго объяснять их старшему что это была всего лишь шутка. На нас хотели написать протокол и привлечь за мелкое хулиганство – кое-как сумели уговорить не возбуждать дело. В общем, нажили проблем по самое не хочу! Но и смеялись до упада, когда смотрели снятое Леной видео с моим растерянным лицом.

— И часто она выкидывает такие шутки?

— Вы пытались как-то образумить ее?

— Вы переживаете что она рано или поздно наживет вам обоим большие проблемы?

— Ну а как тут не беспокоиться? Недавно она решила напугать девчонок из бухгалтерии, устроив в курилке мини пожар. Началась паника, народ начал метаться туда-сюда, босс вообще первым выскочил на улицу и побежал отгонять свою ауди. И тут Лена, как настоящий герой, выходит в коридор с огнетушителем и без проблем тушит пламя. Коллеги чуть ли не на руках ее носили и крайне недобрым словом поминали того, кто все это устроил. Она потом ухахатывалась, когда рассказывала, что завхоз ее чуть не застукал с зажигалкой в руке. Я попытался объяснить ей что так себя вести крайне безответственно – кто-нибудь мог пострадать во время давки или огонь стал бы больше, и она не сумела его потушить.

— Вы правы: такое поведение опасно и для себя, и для окружающих. Нужно принимать меры.

— Вот я и решился привести ее к вам, - просиял мужчина. – А то мне кажется, что ее пранки скоро выйдут из-под контроля.

— Думаете она придет на сеанс?

— Если не захочет добровольно, то я знаю, как решить дело по-другому, - мрачным голосом ответил он. – Раз уж ей не хватает остроты ощущений, то я ей их обеспечу. У меня в органах есть старый друг - я отдам ему все видео с ее приколами и попрошу разобраться. Как только у нее начнутся серьезные проблемы с законом, она сразу же станет сговорчивей.

— Вы же понимаете, что будет если она узнает кто за этим стоит?

— Это не важно – сейчас главное сделать так, чтобы она не угробила себя или кого-нибудь еще…

Мой опыт работы в «нефтегазовой отрасли» (8)

Лето закончилось, прошло еще пару месяцев на голом окладе, и вдруг работа на нас действительно нахлынула. Руководству, видимо, удалось договориться с национальным достоянием. Началось всё с писем. Письма наружу с предложением и полным описанием наших услуг и их стоимостью. Если раньше в день было одно-два, и-то не каждый день, то теперь вдруг их число стало доходить до 20 в день! Это когда не успеваешь сверстать и подготовить одно – надо бежать за данными для другого и так целый день. Мы чувствовали, что процесс пошел, шестеренки закрутились. В один из таких перегруженных дней я даже заработал первую похвалу от начальницы. Когда она узнала, что несколько писем, которые она только собиралась озвучить были уже готовы, она сказала:

- Ну, ты это, прям – электровеник! – сказала она мне лично. Больше слов похвалы до самого конца своей работы там я от неё не услышал.

Начали приходить авансы от заказчика и на эти поступления стали гасить задолженность по зарплате. Принесли расчетные листки. Забавно было получить расчетный листок за июнь в ноябре и наблюдать, как в нем выплаты зарплаты разбиты на 6 частей и стоят с июня по октябрь. По факту это ведь документально зафиксированное, с подписью главбуха, нарушение ТК РФ. Но все были рады, что кризис миновал и теперь заживем!

- Будем искать только с опытом и желательно с таким-же, как у нас или подобным. Учить работать я никого не хочу.

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

Собеседования. Решили сначала попробовать кого-то из знакомых, чтобы не срабатываться с людьми с улицы. Проштудировали всех и оказалось, что моя дальняя родственница подходит по всем параметрам и готова выйти и поработать. Я подумал, что этот вариант будет отличным на случай какого-то конфликта на моей стороне всегда будет еще один человек. Но, признаться честно, не особо подумал тогда, во что я втягиваю своего родственника…

Работала она до этого вольным бухгалтером, обсчитывала несколько контор или ИП-шников по фрилансу, скажем так. И жила на это, без постоянного места работы. Муж, сын, несколько машин – на жизнь хватало, но решила попробовать поработать на постоянку. Пришла к нам, побеседовала с Р, впечатление положительное, я ей всё показал, уже были уверены, что всё будет в порядке. Как вдруг, решила вмешаться главбухша! Оказывается, она в этот день была на месте, заняться ей, судя по всему, было особо нечем, и она решила лично провести еще одно собеседование с кандидатом. Причем, провести его в стресс-формате!

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

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

Мой опыт работы в «нефтегазовой отрасли» (2)

В общем, в тот раз главбухша меня пощадила. Этот человек вообще заслуживает отдельного развернутого рассказа. До меня потом дошло, что мы пересеклись с ней всё-таки один раз чуть раньше этого злосчастного звонка. Я стоял в коридоре, решал по телефону свои дела, а она бежала мимо меня в женский туалет.

- МОЛОДОЙ ЧЕЛОВЕК, ЗДОРОВАТЬСЯ, ЧТО, НЕ УЧИЛИ? – прокричала она мне и пронеслась мимо.

Так я понял, что это какое-то начальство, но знакомы мы не были. Очень крупное, мешковатое телосложение, крупные, мужские черты лица. Возраст сложно сходу определить, злобные горящие глаза, волосы собраны в жидкий хвост, телесного цвета, с огромными залысинами. Всегда взвинченный тон общения, упомянутый в прошлом посте мерзкий ультразвук, если смех, то нарочито громкий. Ну и, как насмешка над всем этим набором, витиеватая фамилия. Она была из разряда тех людей, которые не знают покоя. Которые всегда на взводе. И им нужно, чтобы на взводе были все окружающие. Без этого у них не запускается механизм рабочего процесса. А уж какой барыней она бороздила владения! С каким надменным презрением смотрела на "челядь". Теперь я знаю, как выглядели те самые столбовые дворянки, которые, не моргнув глазом, могли вспороть живот крепостному, чтобы ножки погреть.

Во-первых, текст почти всегда как под копирку. Одинаковый. О том, что работать очень хорошо и всегда компания поощряет тех, кто пришел сюда именно работать, а не баклуши бить. Есть премии, офис чистый и уютный, приятные коллеги. Но кому уж точно тут не место, так это лентяям! Негативные отзывы пишут обиженные на жизнь лентяи! А по факту, работа хорошая, из минусов – отсутствие ДМС.

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

В-третьих, те, кто писал положительные отзывы, всегда представлялись знакомыми мне инициалами. Или искаженными именами, но все равно тем, кто работает внутри компании все эти сотрудники известны и их легко можно узнать. Я так понимаю, что это нарочно делалось еще и для того, чтобы отчитаться перед главбухом. Что вот Коля. Коля написал отзыв как просили, премию в этом месяце получит.

Ну и в-четвертых, ну не пишут столько позитивных отзывов все подряд сотрудники! Это не райское место и не работа мечты, чтобы так радоваться и радость эту выражать. Самый лучший показатель для любой конторы – то, что отзывов о ней на этих сайтах нет, или их мало! Но когда толпа народу пишет, как тут хорошо – тухловатый запах бьет в нос сразу. Отзывы люди пишут, когда произошло что-то вопиющее, несправедливое, с ними нехорошо пуступил работодатель, обманул. Когда всё нормально – все молча работают. У них не возникает даже мысли заходить на сайты отзывов и мониторить их, не то, чтобы писать.

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

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

Читайте также: