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, подписывайтесь, чтобы быть в курсе.

Найдены возможные дубликаты

+3
А другие юзеры без этой программки смогут с файлом работать?
раскрыть ветку 9
0

Ставьте сразу power bi - там и power query и нормальная визуалка и dax язык в придачу.

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

А нет ли ссылочки на путевый гайд для начинающих по PowerBI, а то в инсте часто рекламят всякие курсы, но за оверпрайс??

раскрыть ветку 7
0
Только Adodb Connection/Recordset, только хардкор)
раскрыть ветку 4
0

можно поподробнее?

раскрыть ветку 3
+1
В vba есть возможность устанавливать соединения с практически любыми файлами, базами данных, для этого используется класс Adodb, в котором есть 2 ключевых объекта, connection и recordset, первый - это канал, по которому идут данные, а второй - так называемый курсор, который считывает данные уже посредством использования языка sql. Единственное что требуется - для установки соединения connection нужен connectionstring, это так называемая строка подключения, которая определяет параметры с которым connection "стучится" в бд ( если требуется с логином и паролем). Сделав sql запрос в recordset, можно вставить данные на лист одной командой типа range("a2").copyfromrecordset, только вот шапку данных придется отдельно вставлять, используя данные recordset.fields(номер столбца).name. Либо данные можно получить в массив - array = recordset.getrows, только надо помнить, что данные в массиве будут перевёрнуты, координаты: array(номер столбца, номер строки), в обычных массивах разметка идёт наоборот. По сути все это надстройки - это обертки для работы с этими инструментами, а я предпочитаю делать это самостоятельно и явно. К тому же становятся возможными такие операции типа сделать sql запросы ко всем файлам в определенной директории. Тема большая, описал вкратце
раскрыть ветку 2
0

Спасибо, автор!

Пиши ещё. Как раз занимаюсь от изучением этой надстройки

0

это грубо говоря сводные таблицы навороченные.

или бесплатный BI/OLAP наоборот, упрощенный.

очень неплохой аналог QlikView


только не помню, умеет ли она ЗАПИСЫВАТЬ В БАЗУ то что накреативил юзер.

получить данные, отобразить их, всякие виртуальные таблицы, пользовательские поля "в памяти" это все хорошо, а можно ли это обратно записать?

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

А сводные таблицы/OLAP  может? Нет.

Это для аналитики, отображения.

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

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

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

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

Формула:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel понятным языком: функция ВПР (вертикальный просмотр)

Практика показывает, более 85% вакансий работодателей имеют запрос "знание программы Excel", 40% из которых - "углубленное знание Excel".

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

Вопрос на собеседовании:
Вы умеете ВПэрить?
ЧАВО???

Давайте разбираться.

6 минут, видео на тему ⬇⬇⬇

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


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

Формула:

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

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

Аргумент_1 Искомое_значение, значение, которое вам нужно найти;

Аргумент_2 Таблица, диапазон с искомым значением, в котором находится искомое значение;

Аргумент_3 Номер_столбца, содержащий возвращаемое значение, считается от искомого значения, влево;

Аргумент_4 Интервальный просмотр (не обязательный): 1/ИСТИНА приблизительное совпадение, 0/ЛОЖЬ, точное совпадение возвращаемого значения. По умолчанию всегда 1/ИСТИНА.

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

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

Вкладку Формулы ► Ссылки и массивы

Введя в строку =ВПР(.

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

Пример

Необходимо найти цену продукта по наименованию:

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

Введём формулы:

=ВПР(H4; $B$4:$E$10;4;0) - точный поиск

=ВПР(H4;$B$4:$E$10;4;ИСТИНА) - Приблизительный

ВНИМАНИЕ: искомый столбец Цена четвертый, а не пятый, т.к. отсчёт идет от самого левого столбца с Искомым_значением.
Точный поиск выдает верное значение, а приблизительный выводит значение из последней строки, т.к. список не отсортирован по алфавиту.

Сортируем исходную таблицу по алфавиту:

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

Готово. Подробнее про ограничения читайте ниже.

Именованные диапазоны

Именованные диапазоны — отличный инструмент. Позволяют присвоить имя ячейке или диапазону с данными и в дальнейшем ссылаться на него вместо того, чтобы указывать громоздкие координаты (A2:B1000), делая формулы понятнее.

Создать именованный диапазон, можно на вкладке Формулы ► Задать имя:

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

В открывшемся окне, задайте имя диапазона и укажите сам диапазон:

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

Посмотреть все созданные диапазоны или отредактировать их можно на вкладке Формулы ► Диспетчер имен:

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

Используя созданный диапазон напишем формулу =ВПР(G3;Таблица;4;0)

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

Ограничения

Не может искать влево

ВПР может искать значения только в крайнем левом столбце. В случае неверной ссылки формула выдаст ошибку #Н/Д.

Настройте формулу таким образом, чтобы она ссылалась на правильный столбец, если это невозможно, попробуйте переместить столбцы или используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ.

Если, значение повторяется, функция выведет только первое найденное значение

Вам нужны повторяющиеся данные в списке? Если нет – удалите их, при помощи кнопки Удалить дубликаты на вкладке Данные.


Нужно оставить дубликаты? Для таких случаев отлично подойдёт Сводная таблица, позволяющая выбрать значение и посмотреть результаты.

ВПР не чувствительный к регистру

Добавили или удалили столбец из таблицы, все сломалось

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

Используйте в формуле ИНДЕКС+ПОИСКПОЗ, так Вы раздельно зададите столбцы для поиска и извлечения данных, в результате можете удалять или вставлять сколько угодно столбцов, без возникновения ошибок.

Ссылки на ячейки съехали при копировании или протягивании формулы

Используйте абсолютные ссылки на ячейки при записи диапазона, например $A$1:$D$100 или $A:$D. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

Ошибки

1. Включен точный поиск (0/ЛОЖЬ), но искомого значения (Аргумент_1) нет в диапазоне поиска или он написан с опечаткой;

2. Включен приблизительный поиск (1/ИСТИНА), но таблица, в которой происходит поиск не отсортирована по возрастанию наименований;Столбец поиска не является крайним левым;

3. Съехал Диапазон с искомым значением (Аргумент_2), ссылка на который должна быть абсолютной, нажмите внутри формулы на нём F4;

4. Функция не может найти нужного значения, потому в строке присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.);

Используйте текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ: =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(Аргумент_1));Аргумент_2;Аргумент_3;Аргумент_4).

5. Формат ячейки, откуда берется искомое значение и формат ячеек первого столбца таблицы отличаются (например, числовой и текстовый). Особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, коды, идентификаторы, даты и т.п.).

Подробнее статье ВПР и числа-как-текст.

Для преобразования числовых форматов данных в текст, используйте функцию ТЕКСТ : =ВПР(ТЕКСТ(Аргумент_1; " # ");Аргумент_2;Аргумент_3;Аргумент_4) или Аргумент_1&"".
Обратный вариант преобразования текста в число: --Аргумент_1, Аргумент_1*1 или Аргумент_1+0.
Универсальная формула для исправления ошибки числа, как текст: =ЕСЛИОШИБКА(ВПР(Аргумент_1*1; Аргумент_2;Аргумент_3;Аргумент_4;ВПР(Аргумент_1&""; Аргумент_2;Аргумент_3;Аргумент_4).

Убрать сообщения об ошибке #Н/Д (для версии Excel 2007+)

В случаях, когда функция ВПР не может найти совпадения, используйте функцию ЕСЛИОШИБКА.

Например, заменяет нулями: =ЕСЛИОШИБКА(ВПР(Аргумент_1;Аргумент_2;Аргумент_3;Аргумент_4);0).

Выводит пустое значение:

=ЕСЛИОШИБКА(ВПР( Аргумент_1;Аргумент_2;Аргумент_3;Аргумент_4 );"").

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

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

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

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

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

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

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

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

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

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

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

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

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

Шаги:

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

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

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

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

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

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

Готово.

Показать полностью 2
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, Бухгалтерия, Офис, Длиннопост

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

Показать полностью 5
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
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
109

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

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

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


Чтобы быстро выделить и удалить все с листа, нужно:


На вкладке меню Главная ► Найти и выделить ► Выделить группу ячеек:

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

В открывшемся окне выбираем вариант Объекты:

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

Нажимаем ОК.


Или


Нажать клавишу F5, в появившемся окне Переход, нажать кнопку Выделить:

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

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


Какой вариант использовать, дело вкуса.


Последний штрих, нажмите DELETE.

Для продвинутых:


Откройте Visual Basic (Alt + F11), копируйте и примените макрос:

Sub DelObject()

For Each i In ActiveSheet.Shapes
i.Delete
Next
End Sub
Показать полностью 2 1
392

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

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

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

Вот схема, как это быстро сделать:


1. Выделите строку или столбец;

2. Зажмите и удерживайте клавишу SHIFT;

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

4. Перетащите строку или столбец в необходимое место;

5. Отпустите.


Готово!


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

167

Суммирование в Excel сложение, мастер функций, автосумма, горячие клавиши

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

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


Сумму чисел можно найти двумя путями:

1. Сложением ячеек или констант,через знак "+" в формуле;

2. Используя встроенную функцию СУММ.


Функция СУММ(SUM) – математическая функция Microsoft Excel, позволяет складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трех видов.


Имеет вид:

=СУММ(число1;[число2];…), где:


Число1 (Обязательный аргумент), первое число для сложения. Может быть число 4 (константа), ссылка на ячейку, например, G6, или диапазон ячеек, например, A2:A10.

Число2 – 255 (Необязательный аргумент ), второе число для сложения. Можно указать до 255 чисел.


Функцию можно вызвать:


1. Выберите в строке меню Главная ► Редактирование ► Автосумма:

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

2. Строка меню Функции ► Библиотека функций ► Автосумма или в разделе Математические:

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

3. С помощью мастера функций, нажав на иконку в строке формул:

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

Окно мастера функций:

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

или в строке меню Функции ► Библиотека функций ► Вставить функцию:

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

4. Самый быстрый способ, сочетание клавиш ALT+ =


☝ Функция СУММ(SUM) не работает!?!


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


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


Коллега переслал файл, в котором суммы при протяжке формул не меняются?

Проверьте стоит ли автоматический пересчет в файле, на строка меню Формулы ► Параметры вычислений ► Автоматически.


В этом видео собраны все возможные варианты суммирования, от самых не продуктивных, до быстрых и эффективных ⬇⬇⬇

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

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

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

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

Давайте разберемся, как это сделать быстро.


Инструмент Удаление дубликатов


1. Выделите ячейку таблицы, в которой необходимо удалить дубликаты.

2. Выберите на вкладке Данные ►Удалить дубликаты:

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

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

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

В результате список сократится до уникальных значений:

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

Удалять дубликаты можно только по столбцам, если необходимо удалить по строкам, то сначала удалите по столбцам, затем транспонируйте данные:


1. Скопируйте данные или CTRL+С

2. Вкладка меню Главная ► Вставить ► Специальная вставка ► Траспонировать или вызов окна CTRL+ALT+V


Для корректной работы инструмента с полями формата Дата убедитесь, чтобы все поля имели одинаковый формат даты, например 01.01.2020


Удаление дубликатов при помощи Расширенного фильтра


Операцию выше можно сделать при помощи инструмента Расширенный фильтр.

Актуально, если у вас версия Excel 2003, в которой еще нет инструмента Удалить дубликаты.


1. Выделите шапку таблицы или всю таблицу (CTRL+A), в которой необходимо найти дубликаты.

2. Выберите на закладке Данные ► в разделе Сортировка и фильтр Дополнительно:

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

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

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

5. Поставьте галку Только уникальные записи:

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

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

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

Вам необходимо выделить уникальные значения в исходной таблице?

Скройте повторяющиеся значения при помощи Расширенного фильтра:

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

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

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

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

Поиск дубликатов при помощи Сводной таблицы


1. Добавьте в вашу таблицу дополнительное поле для проверки:

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

2. Создайте Сводную таблицу, вкладка Вставка ► Сводная таблица.

Настройте поля:

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

3. Выберите вариант отображения отчета, на вкладке Конструктор ► Макет отчета ► Показать в табличной форме.

4. Уберите промежуточные итоги Конструктор ► Промежуточные итоги:

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

Выделение повторяющихся значений при помощи условного форматирования для Excel 2007+


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

2. На вкладке Главная ► Условное форматирование ► Правила выделения ячеек ► Повторяющиеся значения:

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

3. В окне Повторяющиеся значения, при необходимости выберите формат выделения дубликатов:

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

Данные будут подсвечены:

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

Как удалить дубликаты и разбить текст по столбцам, смотрите видео ⬇⬇⬇

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

Применение Временной шкалы и Срезов в Excel

Использование стандартных фильтров в Excel не всегда удобно и ограничено одной таблицей. Для увеличения продуктивности в Excel существуют удобные инструменты, такие как Временна шкала и Срез. Они позволяют быстро выбирать данные по заданным параметрам, в том числе для нескольких таблиц сразу.

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

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


Сводная таблица является по сути конструктором используя который можно быстро собрать нужные показатели по заданным критериям. Такие таблицы удобно применять для построения различных отчетов. Подробнее о Сводных таблицах смотрите статью: Сводные таблицы в Excel: как создать?


Срез


Срез представляет собой фильтр, вынесенный в отдельный графический элемент. Добавить один или несколько Срезов, для "Умной" или Сводной таблицы, можно на вкладке Анализ ► Вставить срез:

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

В появившемся окне выберите столбец или несколько столбцов по которым будем построен фильтр:

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

Либо, на закладке Вставка ► Срез.


Внешний вид Срезов:

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

Временная шкала


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

Чтобы добавить шкалу на лист, выберите на вкладке Анализ ► Вставить временную шкалу:

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

Так же можно добавить через вкладку Вставка ► Временная шкала.


В открывшемся окне установите галку на против Дата ► ОK:

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

Внешний вид Временной шкалы:

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

Чтобы производить фильтрацию Временной шкалой:


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

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

2. Перетащите полосу прокрутки временной шкалы к периоду времени, который вы хотите выбрать;


3. В элементе управления отрезком времени нажмите левой кнопкой на плитку периода времени и зажав перетащите ее, будет выбран период.


Чтобы очистить Временную шкалу или Срез, нажмите на шкале в правом верхнем углу кнопку Очистить фильтр.


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


Настройка вида Временной шкалы и Среза


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


Чтобы переместить фигуру, просто перетащите ее в нужное место на листе.


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


Чтобы изменить стиль, нажмите на фигуру, отобразится меню Инструменты временной шкалы, выберите нужный стиль на вкладке Параметры.


Использование Временной шкалы и Срезов для нескольких Сводных таблиц


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


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

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

Для использования Временной шкалы для "Умной" таблицы преобразуйте её в Сводную, на вкладке Конструктор ► Сводная таблица или Вставка ► Сводная таблица. Это очень удобно и даёт больше вариантов для маневра.


Подробнее о Временной шкале и Срезах смотрите в видео ⬇⬇⬇

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

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

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

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


Разве данные в Excel имеющие структуру таблицы – это не таблица?
Отвечу вам: Нет.

То что вы считаете таблицей, представляет собой Рабочую область листа.


Таблица – это объект, имеющий свое название, внутреннюю структуру, свойства и преимущества по сравнению с обычным диапазоном ячеек.


Создание Таблицы


Для создания Таблицы встаньте на Рабочую область листа содержащую данные для создания таблицы и нажмите сочетание клавиш CTRL+T(L). В появившемся окне вы можете изменить диапазон выделения данных для построения таблицы, нажмите ОК.

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

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


Переименование Таблицы


В вашем файле планируется несколько Таблиц? Есть смысл присвоить для каждой Таблицы свое имя. Это облегчит их дальнейшее использование (например, при работе в Power Query или Power Pivot).


Для переименования установите курсор в любую ячейку Таблицы, в появившемся окне Конструктор в разделе Свойства введите новое имя в поле Имя таблицы:

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

Свойства Таблицы


1. Заголовки таблицы берутся из первой строки исходного диапазона.

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

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

2. В таблицу по умолчанию добавляется фильтр, который можно убрать на вкладке Конструктор или сочетание CTRL+SHIFT+L:

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

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


4. При добавлении в таблицу новых столбцов и строк они автоматически включатся в Таблицу. При наличии формул они автоматически применяются для новых данных.


5. Добавить строку итогов (суммирование, среднее и др.) для столбцов Таблицы можно сочетанием клавиш CTRL+SHIFT+T:

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

Настройка Таблицы


Изменить внешний вид Таблицы, вывести итоги, удалить дубликаты, создать Сводную таблицу, переименовать или удалить её можно на вкладке Конструктор.


Для использования Временной шкалы для Таблицы преобразуйте её в Сводную, на вкладке Конструктор ► Сводная таблица или Вставка ► Сводная таблица.


Преобразование Рабочей области в Таблицу является обязательным условием для использования возможностей Power Query и Power Pivot.

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