387

Hard-Excel без тормозов, борьба с Гига-книгами

Hard-Excel без тормозов, борьба с Гига-книгами Excel, Бухгалтерия, Офис, Длиннопост

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



1.Если данные в документ excel вставляются листами или столбцами/строками из файлов, созданных в ИНФИН, 1С, SAP и других подобных ERP-системах, а также с других книг excel. При этом они генерируют в файл «пустые» ячейки (забитые пробелами) и объекты типа “Надпись” . Успешно практикующий бухгалтер, может копировать некоторые столбцы годами. Со временем файл становится очень объёмным, так как забивается. Решение следующее:


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

Hard-Excel без тормозов, борьба с Гига-книгами Excel, Бухгалтерия, Офис, Длиннопост

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

Hard-Excel без тормозов, борьба с Гига-книгами Excel, Бухгалтерия, Офис, Длиннопост

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


Sub DeleteAllTextBox()

Dim oSh As Shape

For Each oSh In ActiveSheet.Shapes

oSh.Delete

Next oSh

End Sub

Hard-Excel без тормозов, борьба с Гига-книгами Excel, Бухгалтерия, Офис, Длиннопост

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

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

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

Hard-Excel без тормозов, борьба с Гига-книгами Excel, Бухгалтерия, Офис, Длиннопост

Готово!

___________________________________________________________________________________

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

Hard-Excel без тормозов, борьба с Гига-книгами Excel, Бухгалтерия, Офис, Длиннопост

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

Найдены дубликаты

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

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

раскрыть ветку 11
0

Use DBMS Luke, go to the bright side.

-2

Быстро и правильно, там нет нечего что влияет на сами данные)

раскрыть ветку 9
+2
Вопрос по поводу макроса. Почему нельзя просто выделить пустые строки и столбцы комбинацией "ctrl + shift+вниз или влево" и потом удалить их? Обычно на скорую руку так делаю, когда из-за копирований файл начинает слишком много весить и тормозить. Спрашиваю с искренним интересом. Без подкола.
раскрыть ветку 6
+1

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

+1
Что делает скрипт из поста? Я не вижу там удаления пустых ячеек.
+3

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

раскрыть ветку 1
+1
На самом деле он может весить 100мб даже в виде обычного списка строк на 600. Когда много копируешь из других книг, данные начинают забиваться и в ячейки, которые выглядят как пустые. Поэтому файл с, по сути, небольшим количеством информации начинает весить как напичканный макросами толмуд на 5к строк.
+2

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

раскрыть ветку 3
+4

Этот макрос не убирает пустые ячейки. Он удаляет все фигуры и русунки в книге.
Будьте осторожны.


Кстати не обязательно сохранять книгу с макросом. Для обного запуска можно нажить F5 после шага 2 и затем удалить модуль.

+2

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


Sub ReplaceNullString()

Dim rR As Range, rF As Range, rC As Range

Dim avR, lr As Long, lc As Long

Set rR = Intersect(ActiveSheet.UsedRange, Selection)

On Error Resume Next

Set rR = rR.SpecialCells(xlCellTypeConstants)

On Error GoTo 0

If rR Is Nothing Then

MsgBox "В выделенных ячейках нет значений!", vbInformation, "pikabu.ru"

Exit Sub

End If

Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole)

If Not rF Is Nothing Then

avR = rR.Value

For lr = 1 To UBound(avR, 1)

For lc = 1 To UBound(avR, 2)

If avR(lr, lc) = "" Then rR.Item(lr, lc).Value = Empty

Next lc

Next lr

MsgBox "Строки нулевой длины заменены", vbInformation, "pikabu.ru"

Exit Sub

End If

MsgBox "Строк нулевой длины на листе нет или лист защищен", vbInformation, "pikabu.ru"

End Sub

-3

Рад, что смог сэкономить кому-то время.

+1

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

+1

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

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

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

+1

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

+1

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

+1

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

раскрыть ветку 1
+1
Иногда похожие проблемы вызывают разные версии экселя. А руководству нужна презентация, вызываемая из отчета производства по 5 предприятиям за 4 предыдущих года и на 3 следующих. Вот и ебитес как хотите🤣
0

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

0

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

0

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

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

0

@moderator,  можете перенести пост в сообщество MS, Libreoffice & Google docs

раскрыть ветку 1
0

ok

Похожие посты
174

Функции Excel: СУММЕСЛИ (суммирование по одному условию)

Функция СУММЕСЛИ используется, когда вам необходимо просуммировать значения диапазона, соответствующие одному заданному условию. Например, необходимо просуммировать заказы за период по менеджеру (поставщику, покупателю, дате) или значения больше 10 000.

Короткое видео⬇⬇⬇

Формула:

=СУММЕСЛИ(Диапазон ячеек (ГДЕ ИЩЕМ); Условие (ЧТО ИЩЕМ); [диапазон_суммирования] (ЧТО СУММИРУЕМ))

Аргументы функции:

Аргумент_1 Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel.

Аргумент_2 Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Подстановочные знаки можно включать в вопросительный знак (?), чтобы они соответствовали любому символу, звездочку (*) в соответствии с любой последовательностью знаков. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак "тильда" (~).

Условие может быть выражено в виде 42, ">66 000", B5, "7?", "Iphone *", "* ~?" или "сегодня" ().

ВАЖНО: текстовые условия, условия с логическими и математическими знаками необходимо заключать в двойные кавычки ("текст").

Аргумент_3 Диапазон_суммирования (не обязательный) ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона.

Если аргумент опущен, Excel суммирует ячейки, указанные в Аргументе_1 (те же ячейки, к которым применяется условие).

Диапазон Аргумента_3 должен быть одного размера , что и Аргумента_1.

Функцию ВПР можно вызвать, через:

Мастер формул, раздел Математические;

Вкладку Формулы ► Математические;

Введя в строку =СУММЕСЛИ(.

Еще интересное по теме Excel:

Трюки с листами книги

ВПР (вертикальный просмотр)

Как перевернуть таблицу в Excel

Курсы валют в Google Таблице (GoogleFinance)

Мгновенное заполнение

"Умные" таблицы в Excel

Как отобразить листы в файлах Excel, выгруженных из 1С

Показать полностью
569

Как напечатать заголовки таблицы Excel на каждой странице

Короткое видео на тему ⬇⬇⬇

Шаги:

Перейдите на вкладку Разметка страницы ► Печатать заголовки:

Как напечатать заголовки таблицы Excel на каждой странице Excel, Ms Office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров

В открывшемся окне, на вкладке Лист ► Печатать заголовки ► сквозные строки (для печати столбцов, сквозные столбцы):

Как напечатать заголовки таблицы Excel на каждой странице Excel, Ms Office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров

Добавьте ссылку на диапазон с заголовками (или столбцами):

Как напечатать заголовки таблицы Excel на каждой странице Excel, Ms Office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров

Готово.

Показать полностью 2
195

Три способа перевернуть таблицу в Excel

Транспонирование - замена строк на столбцы, распространенная задача.


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

Короткое видео ⬇⬇⬇

Первый способ: Специальная вставка

Копируйте данные;

Встаньте в необходимом месте и нажав сочетание клавиш CTRL+ALT+V, или правая кнопка мыши (пкм), в меню иконка Транспонировать или выберите Специальная вставка:

Три способа перевернуть таблицу в Excel Excel, Ms Office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа

В открывшемся окне поставьте галку напротив Транспонировать:

Три способа перевернуть таблицу в Excel Excel, Ms Office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа

Готово.

Свойства: при обновлении данных в исходной таблице, данные в новой таблице не обновляются, это обычное копирование.

Способ второй: функция ТРАНСП

Выделите область, в которую необходимо вставить таблицу (в размер будущей перевернутой таблицы);

Введите =ТРАНСП(массив), где массив — это диапазон исходной таблицы;

Нажмите CTRL+SHIFT+ENTER, т.к. это формула массива и просто ENTER не сработает;

Готово.

Свойства: при обновлении данных в исходной таблице, данные в новой таблице обновляются.

Способ третий: транспонирование с помощью Power Query

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

Загрузите таблицу в редактор: Данные ► Получить данные ► Из других источников ► Из таблицы/диапазона;

Последовательно выполните действия:

1. Главная ► Использовать первую строку в качестве заголовка ► Использовать заголовки как первую строку;

2. Преобразование ► Транспонировать;

3. Главная ► Использовать первую строку в качестве заголовка;

Загрузите запрос: Главная ►Закрыть и загрузить ► Закрыть и загрузить в... ►Только создать подключение;

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

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

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

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

Показать полностью 1
45

Как сохранить отчет Excel в формате PDF

Задаётесь вопросом, как сохранить отчет Excel (выделенную область, лист, файл целиком) в PDF формате? Все очень просто, видео покажет, последовательность сохранения отчета Excel в файл формата PDF. Действия схожи для всех приложений MS Office. Приятного просмотра ⬇⬇⬇

Алгоритм

Откройте файл:

Как сохранить отчет Excel в формате PDF Excel, Ms Office, Аналитика, Бухгалтерия, Обучение, Видео, Длиннопост

Нажмите CTRL+P (откроется окно печати) или выберите Файл ► Печать, настройте Параметры печати:

Как сохранить отчет Excel в формате PDF Excel, Ms Office, Аналитика, Бухгалтерия, Обучение, Видео, Длиннопост

Нажмите F12 или выберите Файл ► Сохранить, как, в открывшемся окне выберите место, куда сохранить будущий файл PDF, переименуйте при необходимости:

Как сохранить отчет Excel в формате PDF Excel, Ms Office, Аналитика, Бухгалтерия, Обучение, Видео, Длиннопост

Нажмите Тип файла и выберите вариант PDF:

Как сохранить отчет Excel в формате PDF Excel, Ms Office, Аналитика, Бухгалтерия, Обучение, Видео, Длиннопост

В Параметрах выберите вариант сохранения (все страницы или отдельные, книгу или диапазон), нажмите ОК:

Как сохранить отчет Excel в формате PDF Excel, Ms Office, Аналитика, Бухгалтерия, Обучение, Видео, Длиннопост

Сохранить, Готово.

Как сохранить отчет Excel в формате PDF Excel, Ms Office, Аналитика, Бухгалтерия, Обучение, Видео, Длиннопост
Показать полностью 5
124

Как переместить строку или столбец в Сводной таблице Excel

Сводная таблица Excel, имеет не такой порядок столбцов и строк, как вам нужно, а сортировка фильтром не помогает??? Смотрим, как легко решить проблему ⬇⬇⬇

Быстрое перемещение строк и столбцов в Excel

197

Как отобразить листы в файлах Excel, выгруженных из 1С

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

Как отобразить листы в файлах Excel, выгруженных из 1С Excel, Ms Office, Аналитика, Бухгалтерия, Отдел кадров, Обучение, Офис, Маркетинг, Видео

Как решить проблему и отобразить ярлычки??? Лучше один раз увидеть ⬇⬇⬇

Интересное по теме Excel:

ТОП-30 горячих клавиш в Excel

Мгновенное заполнение

Трюки с листами книги

Быстро удалить все картинки с листа

Быстрое перемещение строк и столбцов

"Умные" таблицы в Excel

Сводные таблицы в Excel: как создать?

Как построить график или диаграмму из Сводной таблицы

Суммирование в Excel

Поиск и удаление повторяющихся значений в Excel

455

Excel понятным языком: мгновенное заполнение

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


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

Короткое видео на тему ⬇⬇⬇

Алгоритм работы

Введите данные в требуемом формате в соседнем столбце:

Excel понятным языком: мгновенное заполнение Excel, Ms Office, Маркетинг, Отдел кадров, Аналитика, Бухгалтерия, Офис, Продуктивность, Видео, Длиннопост

Данные ► Работа с данными ► Мгновенное заполнение, или нажмите сочетание клавиш Ctrl+E:

Excel понятным языком: мгновенное заполнение Excel, Ms Office, Маркетинг, Отдел кадров, Аналитика, Бухгалтерия, Офис, Продуктивность, Видео, Длиннопост

Готово.

Отменить действие можно при помощи Пиктограммы с молнией:

Excel понятным языком: мгновенное заполнение Excel, Ms Office, Маркетинг, Отдел кадров, Аналитика, Бухгалтерия, Офис, Продуктивность, Видео, Длиннопост

Мгновенное заполнение работает и в Умных таблицах.

Важно:

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

- идеально работает, если данные однотипные, например ФИО;

- ошибка или опечатка при наборе образца может привести к ошибкам заполнения итогового столбца или функция не сработает;

- всегда проверяйте полученные результаты;

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

Не работает Ctrl+E???

Чтобы включить, выберите Файл ► Параметры ► Дополнительно ► Параметры правки и установите флажок Автоматически выполнять мгновенное заполнение:

Excel понятным языком: мгновенное заполнение Excel, Ms Office, Маркетинг, Отдел кадров, Аналитика, Бухгалтерия, Офис, Продуктивность, Видео, Длиннопост

Еще интересное по теме Excel:

Трюки с листами книги

Быстро удалить все картинки с листа

Быстрое перемещение строк и столбцов

Сводные таблицы в Excel: как создать?

Суммирование в Excel

Поиск и удаление повторяющихся значений в Excel
Показать полностью 3
102

Excel Power Query: создание основных запросов

Короткое видео ⬇⬇⬇

В Power Query можно создавать различные виды запросов и подключений к данным:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Рассмотрим самые часто используемые: с листа книги (таблицы/диапазона), из книги, из папки, из Google Таблицы.

Названия вкладок могут отличаться в зависимости от версии Excel.
Для Excel версии до 2016 надстройка расположена на отдельной вкладке Power Query.

Создание запроса к данным листа книги


Откройте лист Excel с данными:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Вкладка Данные ► Получить данные ► Из других источников ►Из таблицы/диапазона:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Автоматически создастся "Умная таблица":

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Нажмите ОК, откроется окно редактора Power Query:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

В окне Параметры запроса ►Свойства ► Имя, можно изменить название запроса.


Загрузите запрос, окно редактора запросов, Главная ►Закрыть и загрузить ► Закрыть и загрузить в...:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

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

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

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


Вкладка Данные ► Получить данные ► Из файла ► Из книги:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

В открывшемся окне укажите путь к файлу и нажмите Импорт:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

В окне Навигатор выберите Лист или Таблицу, нажмите Преобразовать данные:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Загрузите запрос.


Создание запроса из папки


Вкладка Данные ► Получить данные ► Из файла ►Из папки:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

В открывшемся окне укажите путь к папке и нажмите ОК:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Нажмите Преобразовать данные:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

В редакторе удалите все столбцы, кроме двух первых, для этого выделите лишние столбцы зажав SHIFT, правая кнопка мыши по шапке столбца (пкм) ► Удалить столбцы:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Создайте пользовательский столбец, вкладка Добавление столбца ► Настраиваемый столбец, прописав в нём формулу Excel.Workbook([Content]):

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

В созданном столбце, выберите вариант Data, уберите галку Использовать исходное имя столбца как префикс ► ОК:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Разверните столбец:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Преобразуйте названия строк в заголовки столбцов, Главная ► Использовать первую строку в качестве заголовков:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Удалите повторяющиеся заголовки, используя фильтр, сняв галку:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Удалите лишние столбцы, пкм ► Удалить столбцы;

Загрузите запрос.


Создание запроса из Google Таблиц


Копируйте ссылку на файл в настройках доступа:

Из примера: https://docs.google.com/spreadsheets/d/1-oZB45_CfT4leIA6DrIP...
Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Откройте Excel;

Создайте запрос, Данные ► Получить данные ►Из других источников ► Из интернета:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Укажите путь к файлу, измените окончание ссылки с /edit?usp=sharing на /export и нажмите ОК:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

В окне Навигатор выберите Лист и нажмите Преобразовать данные:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Загрузите запрос.


Проверить, что получилось: выберите в окне Запросы и подключение ► пкм ► Загрузить в...:

Excel Power Query: создание основных запросов Excel, Ms Office, Офис, Продуктивность, Маркетинг, Бухгалтерия, Отдел кадров, Аналитика, Видео, Длиннопост

Файл с запросами из статьи

Показать полностью 22
258

Power Query: мощь и простота работы с данными в Excel

"Ручной привод" в работе с данными, частое явление. Многие пользователи Excel, обрабатывают данные "привычным" для себя способом, с минимальной автоматизацией, тратя кучу времени. Мало, кто слышал и использует волшебный инструмент — Power Query.

Power Query: мощь и простота работы с данными в Excel Excel, Аналитика, Офис, Продуктивность, Ms Office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

Почему Power Query?


Power Query — технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять, преобразовывать и уточнять данные из различных источников для последующего анализа. Функции Power Query доступны в Excel и Power BI.


Аргументы ЗА изучение надстройки:


1. Простой способ преобразовать данные, без использования формул и сводных таблиц;

2. Быстрый способ, вы можете много сделать с данными, в несколько кликов мыши;

3. Разовая настройка, сформируйте запрос один раз и обновляйте его, когда происходит изменение данных в источнике, или настройте автоматическое обновление.


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


Используя надстройку, вы сможете быстро:


1. Загружать данные из широкого спектра источников и подключаться к ним:

Power Query: мощь и простота работы с данными в Excel Excel, Аналитика, Офис, Продуктивность, Ms Office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

2. Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML...), по одному или несколько за раз, например из всех файлов указанной папки или непосредственно с листа(-ов) книги;

3. Выполнять слияние источников данных для дальнейшего анализа и моделирования с помощью Power Pivot и PowerView;

4. Выполнять очистку данных от мусора;

5. Причёсывать данные: исправлять регистр, числа-как-текст, разбирать текст на столбцы и склеивать обратно, делить дату на составляющие (год, квартал, месяц, день недели...) и т.д.;

6. Настраивать представление таблиц: используя фильтры, сортировку, изменение порядка столбцов, транспонирование, добавление итогов, разворачивая кросс-таблицы в плоские и сворачивать обратно;

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


Power Query: где искать, как установить?


Для Excel 2016, 2019 или Office 365: надстройка уже находится на вкладке Данные ► Получить и преобразовать:

Power Query: мощь и простота работы с данными в Excel Excel, Аналитика, Офис, Продуктивность, Ms Office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

Для версий 2013 и 2010: загрузите надстройку (официальный сайт Microsoft) выбрав версию, подходящую для вашего устройства. Как только вы загрузите файл, откройте его и следуйте инструкциям.


После этого автоматически откроется вкладка POWER QUERY на ленте:

Power Query: мощь и простота работы с данными в Excel Excel, Аналитика, Офис, Продуктивность, Ms Office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

Если вкладка не появилась, вам нужно ее отобразить:


1. Перейдите на вкладку Файл ► Параметры ► Надстройки;

2. В опциях Надстройки выберите Надстройки COM, нажмите Перейти;

3. Отметьте галочкой Microsoft Power Query for Excel ► ОК, вкладка появится на ленте.


Редактор запросов


Окно редактора запросов, содержит следующие элементы:

Power Query: мощь и простота работы с данными в Excel Excel, Аналитика, Офис, Продуктивность, Ms Office, Бухгалтерия, Отдел кадров, Маркетинг, Длиннопост

1. Лента редактора запросов: Файл, Главная, Преобразование, Добавление столбца, Просмотр;

2. Запросы — окно с перечнем созданных запросов, можно свернуть / развернуть;

3. Строка формул, можно отобразить или скрыть в меню Просмотр ► Панель формул;

4. Сетка предварительного просмотра, в которой выводятся результаты каждого шага запроса;

5. Меню для редактирования данных, открывается при нажатии на шапку столбца правой кнопкой мыши;

Панель параметры запроса:

6. Свойства — редактируемое поле названия запроса;

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


Power Query — запросы, которые может создавать любой, указывая системе, куда обратиться и какие действия выполнить. Команды записываются на языке М. Язык не требует знаний и навыков программиста: код генерируется автоматически. При помощи мыши вы можете решать почти все задачи, стоящие перед вами. Но иногда запрос нужно все-таки поправить, еще реже – написать полностью вручную.


Далее, выйдет серия статей о работе в Power Query, подписывайтесь, чтобы быть в курсе.

Показать полностью 4
688

Excel понятным языком: трюки с листами книги

Статья для новичков и интересующихся Excel.


Содержание:


1. Быстрое копирование листа;

2. Копирование листа в другую книгу;

3. Как убрать линии сетки;

4. Изменение цвета ярлычка;

5. Быстрый подбор ширины столбца (высоты строки);

6. Скрытие и отображение листов;

7. Защита от изменения структуры книги;

8. Увеличение отображения данных (масштабирование);

9. Сравнение данных листов двух книг;

10. Закрепление областей листа книги.


Короткое видео ⬇⬇⬇

Быстрое копирование листа


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


Копирование листа в другую книгу


Выделите ярлык, правая кнопка мыши (пкм) ► Переместить или скопировать:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

в открывшемся окне, выберите лист для копирования, поставьте галку Создать копию, выберите книгу для перемещения (уже открытая или новая) ► ОК:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Как убрать линии сетки


В строке меню, на вкладке Вид панели инструментов, в разделе (от версии Excel: Отображение, Показать, Показать/скрыть), уберите галку Сетка:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Изменение цвета ярлычка


Выделите ярлык ► пкм, в открывшемся окне ► Цвет ярлычка:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Быстрый подбор ширины столбца (высоты строки)


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


Скрытие и отображение листов


1. Скрытие: Выделите ярлык, пкм ► Скрыть:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

2. Отображение: Выделите ярлык, пкм ► Показать (в открывшемся окне, выберите нужный лист):

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Защита от изменения структуры книги


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


Панель инструментов Рецензирование ► (Защита) Защитить книгу (пароль не обязателен):

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Увеличение отображения данных (масштабирование)


Зажмите CTRL, вращением колеса мыши, от себя или на себя, меняйте размер отображения данных.


Быстро вернуть размер 100%: панель инструментов Вид ► (Масштаб) 100%:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Сравнение данных листов двух книг


Панель инструментов Вид ► (Окно) Рядом:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост
Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Изменить расположение окон: Вид ► (Окно) Упорядочить все:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Варианты расположения окон:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Синхронная прокрутка листов: Вид ► (Окно) Синхронная прокрутка:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Закрепление областей листа книги


В строке меню, на вкладке Вид панели инструментов, в разделе (Окно) Закрепить области, выбрать один из трёх вариантов закрепления:

Excel понятным языком: трюки с листами книги Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Для снятия закрепления, выберите Вид (Окно) ► Закрепить областиСнять закрепление областей.

Показать полностью 14
46

Когда ваш день рождения?

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

Когда ваш день рождения? Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Длиннопост

Как определить количество дней между датами?


Для определения количества дней между датами (от и до), в Excel необходимо вычесть большую дату из меньшей. Формат итоговой ячейки должен быть числовой. Ограничением являются операции с датами до 1 января 1900 г., так повелось исторически. Почему? Microsoft их знает.


Допустим человек родился 04.07.1984, сегодня 07.06.2020, сколько дней человек прожил?


=A2-B2, где A1 конечная дата, B2 начальная дата, ответ 13 122 дня.


Чтобы посчитать количество дней между датами:

=A2-B2-1

Когда ваш день рождения? Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Длиннопост

Как определить возраст человека?


Для определения возраста, нам понадобится функция ДОЛЯГОДА, которая возвращает часть года, то есть количество целых дней между двумя датами (начальной и конечной).


= ДОЛЯГОДА(нач_дата;кон_дата;[базис]), где:

Нач_дата — начальная дата.

Кон_дата — конечная дата.
Базис — используйте 1, Excel делит фактическое количество дней в месяце на фактическое количество дней в году.

Модернизируем формулу, чтобы на выходе было целое число:


=ЦЕЛОЕ(ДОЛЯГОДА(B2;СЕГОДНЯ();1)), где B2 — день рождения человека.


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


=РАЗНДАТ(нач_дата,кон_дата,единица)
функция возвращает разницу в годах, месяцах и днях, в зависимости от параметра, который вы задаете в аргументе (единица):

Y - возвращает количество лет.

M - количество месяцев.
D - количество дней.
YM - возвращает месяцы, игнорируя дни и годы.
MD - разница в днях, игнорируя месяцы и годы.
YD - разница в днях, игнорируя годы.

=РАЗНДАТ(B2;СЕГОДНЯ();"Y"), где B2 — день рождения человека.


Расчёт в днях, месяцах и годах, немного усложним формулу:


=РАЗНДАТ(B2;СЕГОДНЯ();"Y")&" л. "&РАЗНДАТ(B2;СЕГОДНЯ();"YM")&" мес. "&РАЗНДАТ(B2;СЕГОДНЯ();"MD")&" д."


Сделаем совсем красиво:


=ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();"Y");РАЗНДАТ(B2;СЕГОДНЯ();"Y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();"Y")-11;100);9);10);"[<1]\го\д;[<4]\го\да;лет")&" ";)& ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();"YM");РАЗНДАТ(B2;СЕГОДНЯ();"YM")&" меся"&ТЕКСТ(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();"YM")-1; 11);"[<1]ц;[<4]ца;цев")&" ";)& ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();"MD");РАЗНДАТ(B2;СЕГОДНЯ();"MD")&" д"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();"MD")-11;100);9); 10);"[<1]ень;[<4]ня;ней");)

Когда ваш день рождения? Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Длиннопост

Сколько вам будет лет в определенный год?


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


=РАЗНДАТ(B2;ДАТА(A2;1;1);"Y"), где B2 — день рождения человека, А2 год на который производится расчет.

Когда ваш день рождения? Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Длиннопост

Сколько лет вам будет на определённую дату?


И в этом случае нам поможет функция РАЗНДАТ:


=ЕСЛИ(РАЗНДАТ(B2;A2;"Y");РАЗНДАТ(B2;A2;"Y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;A2;"Y")-11;100);9);10);"[<1]\го\д;[<4]\го\да;лет")&" ";)& ЕСЛИ(РАЗНДАТ(B2;A2;"YM");РАЗНДАТ(B2;A2 ;"YM")&" меся"&ТЕКСТ(ОСТАТ(РАЗНДАТ(B2;A2;"YM")-1; 11);"[<1]ц;[<4]ца;цев")&" ";)& ЕСЛИ(РАЗНДАТ(B2;A2;"MD");РАЗНДАТ(B2;A2 ;"MD")&" д"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;A2;"MD")-11;100);9); 10);"[<1]ень;[<4]ня;ней");), где B2 — день рождения человека, А2 дата на которую производится расчет.

Когда ваш день рождения? Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Длиннопост

Узнаем дату, когда человек достигнет N лет


Предположим, сотрудник родился 15.07.1988 года. Необходимо определить, когда ему исполнится 65 лет? Нам поможет функция ДАТА, которая возвращает порядковый номер определенной даты. В формулу необходимо ввести последовательно функции ГОД, МЕСЯЦ и ДЕНЬ.


Итоговая формула будет иметь вид:


= ДАТА(ГОД(B2)+65; МЕСЯЦ(B2); ДЕНЬ(B2)), где B2 — день рождения человека, ответ 15.07.2053


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

Так можно посчитать дату окончания испытательного срока, прибавив дни, а не годы к ДЕНЬ.


Чтобы не писать формулы самостоятельно, копируйте их в свой файл и замените ссылки в формулах на ссылки с вашими данными.

Показать полностью 4
97

Как построить график или диаграмму из Сводной таблицы

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

Как построить график или диаграмму из Сводной таблицы Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Создать визуализацию такой таблицы в виде графика или диаграммы очень просто:


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

2. Выберите в строке меню Вставка ► Сводная диаграмма:

Как построить график или диаграмму из Сводной таблицы Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

или на вкладке Анализ ► Сводная диаграмма.


3. В открывшемся окне, выберите один из вариантов представления, нажмите OK:

Как построить график или диаграмму из Сводной таблицы Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост
Совет: изначально выбирайте верный тип диаграммы и не мудрите с дизайном.
Как построить график или диаграмму из Сводной таблицы Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

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


Для перемещения, выделите диаграмму, выберите на вкладке Конструктор (Анализировать) ► Переместить диаграмму:

Как построить график или диаграмму из Сводной таблицы Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

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

Как построить график или диаграмму из Сводной таблицы Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Таблица, Видео, Длиннопост

Можно задать название нового листа с диаграммой, написав его в поле правее варианта расположения.

Плюсом сводных диаграмм, является динамичность и интерактивность, при совместном использование с Временными шкалами и Срезами. Такие диаграммы можно использовать для создания Дашбордов (Dashboard) в Excel.

Показать полностью 4 1
Похожие посты закончились. Возможно, вас заинтересуют другие посты по тегам: