46

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1

Для начала - обнял, подбросил всех своих уже 56 фолловеров! Впечатлен, растроган, буду дальше для вас стараться)

И как раз один из вас @ashvud,  написал в Вологду-гдугдугду вот о такой проблеме:

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

Скажу сразу - долго не бился, на тест ушло чуть более минуты, так как пример был очень относительный - решение будет тоже относительное, но, если бы столкнулся сам, то решил бы ИЛИ регуляркой по заглавным, точнее по чередованию строчнаяЗАГЛАВНАЯ (в кодировке это разные символы, вот по ним бы и да) с последующей заменой, или скачиванием базы имен и тупо поиску по строке с заменой при нахождении вот по такой маске:

ИМЯ -> ИМЯ+Разделитель

Но правильное и полностью рабочее решение - в конце рецепта, а пока погнали полуфабрикаты готовить..

Рецепт 2. Побеждаем кривое форматирование, неправильный перенос по строкам.

Открыли табличку и смотрим:

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

Ага, у нас есть подсказка - если заполнена строка в столбце A - то это начало ФИО в столбце B. Для примера - ячейка A5 - в ней начинается новая часть ПОЛНОЙ строки.

А еще строка А5 намекает на то, что в строке B4 содержится последние символы из "кривого куска".

Что нам это дает? Да собственно решение всей проблемы)

ШАГ А.  ДОБАВЛЯЕМ столбец между существующими A и B, который будем использовать как технический:

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

В ячейку B2 вбиваем следующее:

=IF(A2<>"";"WUT";"")

Это мы проверяем - если значение в столбце А указано - ставим технический символ, а если нет - не ставим. Тем самым мы обозначаем как и окончание полной ПРАВИЛЬНОЙ строки, так и начало полной правильной строки.

Для простоты, кто не в курсе - наводим на нижний правый угол нужной ячейки (B2 в нашем случае), появляется черный крестик. На него два раза - и автозаполнение пройдет до последней заполненной строки справа или слева. (где последняя - туда и дойдет)

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных
Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

ШАГ Б. Поехали рвать вам пуканы в мой любимый текстовый редактор.Выделяем столбцы B, C, копировать, вставить в текстовый редактор.

Наша задача - удалить лишние переносы, при этом не убить текущие правильные пробелы. ОК ГУГЛ, не страшно)
Найти и заменить:

1. SPACE -> --

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

2. TAB -> (пусто, просто удалить делаем через найти и заменить)

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

3. Тыкаем вот сюда, выделяется кусок "пустоты". Выделяем его, копировать-вставить в найти.
ПУСТОТА - > (пусто, просто удалить делаем через найти и заменить)

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

4. Тыкаем в конец файла, жмем ДВАЖДЫ Enter. Получается вот так:

МАССИВ_ТЕКСТА_СЛИТНО

LUL

Выделяем LUL, жмем вверх, выделяется еще и пустая строка. Отлично, копируем получившееся (вместе с пустой строкой). Очищаем ОБЕ строки Найти / Заменить, вбиваем

WUT -> "

LUL"

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

5. LUL -> (пусто, просто удалить делаем через найти и заменить)

6. -- -> SPACE

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

Оп-па менты!

ШАГ В. Чтобы получившееся вернуть в Эксель ПОСТРОЧНО, возвращаемся в файл, и там такие применяем фильтр по строке "НОМЕР", убирая значения BLANKS.

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

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

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

Сохраняемся в конце уровня и просим маму принести полотенце, так как битва была жаркой))

Оставшееся слитные строки - правим руками, потому что мы уже автоматизировали более 90% работы менее чем за 2 минуты, а автоматизировать оставшиеся 10% займет уже дольше времени, чем руками. Потому не ленимся, помним - Эксель это чтобы БЫСТРО.
Ну а точнее - это можно поправить в самой обработке, используя шаги 1-6, просто обработки я не вижу, но автоматизировать на 100% заняло бы минуты 2-3.

Ну, собственно, вот как-то так)

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

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

+10
ТС, @ashvud, тут все гораздо проще)
Иллюстрация к комментарию
раскрыть ветку 8
+2

Ну тогда считаем так - мы пошли длинным путем))))

+2

Блять) Вот тут да, на самом деле так ГОРАЗДО проще) Не подумал об этом)

+1

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

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

Ты чего) способ рабочий - как реализован - не важно.
я себя знаешь как по лбу треснул, когда в первой же формуле написал 90% решения, а потом полез в блокнот?)))

+1

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

+1

И, опять-же, дело привычки... Я ни в коем случае не отрицаю, что я предлагаю самые простые и самые правильные решения. Просто что конкретно тогда в голову пришло - так и сделал.

Скажем так - определенный бэкграунд лично моего опыта мне подсказал путь через блокнот, про элементарщину с concat - вот "мамой клЯнусь слушай да" не подумал даже)

И спасибо за то, что вы первый, кто подкрепил слова - делом!)

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

(не утверждаю в смысле. потом перепрочел - вот что значит ночь не спал)))

0

Изящно! Именно снизу-вверх. В копилку опыта схоронил)

+2

Строк может быть от одной до 5-ти. Значит всего 5 разных формул Concatenate.

Я поступил бы по-другому:

копируем столбец с цифрами и заполняем пустые места такими же:

выделить столбец, Find and Select=>Goto special=> Blanks

выделяются все пустые. Далее нажать стрелку вверх (на клавиатуре), символ = и Ctrl+Enter.

Все пустые заполнены верхними.

Добавляю еще один столбец, в ячейке С2 формула =COUNTIF(D:D,D2), протягиваем до низу.

Добавляю фильтр по всем колонкам.

Сначала фильтр по столбцу С, например  по цифре 3

И для первой строки составляем формулу CONCATENATE

Теперь фильтр по столбцу B (с изначальной цифрой) и протягиваем.


Снять фильтры и повторить для других количеств строк.


Или же можно наконструировать и протянуть нечто со второго снимка:
=IF(B2,IF(C2=1,E2,IF(C2=2,CONCATENATE(E2,E3),IF(C2=3,CONCATENATE(E2,E3,E4),IF(C2=4,CONCATENATE(E2,E3,E4,E5),IF(C2=5,CONCATENATE(E2,E3,E4,E5,E6),"TOO MANY VALUES!!"))))),"")


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

Иллюстрация к комментарию
Иллюстрация к комментарию
раскрыть ветку 3
0

Вторая годнота! Прям другое дело)

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

А что ж не сделать рекурсию-то? Одна формула на все ячейки будет. Короткая.

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

Хм.
Чот мне это напомнило, как я однажды протупил, и, забыв о существовании в HTML такой (действительно весьма редко используемой) фичи, как связка тэгов map-area — написАл её аналог на js ))) Самое печальное — трое коллег видели, что я делаю, ни один не остановил, а потом на моё "ты чо, не мог мне напомнить про map, чтоб я целый час на эту херь не тратил?" один (веб-верстальщик, дохуя опыта, под тыщщу кейсов в портфолио) сказал, что первый раз о такой штуке слышит и спасибо мне за наводку на полезную фишку, которой он не знал, а двое сказали, что ничуть не сомневались, что я сознательно отказался от простого пути, потому что он меня чем-то не устроил.

Короче, автор, ты, имхо, сознательно самый сложный способ выбрал.


Отдельная просьба для любителей VBA и поблястать своими знаниями из школьной программы информатики - решить эту задачу за потраченные мною полторы минуты
Хм. Из ТЗ очевидно, что работа не разовая, человеку явно надо периодически эту операцию выполнять. А из этого уточнения становится очевидно, что разумнее один раз за 10 минут написать конвертор на том же VBA, который потом будет выполнять задачу автоматически за секунду, чем тратить "полторы минуты" каждый раз на ручную работу.
Учтите ещё, что человек уточняет: пример не полон, подобную операцию желательно проводить и с другими столбцами — что значительно усложняет вашу "ручную работу", а для вба-решения усложнение от этого допа ничтожно.

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

Вот, настряпал макрос. Вставьте код в стандартный модуль (Alt+F11, Insert - Module).

Выделяете диапазон, в котором надо убрать расползшиеся строки. Теперь нажимаем Tab, чтобы выбрать колонку, которая разделяет строчки. У автора это первая колонка («Номер»), но чаще всего это «Количество» где-то в конце. Когда нажимаете Tab, активная (светленькая) ячейка перемещается дальше по выделению.

Теперь вызываете (Alt+F8) макрос SelectionSimpleJoin.

Option Explicit
Sub SelectionSimpleJoin()
If Selection.Rows.Count = 1 Then
MsgBox "Выделите диапазон с данными. С помощью клавиши Tab укажите, на какую колонку ориентироваться при поиске начала «строки» (например, №п/п или количество), затем запустите этот макрос"
Exit Sub
End If
SimpleJoin Selection, ActiveCell.Column - Selection.Column + 1
End Sub
Sub SimpleJoin(rng As Range, indicator As Long)
' Собирает диапазон, в котором некоторые значения расползлись на несколько строк,
' ориентируясь на колонку № indicator
Dim colCount As Long: colCount = rng.Columns.Count
If indicator > colCount Then Exit Sub
If indicator = 0 Then Exit Sub
Const JOIN_SYMBOL = " " ' объединять строки в одну этим разделителем
' Этап 1. Объединение строчек
Dim groupStartLine As Long: groupStartLine = 1
Dim line As Long: For line = 2 To rng.Rows.Count
If rng.Cells(line, indicator) <> "" Then
groupStartLine = line
Else
Dim col As Long: For col = 1 To colCount
If col <> indicator Then
Dim v As Variant: v = rng.Cells(line, col).Value
If v <> "" Then
rng.Cells(groupStartLine, col).Value = rng.Cells(groupStartLine, col).Value & JOIN_SYMBOL & v
rng.Cells(line, col).ClearContents
End If
End If
Next col
End If
Next line
' Этап 2. Удаление пустых строк
For line = rng.Rows.Count To 1 Step -1
Dim rowRange As Range: Set rowRange = rng.Cells(line, 1).Resize(, colCount)
If WorksheetFunction.CountA(rowRange) = 0 Then
rowRange.Delete xlShiftUp ' удаление только строки таблицы
'rowRange.EntireRow.Delete xlShiftUp ' удаление всей строки на листе
End If
Next line
End Sub
JOIN_SYMBOL — это символ, которым будут склеиваться значения в строке. Например, пробел. Можно сделать "" (пусто).

Среди этих инструкций:

rowRange.Delete xlShiftUp ' удаление только строки таблицы
'rowRange.EntireRow.Delete xlShiftUp ' удаление всей строки на листе
Закоментируйте/раскомментируйте нужную. Успехов!
0

А где скачать файл с исходником?

0

Спасибо!

раскрыть ветку 3
-1

Предложение скинуть выгрузку напрямую - в силе.
Контакты ннада?)

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

заманчиво, но извини, не буду... медицина, перс.данные...

мне вполне достаточно, с мелочами и сам способен забороть.

раскрыть ветку 1
0
Лучше задачу решить чз Power query, по-моему на планете эксель был похожий пример, как это дело автоматизируется
раскрыть ветку 1
0
Делается через 4 функции
-1

Вопрос - господа, сам стиль написания вас устраивает? Все более-менее понятно зачем/ куда / почему?

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

Нет, все наизусть должны знать ваш любимый текстовый редактор?

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

Кого как учили- тот то и знает. Я через текстовый редактор делал оптимизацию 1с на 27к строк и четверть ярда. И сделал. А у вас как дела?))

-1
Друг, подскажи как вытащить из Экселя все слова, которые содержат символ " @", т.е. надо все почтовые адреса вычленить из каждой ячейки. Также в каждой ячейке есть куча лишний данных (номера, адреса, имена и т.д.). Мне же нужны только почты.

И желательно простым языком, т.к. я нуб
раскрыть ветку 6
0

Почта же отделена от соседних слов? Пишем Поиск("@";А3;1) - находим номер первой собаки, далее ищем пробелы слева и справа и делаем ПСТР. Блин, компа нет под рукой показать.

раскрыть ветку 3
0
Да я уже макрос нашел, который вычленил все почты в чистом виде.

Но буду рад, подробному описанию

Может ещё подскажешь какую-нибудь книженцию для новичков?
раскрыть ветку 2
-1

примеры в студию) самому интересно

раскрыть ветку 1
0
Пример макроса, который нашел, нужен ?
Похожие посты
189

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

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

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

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

193

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

453

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
99

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
534

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

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


Формула может пригодиться для контроля сроков заказов, отправки грузов и выполнения проектов, чтобы наглядно видеть количество оставшихся дней до 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
255

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
683

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
638

Сортировка по дням рождения в 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
96

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

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

Как построить график или диаграмму из Сводной таблицы 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
392

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

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


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

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

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


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


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


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


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


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

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

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

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


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

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

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


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

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

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

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


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


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

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

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


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

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

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

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

Источник

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

Суммирование в 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
269

Редизайнер таблиц в Excel

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


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

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

- одна строка - одна законченная операция (сделка, продажа, проводка, проект и т.д.)

- без объединенных ячеек

- без разрывов в виде пустых строк и столбцов


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

из такой таблицы

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

сделать

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

В терминах баз данных нижнюю таблицу обычно называют плоской (flat) - именно по таким таблицам лучше всего строить отчеты сводных таблиц (pivot tables) и проводить аналитику.


Преобразовать двумерную таблицу в плоскую можно при помощи простого макроса. Откройте редактор Visual Basic через вкладку Разработчик - Visual Basic (Developer - Visual Basic Editor) или сочетанием клавиш Alt+F11. Вставьте новый модуль (Insert - Module) и скопируйте туда текст этого макроса:

Sub Redesigner()

Dim i As Long

Dim hc As Integer, hr As Integer

Dim ns As Worksheet

hr = InputBox("Сколько строк с подписями сверху?")

hc = InputBox("Сколько столбцов с подписями слева?")

Application.ScreenUpdating = False

i = 1

Set inpdata = Selection

Set ns = Worksheets.Add

For r = (hr + 1) To inpdata.Rows.Count

For c = (hc + 1) To inpdata.Columns.Count

For j = 1 To hc

ns.Cells(i, j) = inpdata.Cells(r, j)

Next j

For k = 1 To hr

ns.Cells(i, j + k - 1) = inpdata.Cells(k, c)

Next k

ns.Cells(i, j + k - 1) = inpdata.Cells(r, c)

i = i + 1

Next c

Next r

End Sub

После этого можно закрыть редактор VBA и вернуться в Excel. Теперь можно выделить исходную таблицу (полностью, с шапкой и первым столбцом с месяцами) и запустить наш макрос через Разработчик - Макросы (Developer - Macros) или нажав сочетание Alt+F8.


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


Также есть второй вариант для работы с большими таблицами

Sub Redesigner()

Dim inpdata As Range, realdata As Range, ns As Worksheet

Dim i&, j&, k&, c&, r&, hc&, hr&

Dim out(), dataArr, hcArr, hrArr

hr = Val(InputBox("Сколько строк с подписями данных сверху?"))

hc = Val(InputBox("Сколько столбцов с подписями данных слева?"))

Set inpdata = Selection

If inpdata.Rows.Count <= hr Or inpdata.Columns.Count <= hc Then Exit Sub

Set realdata = inpdata.Offset(hr, hc).Resize(inpdata.Rows.Count - hr, inpdata.Columns.Count - hc)

dataArr = realdata.Value

If hr Then hrArr = inpdata.Offset(0, hc).Resize(hr, inpdata.Columns.Count - hc).Value

If hc Then hcArr = inpdata.Offset(hr, 0).Resize(inpdata.Rows.Count - hr, hc).Value

ReDim out(1 To Application.CountA(realdata), 1 To hr + hc + 1)

Set ns = Worksheets.Add

For i = 1 To UBound(dataArr, 1)

For j = 1 To UBound(dataArr, 2)

If Not IsEmpty(dataArr(i, j)) Then

k = k + 1

For c = 1 To hc: out(k, c) = hcArr(i, c): Next c

For r = 1 To hr: out(k, c + r - 1) = hrArr(r, j): Next r

out(k, c + r - 1) = dataArr(i, j)

End If

Next j, i

ns.Cells(2, 1).Resize(UBound(out, 1), UBound(out, 2)) = out

End Sub

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

Взято отсюда

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

Надстройка для MS Excel (часть 2)

Всем привет!

В прошлом посте я представил на суд общественности свой open-source проект - надстройку для MS Excel, с набором полезных функций. Пост был встречен очень тепло, многие пикабушники оставили пожелания о добавлении новых функций, а несколько человек написали мне с предложениями подключиться к разработке. Спасибо @1041618 за редизайн - мы обзавелись новыми иконками в едином стиле и поддержкой серой и тёмной тем, а также учитываем в локализации язык пакета MS Office.

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Исходный код проекта и инструкции по установке и использованию опубликованы на GitHub под лицензией MIT (неограниченное право на использование, копирование, изменение).


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

Транслитерация кириллицы в латинские буквы


Конвертирует содержимое текстовых ячеек, содержащих символы кириллицы, в латинские символы (по стандарту ICAO doc 9303), за идею спасибо @negotivko

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Подсветка дублей


Раскрашивает разными цветами группы одинаковых значений:

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

«Размерживание» объединенных ячеек с их заполнением


При разбиении объединенных ячеек стандартной функцией Excel все ячейки области, кроме левой верхней, остаются пустыми. Но теперь можно заполнить их значением исходной ячейки:
Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Экспорт таблицы в markdown


Markdown - это удобный язык разметки, используемый при форматировании текстов во многих системах: wiki (Confluence), GitHub, Gitlab, Reddit, Stack Exchange, OpenStreetMap и множество других. Функция копирования таблицы в markdown помещает в буфер обмена отформатированную таблицу с заголовками, и Вы легко можете вставить ее в свой документ:

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Проверка значений в выделенных ячейках (числа, даты, ИНН ЮЛ/ФЛ и т.д.)


Можно проверить, например, список ИНН организаций на корректность (по контрольным цифрам в номере):

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

Кроме того, есть две функции по работе с XML-файлами. Они не относятся напрямую к функциональности Excel, но иногда в них возникает потребность:

Сформировать пример XML файла на основе XSD-схемы


Если у вас есть файл XSD, который содержит xml-схему, Вы можете сформировать образец XML-файла на основе этой схемы:

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

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


Проверить XML по XSD-схеме


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

Установка надстройки


Для установки надстройки выберите последнюю по времени успешную сборку пайплайна NavfertyExcelAddIn - Publish и скачайте опубликованные файлы:

Надстройка для MS Excel (часть 2) Excel, Надстройка, Полезное, Длиннопост

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


Для установки надстройки нужно запустить файл .vsto


Хотел бы предупредить об одном ограничении: при вызове функций надстройки (как COM-надстроек, так и VBA) в MS Excel очищается стек последних действий пользователя для отмены (Undo). Я работаю над тем, чтобы обойти это ограничение хотя бы для возможности отмены выполнения самой функции, но пока что будьте внимательны при запуске функций - отменить действие можно будет, только закрыв книгу без сохранения.


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

https://github.com/navferty/NavfertyExcelAddIn


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


Если Вы .NET-разработчик и хотите присоединиться к работе над проектом, пишите мне на почту (указана в профиле гитхаба), заводите issue и пулл-реквесты.

Если Вы только изучаете платформу .NET, и хотите поучаствовать - не стесняйтесь! Читайте исходный код, задавайте вопросы, если что-то непонятно - буду рад объяснить и поделиться знаниями =)

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

Надстройка для MS Excel

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

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Исходный код проекта и инструкции по установке и использованию опубликованы на GitHub под лицензией MIT (неограниченное право на использование, копирование, изменение).


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

Ниже приведу краткое описание некоторых функций.

Конвертация чисел, форматированных как текст


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


Ниже пример преобразования чисел при помощи надстройки. Столбец B для примера заполнен формулой "=A2+1", которая демонстрирует, является ли значение слева числом:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Переключение регистра текста


В MS Word есть удобная функция, доступная по Shift+F3, которая переключает регистр выделенного текста (в последовательности "Sentence case" -> "lowercase" -> "UPPERCASE"). Иногда такой функции не хватает и в Excel, но надстройка восполняет этот пробел:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Очистка текста от пробельных символов


Позволяет очистить текст от пробелов в начале и конце значения, а также от повторяющихся пробелов и переносов строки в середине текста:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Интерактивный поиск ячеек, в которых произошла ошибка вычисления


Показывает список всех ячеек с ошибкой (например, "#Н/Д"), и позволяет быстро перемещаться к выбранной ячейке:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

И другие функции:


- Подсветка дублей (разными цветами группы одинаковых значений)

- «Размерживание» объединенных ячеек с их заполнением

- Снятие пароля с защищённой книги и листов

- Экспорт таблицы в markdown

- Проверка значений в выделенном диапазоне ячеек (числа, даты, корректный ИНН ЮЛ/ФЛ и т.д.)


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


- Сформировать пример XML файла на основе XSD-схемы

- Проверить XML по XSD-схеме


Установка надстройки


Для автоматической сборки установочных файлов настроена сборка в Azure.

Выберите последнюю по времени успешную сборку пайплайна NavfertyExcelAddIn - Publish и скачайте опубликованные файлы:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Важный момент: при установке надстройки Excel "запоминает" путь к папке, из которой она была установлена, и в будущем установка обновлений будет возможна только из этой папки, в противном случае нужно будет воспользоваться "установкой и удалением программ" через Панель управления Windows.


Для установки надстройки нужно запустить файл .vsto. Разумеется, установка возможно только при наличии установленного MS Excel =)


Если всё сделано правильно, то Вы увидите новую вкладку при следующем запуске Excel:

Надстройка для MS Excel Excel, Надстройка, Полезное, Длиннопост

Если Вы .NET-разработчик и хотите присоединиться к работе над проектом, пишите мне на почту (указана в профиле гитхаба), заводите issue и пулл-реквесты.

Если Вы только изучаете платформу .NET, и хотите поучаствовать - не стесняйтесь! Читайте исходный код, задавайте вопросы, если что-то непонятно - буду рад объяснить и поделиться знаниями =)

Еще раз ссылка на проект (там же инструкции по использованию и установке):

https://github.com/navferty/NavfertyExcelAddIn

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

Отслеживание входа пользователей в книгу Excel

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


Этап 1. Создаем "Лог"

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

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Этап 2. Макросы фиксации входа-выхода

Теперь добавим макросы для записи на лист Лог даты-времени и имен пользователей при открытии и закрытии книги. Для этого нужно открыть редактор Visual Basic с помощью сочетания Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) и найти в левом верхнем углу панель Project (если она не отображается, то включить ее можно сочетанием клавиш Ctrl+R):

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Двойным щелчком откройте модуль ЭтаКнига (ThisWorkbook) и вставьте туда пару наших макросов для обработки событий открытия и закрытия книги:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим дату-время выхода из файла

If lastrow>1 Then Worksheets("Лог").Cells(lastrow, 3) = Now

'сохраняемся перед выходом

ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим имя пользователя и дату-время входа в файл

Worksheets("Лог").Cells(lastrow + 1, 1) = Environ("USERNAME")

Worksheets("Лог").Cells(lastrow + 1, 2) = Now

End Sub

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

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Этап 3. Улучшаем надежность

Можно было бы скрыть лист Лог и на этом остановиться, но есть одно "но": если у пользователя, который открывает нашу книгу, макросы разрешены по умолчанию либо он сам их разрешает, нажав в окне предупреждения на кнопку Включить содержимое, то все в порядке:

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

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

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

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Суть в том, чтобы по умолчанию скрыть в книге все листы кроме этого, а рабочие листы с данными отображать с помощью специального макроса. Если пользователь не разрешил выполнение макросов, то он увидит в книге только один лист с предупреждением. Если же макросы разрешены, то наш макрос обработки события открытия книги скроет лист с предупреждением и отобразит листы с данными. Чтобы пользователь сам не отобразил их - используем суперскрытие вместо обычного скрытия листов (параметр xlSheetVeryHidden вместо обычного False).

Чтобы реализовать все описанное, слегка изменим наши процедуры в модуле ЭтаКнига (ThisWorkbook)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим дату-время выхода из файла

If lastrow > 1 Then Worksheets("Лог").Cells(lastrow, 3) = Now

'скрываем все листы, кроме листа ПРЕДУПРЕЖДЕНИЕ

Worksheets("Предупреждение").Visible = True

For Each sh In ActiveWorkbook.Worksheets

If sh.Name = "Предупреждение" Then

sh.Visible = True

Else

sh.Visible = xlSheetVeryHidden

End If

Next sh

'сохраняемся перед выходом

ActiveWorkbook.Save

End Sub


Private Sub Workbook_Open()

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим имя пользователя и дату-время входа в файл

Worksheets("Лог").Cells(lastrow + 1, 1) = Environ("USERNAME")

Worksheets("Лог").Cells(lastrow + 1, 2) = Now

'отображаем все листы

For Each sh In ActiveWorkbook.Worksheets

sh.Visible = True

Next sh

'скрываем листы ПРЕДУПРЕЖДЕНИЕ и ЛОГ

Worksheets("Предупреждение").Visible = xlSheetVeryHidden

Worksheets("Лог").Visible = xlSheetVeryHidden

End Sub

Чтобы просмотреть скрытый Лог откройте редактор VisualBasic (Alt+F11), выделите лист на панели Project и измените его видимость на панели Properties, используя свойство Visible:

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Если пользователи настолько продвинутые, что знают про суперскрытые листы и могут их отобразить через редактор Visual Basic или нарушить работу наших макросов, то можно дополнительно поставить пароль на просмотр и изменение макросов. Для этого щелкните правой кнопкой мыши по имени файла в панели Project (строка VBAProject (blackbox.xls)), выберите команду VBA Project Properties и включите флажок Lock project for viewing и задайте пароль на вкладке Protection:

Отслеживание входа пользователей в книгу Excel Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Теперь точно никто не уйдет безнаказанным.


Интересные поправки в макрос из комментария источника:

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

Private Sub Workbook_Open()

Worksheets("Реестр изменений").Rows("2:2").Insert Shift:=xlDown 'вставляем между строками 1 и 2 новую строку

Worksheets("Реестр изменений").Rows("501:501").Delete Shift:=xlUp 'удаляем строку 501 (реестр на 500 строк)

Worksheets("Реестр изменений").Cells(2, 1) = Environ("USERNAME") 'запись в первую ячейку второй строки

Worksheets("Реестр изменений").Cells(2, 2) = Now 'запись во вторую ячейку второй строки

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