Сообщество - MS, Libreoffice & Google docs
Добавить пост
261 пост 10 230 подписчиков
1521

ВСЁ о сводных таблицах с нуля за полчаса

На позапрошлой неделе снимал получасовое видео о сводных таблицах в Excel. Тогда не решился выкладывать его на Пикабу, так как подумал, что плохо зайдёт (переводить все 30 минут видео в формат поста точно не охота, а закинуть лишь видео не решился, так как думал, что такой пост не будет особо хорошо принят). Тем не менее, видео-продолжение о построении дашбордов зашло на ура, так что сейчас также делюсь видеоуроком о всех особенностях работы со сводными таблицами с нуля:

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


Приятного просмотра! 😊

1046

Как создать Дашборд в Excel

Знаю, что хорошо заходят посты, имеющие определённое количество текста/картинок. Тем не менее, создание дэшбордов в Excel – эта такая тема, которую уместить в несколько картинок и листов текста ну очень сложно! А поскольку поделиться еще одним из вариантов создания дэшбордов всё-таки очень хочется, решил поделиться в этом посте информацией в формате видео:

Что рассмотрено в видео:

• как создавать интерактивные дэшборды в Excel со сводными и не только диаграммами на основе сводных таблиц

• оптимальная структура рабочей книги для дэшборда

• подход в создании дэшбордов для их долгосрочного использования и развития (разработка отдельных деталей дэшборда на отдельных листах)


Приятного просмотра! 😊

56

Excel VBA: как выбрать подходящее место для кода

В Excel VBA cуществует пять мест для кода:
- модуль;

- модуль листа;

- модуль книги;

- модуль формы;

- модуль класса.

Excel VBA: как выбрать подходящее место для кода Microsoft Excel, Vba, Для начинающих, Длиннопост

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

У остальных модулей - своё назначение, так куда же положить код? Как выбрать подходящее место? На определённом этапе всё станет очевидно, но что делать с самым первым макросом?

Ответ 1: модули листа и книги должны содержать только код, который обрабатывает события листа и книги соответственно (но это же наш первый макрос, какие ещё события?); получается, что единственным местом остаётся модуль.

Ответ 2: если не знаете, куда поместить код, используйте модуль; кстати, записанные макросы попадают именно в модули.

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

Объект верхнего уровня - Application (собственно приложение Excel). В большинстве случаев его можно не указывать.

Следующий уровень - объект Workbook (книга). Все объекты этого уровня входят в коллекцию Workbooks и на каждый из них можно сослаться
Application.Workbooks("Книга1")
Workbooks("Книга1").

Далее - объект Worksheet (лист). Все объекты этого уровня входят в коллекции Worksheets и на каждый из них можно сослаться разными способами:

Application.Workbooks("Книга1").Worksheets("Лист1")
Workbooks("Книга1").Worksheets("Лист1")
Worksheets("Лист1")
Кроме того, все объекты Worksheet входят в коллекцию Sheets, т.е. можно обращаться к ним:

Application.Workbooks("Книга1").Sheets("Лист1")
Workbooks("Книга1").Sheets("Лист1")
Sheets("Лист1")
Далее остаётся только получить конкретную ячейку на листе в книге. Для получения ячейки используется свойство Cells объекта Worksheet. Для упрощения (очень-очень сильного упрощения) можно считать ячейку концом цепочки:

Application.Workbooks("Книга1").Sheets("Лист1").Cells(1, 1)
Workbooks("Книга1").Sheets("Лист1").Cells(1, 1)
Sheets("Лист1").Cells(1, 1)
Cells(1, 1)

Остаётся упомянуть ещё несколько крайне важных вещей:

Application.ActiveWorkbook или ActiveWorkbook возвращает активную книгу

Application.ActiveSheet или ActiveSheet возвращает активный лист

Application.ThisWorkbook или ThisWorkbook возвращает книгу, в которой расположен код

ThisWorkSheet - ошибка, нет такого слова!

И вот теперь можно, наконец, сказать главное: всегда, в каждой строчке кода, в любой момент времени нужно точно знать цепочку Workbook > Worksheet, потому что почти во всех случаях, если эта цепочка не указана явно, подразумевается активный лист и активная книга. Т.е. если код, расположенный в любом месте в "Книга1", выполняется при активной "Книга2", то он будет работать с данными и изменять содержимое активного листа книги "Книга2".

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

Например, код

Cells(1, 1)

расположенный в модуле листа, всегда будет работать с этим листом. Этот же код, расположенный в модуле или модуле книги, будет работать с активным листом активной книги.

Код

Workbooks("Книга1").Sheets("Лист1").Cells(1, 1)

всегда будет работать одинаково.

Таким образом:

- если вы совершенно точно собираетесь работать только с одним листом, то можно поместить код в модуль листа, это визуально упростит код;

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


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

Существуют разные способы упростить код и избежать ошибок.

1. Если вы уверены, что работа с активным листом активной книги - всё, что вам нужно, то цепочку Workbook > Worksheet в большинстве случаев можно опустить.

2. Оператор With:

With Workbooks("Книга1").Sheets("Лист1")
.Cells(1, 1) = 1
End With
эквивалентен

Workbooks("Книга1").Sheets("Лист1").Cells(1, 1)
Excel VBA: как выбрать подходящее место для кода Microsoft Excel, Vba, Для начинающих, Длиннопост

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

3. Переменные типа объект (и помните: любой код в VBA должен начинаться с Option Exlicit - два этих слова уберегут вас от миллионов ошибок)

Excel VBA: как выбрать подходящее место для кода Microsoft Excel, Vba, Для начинающих, Длиннопост

4. Можно просто сделать нужный лист активным (лично я не люблю этот метод, но почему бы и нет)

Excel VBA: как выбрать подходящее место для кода Microsoft Excel, Vba, Для начинающих, Длиннопост

Понимание объектной модели данных и умение точно указывать на объект, его свойства и методы, в Excel VBA чрезвычайно важны, без этого любые попытки что-то сделать будут мучением.

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

Шаблон для бирок ключей

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

Шаблон для бирок ключей Шаблон, Бирка, Брелок, Microsoft Word

Ну то да се, взял линейку, примерно обмерил; так как внутри бумажка заходит за края - прикинул отступы. И по быстрому сварганил в word  - так как шаблон надо переслать по почте туда, а там кроме офиса и докоборота нету ничего, ни корела ни фотошопа:

Шаблон для бирок ключей Шаблон, Бирка, Брелок, Microsoft Word

В итоге получилось как-то так.

Шаблон для бирок ключей Шаблон, Бирка, Брелок, Microsoft Word

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

Так что, может быть кому и пригодится :) А нам уже пригодилось :D
Заодно оставлю себе, ибо хочется поменять бирки на ключах к замкам на мордах серверов и шкафов на более современные, что ли...

Закинул себе в облако, если что, шибко не пинайте :)
https://drive.google.com/file/d/1JD9MGnSntiq0GRQrvi4Rrj5onvK...

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

Нормализация значений в ячейках Excel

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

Итак, проблема.

Бывает такое странное, что в ячейке стоит значение, а она не форматируется, как тебе надо. Всегда остается неизменной. Даже, какие-то вычисления с ней можно произвести, а формат не меняется, пока в ячейку не зайдешь и не нажмешь ввод. Такое случается при выгрузке в формате эксель из какой-нибудь другой программы. 1С, например, будь он проклят. Или, что будет описано ниже, из MS Project. Далее описание примера с картинками.

Нормализация значений в ячейках Excel Microsoft Excel, Vba, Длиннопост

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

Нормализация значений в ячейках Excel Microsoft Excel, Vba, Длиннопост

Вот так это работает. Я внес в ячейку G12 просто 1, ячейка O12 сигнализирует, что она отличается.

Нормализация значений в ячейках Excel Microsoft Excel, Vba, Длиннопост

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

Нормализация значений в ячейках Excel Microsoft Excel, Vba, Длиннопост

Формат применен, однако в ячейке по прежнему год 2020

Нормализация значений в ячейках Excel Microsoft Excel, Vba, Длиннопост

Но, если дважды кликнуть по ячейке, а затем нажать Enter (как бы ввести значение в ячейку), то формат становится тем, какой применен ранее. Вот тут заливка желтым сигнализирует, что ячейки B3 и J3 не равны.

Теперь по формулам в таблицах. Они идентичны.

Нормализация значений в ячейках Excel Microsoft Excel, Vba, Длиннопост

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

Нормализация значений в ячейках Excel Microsoft Excel, Vba, Длиннопост

Синим выделил расчет в начальном варианте. 23.09.20 считается от окончания, хотя оно раньше 12.10.20 в предыдущей строке. А должно быть наоборот, как в таблице справа.

Нормализация значений в ячейках Excel Microsoft Excel, Vba, Длиннопост

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

Как водится, в экселе, таких строк обычно тысячи и каждую ячейку вручную не исправишь.

К сожалению, никаких средств в самом экселе для решения такой проблемы нет.

решение

Для себя же, методом тыка, нашел команду в VBA и вставил простенький макрос:

Нормализация значений в ячейках Excel Microsoft Excel, Vba, Длиннопост

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

!Еще бывает, если выделять очень длинный диапазон, то случалось вместо 12.05.2021 получить 12.05.01. Почему и как оно так вышло, не понял.

На самом деле, метод Parse предназначен не для этого, конечно.

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

Но, оно работает, а большего мне и не надо.

Как вставить себе в эксель макрос и повесить его на кнопку, много где описано, поэтому не стану.

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

ПКМ и Drag & Drop в Excel

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


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


Работает этот подход очень просто:

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

ПКМ и Drag & Drop в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

2. Наводим мышку на выделенный край выбранного диапазона

ПКМ и Drag & Drop в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

3. Зажимаем правую кнопку мышки и перетягиваем выбранный диапазон в нужное место

ПКМ и Drag & Drop в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

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

ПКМ и Drag & Drop в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

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

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

Суммирование по цвету в Excel

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


Поэтому для подобных задач приходится писать пользовательские функции. В этом посте хочу поделиться примером кода UFD (User Defined Function – Пользовательской функции) для суммирования значений ячеек из определенного диапазона, соответствующих заданному цвету заливки.


Вот код VBA:


Option Explicit
Function SUMIFCOLOR(rgCellsToSum As Range, rgColorSample As Range)
Dim rgCellChecked As Range
Dim intColorIndex As Integer
Dim dblSum As Double
Application.Volatile
'Сохраняем указание цвета в числовую переменную
intColorIndex = rgColorSample.Interior.ColorIndex
'Проверяем каждую ячейку и сохраняем её значение в промежуточную переменную
For Each rgCellChecked In rgCellsToSum
If rgCellChecked.Interior.ColorIndex = intColorIndex Then
dblSum = dblSum + rgCellChecked.Value
End If
Next rgCellChecked
'Вносим значение в функцию
SUMIFCOLOR = dblSum
End Function

Итак, что теперь с этим кодом надо сделать.

1. Открываем требуемый файл

2. Открываем окно редактора VBA с помощью комбинации клавиш Alt+F11

3. В нём добавляем новый модуль: Insert (1) -> Module (2)

Суммирование по цвету в Excel Microsoft Excel, Vba, Лайфхак, Пособие, Видео, Длиннопост

4. В открывшемся поле вставляем код (отступы, к сожалению, по желанию придётся проставить вручную):

Суммирование по цвету в Excel Microsoft Excel, Vba, Лайфхак, Пособие, Видео, Длиннопост

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

Суммирование по цвету в Excel Microsoft Excel, Vba, Лайфхак, Пособие, Видео, Длиннопост

При этом обрати внимание – чтобы теперь в файле вставленный макрос сохранился, сохранить файл надо будет в формате xlsm.


При этом, данная функция является волатильной (перерасчет происходит всегда, даже если изменения произошли в другой ячейке), но изменения формата не являются триггерами перерасчёта – поэтому, если хочешь, чтобы она всегда отражала актуальное значение, её надо немного расширить с помощью событийной процедуры Worksheet_SelectionChange. Это уже более объемная тема, которую тяжело компактно описать в тексте, поэтому предлагаю посмотреть вот это видео:

В нём, во-первых, в деталях разбирается, как работает представленная в этом посте функция, во-вторых, представлено её «расширение» Worksheet_SelectionChange, ну и в конце концов этом видео подробно объясняется, что такое волатильность функций, как работает перерасчет функций в Excel, и как работают событийные процедуры в VBA.

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

Детский сад. Табель

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

Чуть более года назад моя мама начала меня ежемесячно просить помочь ей заполнить табель учета посещаемости детей (она работает в д/с воспитателем).
Это меня как бы особо и не напрягало, напрягал тот момент, что таблица была в word-е, а количество дней надо было пересчитать, вывести КС и на этих моментах периодически всплывали ошибки, так как кто-то в рукописном табеле ошибся - и сиди ищи.

В итоге я решила перевести эту табличку в excel.
1. Отредактировала форму, чтобы она повторяла ту, что была в word.
2. Поколдовала с формулами (не буду описывать этот процесс полностью, так как было это давно и всего уже не помню).

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

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

По традиции - инструкция с описанием как что заполнять - на первом листе в самом файле
Ссылка на табель
https://drive.google.com/file/d/1j56i5nmgAhgo6BWYAEtJI4jl-ZC...


Если не открывается/ не скачивается - пишите, буду отправлять ссылки в комментарии


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

59

Изменение значения ячейки в зависимости от другой ячейки Excel

Доброго времени суток.
Перерыл кучу информации, но так и не понял, как присвоить значение ячейке в зависимости от значения другой, при этом сделать это не возвращением, а именно присвоением, чтобы если что ячейки которым присвоят значение могли меняться.
Пример:
Есть ячейка B3, в которой на данный момент внесено значение 100
Есть ячейка B4, в которой на данный момент значение 0.
Как сделать так, что при внесении любого числа больше 0 в ячейку B4, ячейка B3 автоматически станет равна 0 и наоборот.
Т.е. эти ячейки должны остаться редактируемыми, но при этом автоматически меняющимися в зависимости от значений друг друга.

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

UPD ответ найден в комментарии #comment_196491784

15

Цветные таблицы. Кошаки. Старт нового проекта :)

Доброго времени суток, котяточки. У меня для вас классные новости, но обо всем по порядку.

Из - за моей великой любви к табличкам, год назад я начал вести этот блог. За это время несколько моих читателей переросли в моих клиентов, а откликов на посты и запросов на консультацию становилось все больше и было принято решение о старте собственного проекта.  С гордостью расскажу вам о своём детище, под названием Colorful Tables, да - да, цветные таблички). Старт своего проекта стал глотком свежего воздуха и раньше я не испытывал такого кайфа от работы. И виновники моего счастья - свобода самостоятельно принимать решения и построить команду в соответствии с той горой нонфикшн литературы, которой я вдохновлялся последние годы и собственно отличная команда, которую удалось собрать. Для себя я на старте решил, что мне не оч интересны большинство HR-ных метод и подбирал команду по двум критериям - чтобы человек хотел учиться и чтобы нам было круто общаться. Таким образом в команду попали 3 девочки-джуна (они просто невероятные *___*), которых буду учить таблицам с самого нуля и с первых дней - все решения по дальнейшей судьбе проекта мы принимаем совместно.

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

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

ЗЫ Прототип нашего лого на фото :)

Цветные таблицы. Кошаки. Старт нового проекта :) Google Sheets, Обучение
Мои подписки
Подписывайтесь на интересные вам теги, сообщества,
пользователей — и читайте персональное «Свежее».
Чтобы добавить подписку, нужно авторизоваться.
Отличная работа, все прочитано!