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


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

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


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

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

+1
Хм. Тот случай, когда проще написать макрос на пару строк, а не вот это всё.
+1

Я и в уме посчитаю, чем столько букв писать...

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

На массиве из 1000 сотрудников быстро посчитать стаж, даты прохождения медосмотра выход на пенсию, просто возраст) а так то да, счет в уме хороший навык

0
Я не знаю как это в русском приложении, но в английском есть отличная функция Datedif, которая считает разницу между датами в годах, месяцах, днях.. В списке функций ее однако нет, не ищите, но она все же работает)
раскрыть ветку 1
0
Это разндат на русском
0
Узнаем дату, когда человек достигнет N лет
Предположим, сотрудник родился 15.07.1988 года. Необходимо определить, когда ему исполнится 65 лет? Нам поможет функция

Да лан??! 15.07.1988+65 = 15.07.2053.


Но я прикалываюсь. Тут будет засада, когда захочется узнать 10000 дней и такого типа "юбилейные даты".

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

Аж пот прошибло, думал ошибся)))

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

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

0
Аааааааа!!
-2
Автор, че ты тут хуету развел? Пишешь дату рождения в ячейку, рядом текущюю дату и разница в днях =А2-А1
а там хоть в месяца хоть в года дели.
Похожие посты
210

Ролловер эффект в Excel

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

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


Итак приступим. Для начала создаём файл с поддержкой макросов. Затем на вкладке «Формулы» - «Диспетчер имён» задаём имя для диапазона ячейки А1, например DataА1:

Ролловер эффект в Excel Excel, Vba, Таблица, Полезное, На заметку, Видео, Гифка, Длиннопост

Далее нажимаем клавиши Alt+F11 и кликнув правой клавишей мыши в левой части экрана создаём новый модуль, куда вписываем маленький макрос:

Public Function MouseMove(Str$, Data$)
Application.Evaluate(Data) = Str
End Function
Ролловер эффект в Excel Excel, Vba, Таблица, Полезное, На заметку, Видео, Гифка, Длиннопост

Теперь в ячейке А2 вводим формулу =ГИПЕРССЫЛКА(MouseMove(1;"DataA1"))

Ролловер эффект в Excel Excel, Vba, Таблица, Полезное, На заметку, Видео, Гифка, Длиннопост

У вас выйдет ошибка, не пугайтесь, её можно спрятать формулой =ЕСЛИОШИБКА(ГИПЕРССЫЛКА(MouseMove(1;"DataA1"));1)

Ролловер эффект в Excel Excel, Vba, Таблица, Полезное, На заметку, Видео, Гифка, Длиннопост

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


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

Ролловер эффект в Excel Excel, Vba, Таблица, Полезное, На заметку, Видео, Гифка, Длиннопост

Пришло время навести окончательную красоту, для этого выделяем нашу таблицу без шапки (А1), на вкладке «Главная» - «Условное форматирование» применяем второе правило «Форматировать только ячейки, которые содержат». Выбираем «равно» и указываем нашу ячейку «А1». В «Формат» задаём цвета и стили границы, нажимаем «Ok» и наслаждаемся ролловер эффектом))

Ролловер эффект в Excel Excel, Vba, Таблица, Полезное, На заметку, Видео, Гифка, Длиннопост
Показать полностью 6
12170

Самая полезная клавиша при работе с документами Word, Excel - F4

Хочу посвятить пост всего одной, но очень важной при работе с офисными программами, кнопке на клавиатуре - "F4"

Самая полезная клавиша при работе с документами Word, Excel - F4 Ms Office, Офис, Microsoft Word, Excel, Лайфхак, Горячие клавиши

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


Что же она делает? Всего-навсего повторяет последнее действие.


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


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


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


Вдруг кто-то этого не знал и ему это теперь пригодится)

191

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

Функция СУММЕСЛИМН суммирует все аргументы, удовлетворяющие нескольким условиям.

Например, можно найти сумму продаж менеджеров по месяцам.

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

Формула:

=СУММЕСЛИМН(диапазон_суммирования (ЧТО СУММИРУЕМ); диапазон_условия1 (ГДЕ ИЩЕМ УСЛОВИЕ 1); условие1 (ЧТО ИЩЕМ В ДИАПАЗОНЕ 1) ; [диапазон_условия2; условие2]; …)

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

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

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

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

Условия могут вводится в виде: 52, ">45", A2, "текст" или "22".
Диапазон_условия1 и Условие1 составляют пару, определяющую, к какому диапазону применяется условие при поиске. Соответствующие значения найденные в этом диапазоне ячеек суммируются в пределах аргумента Диапазон_суммирования.

Аргумент_n Диапазон_условия2, Условие2, … (необязательный аргумент) дополнительные диапазоны и условия для них.


Можно ввести до 127 пар диапазонов и условий.

Совет: ошибки могут возникать при протягивании формулы, поэтому закрепляйте диапазоны и условия используя клавишу F4.

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

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

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

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

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

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

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

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

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

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

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

Как в Excel из кросс-таблицы сделать плоскую

152

Как в Excel из кросс-таблицы сделать плоскую

В этой статье разберем практическую задачу.

Как из кросс-таблицы:

Как в Excel из кросс-таблицы сделать плоскую Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Маркетинг, Видео, Длиннопост
Кросс-таблица (Сводная таблица) – способ визуализации данных, в котором они упорядочены по строкам и столбцам. Строкам соответствует одна группа данных, столбцам другая, их пересечение содержит информацию, объединяющую их.

Получить плоскую:

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

Выполнить такую магию без COPY/PASTE можно при помощи Макроса или надстройки Power Query.

Видео на тему ⬇⬇⬇

Макрос

1. Откройте редактор VBA: ALT+F11;

2. Создайте новый модуль Insert ► Module:

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

3. Вставьте макрос:

Sub ПреобразованиеТаблиц()
Dim OutputRng As Range
Dim InputRng As Range
Dim out_row As Long, out_col As Long
Dim in_col As Long, in_row As Long
Set InputRng = ActiveCell.CurrentRegion
Set OutputRng = Application.InputBox(prompt:="Выберите ячейку для вывода новой таблицы", Type:=8)
OutputRng.Range("A1:C1") = Array("Столбец1", "Столбец2", "Столбец3")
out_row = 2
out_col = 2
For in_row = 2 To (InputRng.Rows.Count - 1) * (InputRng.Columns.Count - 1) + 1
For in_col = 1 To 3
If in_col = 1 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(out_row, 1)
If in_col = 2 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(1, out_col)
If in_col = 3 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(out_row, out_col)
Next in_col
out_col = out_col + 1
If out_col = InputRng.Columns.Count + 1 Then
out_col = 2
out_row = out_row + 1
End If
Next in_row
End Sub
Как в Excel из кросс-таблицы сделать плоскую Excel, Аналитика, Отдел кадров, Бухгалтерия, Офис, Продуктивность, Ms Office, Маркетинг, Видео, Длиннопост

4. Сохраните книгу с поддержкой макросов;

5. Создайте кнопку для вызова макроса Разработчик ► Вставить:

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

6. Назначьте на кнопку макрос:

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

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

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

Готово.

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

Power Query

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

2. Выделите первый столбец, нажмите пкм на шапке столбца ► Отменить свертывание других столбцов:

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

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

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

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

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

5. Готово.

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

Полезно? Пишите в комментах, следующую тему.

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

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

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

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

Формула:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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
574

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

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

Шаги:

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

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

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

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

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

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

Готово.

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

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
199

Три способа перевернуть таблицу в 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
125

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

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

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

200

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

457

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
103

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
544

Количество оставшихся дней до контрольной даты

В этой статье мы рассмотрим пример использования функции ВПР для подсчёта оставшегося количества дней до заданной даты.


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

Количество оставшихся дней до контрольной даты Excel, Сроки, Таблица, Впр, Полезное, На заметку

=-ВПР(;B2-ДАТА(ГОД(B2)+{1:0};МЕСЯЦ(A2);ДЕНЬ(A2));1)

=-VLOOKUP(;B2-DATE(YEAR(B2)+{1:0};MONTH(A2);DAY(A2));1)


Рассмотрим формулу подробнее:

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

Описание из справки формулы ВПР: «Интервальный_просмотр — это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение».

- Найденное значение это и есть количество дней до контрольного дня, но со знаком минус. Знак минус перед ВПР() меняет знак результата.

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


Также есть другой вариант этой же формулы:

Количество оставшихся дней до контрольной даты Excel, Сроки, Таблица, Впр, Полезное, На заметку

=-ВПР(;B1-(МЕСЯЦ(A1)&{-1:0}-ГОД(B1))-ДЕНЬ(A1)+1;1)

=-VLOOKUP(;B1-(MONTH(A1)&{-1:0}-YEAR(B1))-DAY(A1)+1;1)


Кроме того, данный приём можно использовать для подсчёта оставшихся дней до дня рождения, применив в столбце «Текущая дата» формулу =СЕГОДНЯ()

Количество оставшихся дней до контрольной даты Excel, Сроки, Таблица, Впр, Полезное, На заметку

Формулы взяты отсюда, пост оформлен мною.

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

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
642

Сортировка по дням рождения в Excel

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


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

Сортировка по дням рождения в Excel Excel, Таблица, День рождения, Сортировка, Полезное, На заметку, Длиннопост

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


В соседнем столбце используем функцию ТЕКСТ (TEXT), которая представляет числа и даты в заданном формате: =ТЕКСТ(B4;"ММ ДД")

Сортировка по дням рождения в Excel Excel, Таблица, День рождения, Сортировка, Полезное, На заметку, Длиннопост

Формат «ММ ДД» означает, что нужно из всей даты отобразить только номер месяца и день.


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

Сортировка по дням рождения в Excel Excel, Таблица, День рождения, Сортировка, Полезное, На заметку, Длиннопост

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


Выделяем весь список (кроме заголовка) и на вкладке Главная выбираем - Условное форматирование - Создать правило (Home - Conditional formatting - Create Rule). В появившемся окне выбираем - Использовать формулу для определения форматируемых ячеек и вводим формулу: =МЕСЯЦ($B2)<>МЕСЯЦ($B3)


В разделе Формат на вкладке Границы (Borders) выбираем нижнюю границу ячейки, задаём понравившийся цвет линии и убираем лишние знаки доллара в формуле, чтобы закрепить в ней только столбцы.

Сортировка по дням рождения в Excel Excel, Таблица, День рождения, Сортировка, Полезное, На заметку, Длиннопост

Также можно на вкладке Вид выбрать Закрепить областиЗафиксировать верхнюю строку и ввести формулу =СЕГОДНЯ() для ежедневного обновления даты и визуального удобства.


P.S. При создании таблицы ни один шрифт не пострадал, только глаза)))

Шрифты для друзей _Arabian, a_Algerius, WienLight, Benguiat Rus, Romic

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

ВПР и числа-как-текст

Обычные числа и числа-как-текст (т.е. числа, которые только выглядят как числа, а, по-сути, являются текстом) - это причина многих проблем и сложностей при работе с данными в Microsoft Excel. Одна из подобных ситуаций - использование функции ВПР (VLOOKUP) для поиска и подстановки, когда в исходных данных есть эти пресловутые числа в текстовом формате.


Рассмотрим классический пример - подстановку цен из прайс-листа в таблицу заказов по совпадению артикулов:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

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


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


Примечание: Различить нормальные и текстовые числа не всегда легко. Иногда на таких ячейках появляется зелёный уголок-индикатор, иногда - нет. В этой статье, для наглядности, я буду выравнивать числа-как-текст по левому краю, а нормальные числа - по правому.


Вариант 1. Числа-как-текст в искомых значениях


Предположим для начала, что псевдочисла эпизодически встречаются у нас в искомых значениях, т.е. в таблице заказов (диапазон B4:B7) и перемешаны с нормальными числовыми артикулами, что приводит к появлению ошибок.


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

- умножения или деления на 1

- прибавления или вычитания 0

- двойного знака минус перед артикулом (равносильно двойному умножению на -1)


Значение артикула от выполнения такой безобидной математической операции никак не изменится, но сам факт её выполнения заставит Excel воспринимать артикул именно как число. А значит и ВПР найдет текстовые значения без проблем:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Вариант 2. Числа-как-текст в таблице, где ищем


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

Самым простым и компактным вариантом будет приклеивание к артикулу пустой строки:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

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


Вариант 3. Пропадание начальных нулей


Частным случаем предыдущего примера бывает сценарий, когда артикулы в прайс-листе не просто текстовые, но ещё и содержат нули в начале строк, дополняющие их до заданной разрядности. Тут поможет функция ТЕКСТ (TEXT), которая умеет любое число выводить как текст по заданному шаблону:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Вариант 4. Числа-как-текст вперемешку с числами в обеих таблицах


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

Звучит страшно, но решается легко - нужно просто скомбинировать первый и второй способы, вложив их в функцию ЕСЛИОШИБКА (IFERROR). Эта функция прокачает обе версии ВПР - "текстовую" и "числовую" - и выдаст ту, которая не приводит к ошибке #Н/Д:

ВПР и числа-как-текст Excel, Впр, Таблица, Полезное, На заметку, Длиннопост

Вот и всё - и никаких больше ошибок :)

Источник

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