VBA.Excel

VBA.Excel

https://www.youtube.com/c/ExcelVBABillK/featured
На Пикабу
поставил 8 плюсов и 2 минуса
отредактировал 1 пост
проголосовал за 3 редактирования
Награды:
самый сохраняемый пост недели самый сохраняемый пост недели более 1000 подписчиков
48К рейтинг 4483 подписчика 4 подписки 56 постов 53 в горячем

Суммирование по цвету в 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.

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

ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel

В абсолютном большинстве случаев ВПР применяется для поиска точного совпадения – для этого в последнем аргументе функции вводится значение ЛОЖЬ. Тем не менее, также очень полезно знать, как и для чего применяется ВПР со значением ИСТИНА в качестве последнего аргумента, тем более что сферы применения такого варианта ВПР достаточно интересны и на самом деле встречаются намного чаще, чем можно подумать.


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

ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel Microsoft Excel, Функция, Видео, Длиннопост

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

ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel Microsoft Excel, Функция, Видео, Длиннопост

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


При этом обязательно нужно обратить внимание, что значения во вспомогательной таблице должны быть просортированы в возрастающем порядке! Буквально через пару строк я объясню, как работает ВПР с ИСТИНА в последнем аргументе, и сразу станет почему сортировка вспомогательной таблицы так важна.


Но перед этим сперва пропишем функцию:

ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel Microsoft Excel, Функция, Видео, Длиннопост

Текст функции у нас выходит: =ВПР(C3;$F$7:$G$10;2;ИСТИНА)


С3 – ссылка на искомое количество баллов

$F$7:$G$10 – ссылка на вспомогательную таблицу. Обязательно закрепляем с помощью F4

2 – это указание, что нужно значение из второго столбца вспомогательной таблицы

ИСТИНА – Поиск «примерного» значения


Отлично! Ну и остаётся лишь протянуть функцию:

ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel Microsoft Excel, Функция, Видео, Длиннопост

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


Итак, у функции ВПР с ИСТИНОЙ в последнем аргументе есть три варианта срабатывания:


Первый: ВПР находит точное совпадение. Отличным примером является студент А. ВПР видит, что у него оценка 75 и начинает пошагово проверять каждую строчку в справочной табличке. 0 не подходит, идём дальше, 60 не подходит, идём еще дальше, и вот следующим и попадается 75. Значение точно соответствует искомому, поэтому Excel без раздумий берёт соседнее справа значение.

ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel Microsoft Excel, Функция, Видео, Длиннопост

Теперь второй вариант – оценка студента В. Взяв его 86 баллов, Excel также начинает пошагово проверять справочную табличку. Сперва всё похоже с первым вариантом. 0 не подходит, 60 не подходит, 75 не подходит, но вдруг, что происходит дальше – следующее значение 90 у нас уже больше, чем искомое 86, поэтому Excel делает один шаг назад, возвращаясь к предыдущему уровню, и берёт его оценку. В итоге мы имеем как раз то, что нужно. Значение 86 лежит между 75 и 89, поэтому итоговой оценкой выходит 4.

ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel Microsoft Excel, Функция, Видео, Длиннопост

Ну и последний вариант срабатывания ВПР с ИСТИНОЙ — это выведение ошибки. Такое происходит, если все значения в указанной табличке сравнения больше искомого и Excel не может найти подходящих значений.

ВПР и ИСТИНА: Вычисление оценок, комиссий и т.д. в Excel Microsoft Excel, Функция, Видео, Длиннопост

На картинках, конечно, немного сложно наглядно объяснить принцип работы ВПР, поэтому предлагаю посмотреть следующее видео:

В нём я наглядно на анимациях рассмотрел принцип работы ВПР с ИСТИНОЙ в качестве последнего аргумента. Кроме того, в видео также есть упражнение на закрепление с интересным примером расчёта налоговой ставки 😊

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

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки!

Защита листа в Excel – это всем хорошо известная функция, которая позволяет блокировать внесение изменений в рабочий лист («Рецензирование» -> «Защитить лист»). Я уверен, ты с ней уже знаком.


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


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

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

Для этого, в настройках форматирования каждой из этих оранжевых ячеек нужно отключить настройку защиты ячейки «Защищаемая ячейка» (то есть даже если весь лист будет защищен, ячейки всё также будут изменяемыми).


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


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

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

И открываем окно поиска значений с помощью комбинации клавиш CTRL+F:

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

Нажав на «Параметры», раскрываем расширенные настройки поиска:

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

Теперь, если у тебя вместо «Формат не задан» написано «Образец» (1), то нужно нажать на «Очистить формат поиска»:

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

После этого щелкаем по кнопке «Формат» (5) и в открывшемся окне, не изменяя других настроек, указываем те настройки, которые нам нужны. В нашем примере нам нужны лишь оранжевые ячейки, так что захожу в раздел «Заливка» (6) и выбираю нужный мне цвет (7), после чего подтверждаю ввод (8):

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

Отлично, требуемый формат задали, так что идём дальше, и теперь нажимаем на «Найти все» (9). В нижней части окна Excel выводит все найденные ячейки, соответствующие заданному формату (10):

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

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

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

Сейчас все нужные оранжевые ячейки были успешно выбраны, так что теперь заходим в окно форматирования ячеек (CTRL+1) и в открывшемся окне во вкладке «Защита» убираем галочку в поле «Защищаемая ячейка» (11):

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

Подтверждаем и теперь защищаем рабочий лист (Вкладка «Рецензирование» -> «Защитить лист»):

Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки! Microsoft Excel, Лайфхак, Пособие, Таблица, Видео, Длиннопост

Всё, всё готово! Теперь пользователь этого файла может вносить изменения лишь в разрешенные нами ячейки.


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

В нём я также в принципе пошагово и еще более углубленно разобрал тему сегодняшнего поста, поэтому также предлагаю тебе посмотреть это видео.


Надеюсь, совет будет для тебя полезным 😊

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

Оператор пересечения в Excel

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


Вот как применяется оператор пересечения. Например, выведем в ячейке B10 значение, находящееся на пересечении столбца продукта «C» со строкой страны Мексики. Для этого в В10 вписываем =E2:E8 B5:H5

Оператор пересечения в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

Эта формула интерпретируется так:

Оператор пересечения в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

Подтверждаем ввод формулы, и видим, что как раз значение из пересечения заданных диапазонов выводится в качестве результата расчета формулы:

Оператор пересечения в Excel Microsoft Excel, Лайфхак, Видео, Длиннопост

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

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

Когда получил диплом, чтобы весь день копи-пейстить в Excel

Когда получил диплом, чтобы весь день копи-пейстить в Excel

Регистр -> Регистр -> РЕГИСТР: Просто, быстро и удобно!

Заметил, что многие еще не знакомы с такой зачастую спасающей комбинацией клавиш в Word, как Shift+F3, и из-за этого вручную переводят формат написания слов/фраз/предложений в другой регистр. Поэтому обязательно попробуйте следующий трюк:

1. Выбираем нужное слово

2. Используем комбинацию клавиш Shift+F3 (если Вы работаете с ноутбука, то, возможно, придётся в эту комбинацию клавиш также добавить клавишу Fn)

3. Наслаждаемся результатом! Бонус: повторяя нажатие по F3 при всё также зажатом шифте, ворд будет циклировать изменение регистра, т. е. например регистр -> Регистр -> РЕГИСТР -> регистр и т.д. (см. пример ниже с предложением)

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

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

Excel: Как изменить отрицательные числа на положительные и наоборот (минусовые на плюсовые значения)

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


Рассмотрим на примере следующей таблицы:

Excel: Как изменить отрицательные числа на положительные и наоборот (минусовые на плюсовые значения) Microsoft Excel, Google Таблицы, Пособие, Видео, Длиннопост

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

Excel: Как изменить отрицательные числа на положительные и наоборот (минусовые на плюсовые значения) Microsoft Excel, Google Таблицы, Пособие, Видео, Длиннопост

Поэтому формулу нужно поправить. Вписываем следующее:

Excel: Как изменить отрицательные числа на положительные и наоборот (минусовые на плюсовые значения) Microsoft Excel, Google Таблицы, Пособие, Видео, Длиннопост

Прописав эту формулу, протягиваем её на соответствующий таблице диапазон:

Excel: Как изменить отрицательные числа на положительные и наоборот (минусовые на плюсовые значения) Microsoft Excel, Google Таблицы, Пособие, Видео, Длиннопост

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

Excel: Как изменить отрицательные числа на положительные и наоборот (минусовые на плюсовые значения) Microsoft Excel, Google Таблицы, Пособие, Видео, Длиннопост

Всё готово. Вспомогательные временные столбцы после этого можно удалить.

Excel: Как изменить отрицательные числа на положительные и наоборот (минусовые на плюсовые значения) Microsoft Excel, Google Таблицы, Пособие, Видео, Длиннопост

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


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

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

Excel: Как заполнить пустые ячейки снизу значениями

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

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

Поэтому, в этом посте я хочу рассказать, как быстро и просто привести подобную показанную слева неполную таблицу к требуемому полному виду исключительно с помощью стандартных инструментов Excel, не прибегая к использованию Power Query или VBA.


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

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

Для этого первым шагом, нужно выбрать все пустые ячейки в этих столбцах. Естественно, делать это следует не вручную, а автоматизировано. Выбираем для этого целиком первый и второй столбец и затем используем комбинацию клавиш CTRL+F, для того чтобы открыть окно поиска значений:

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

Здесь, поле «Найти», оставляем пустым, мы же пустые ячейки ищем, и нажимаем на «Найти все». В нижнем поле окна поиска Excel выведет список всех найденных пустых ячеек в выделенном диапазоне:

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

Поскольку первая ячейка в списке уже выбрана (можно заметить это по синему выделению), воспользовавшись сразу комбинацией CTRL+A, мы можем выбрать все эти пустые ячейки:

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

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


Следующим пунктом просто нажимаем на клавишу «Равно» на клавиатуре и у нас вставляется символ равенства в активной ячейке:

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

Теперь вводим ссылку на соседнюю сверху ячейку:

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

… и вводим данную формулу во все пустые выбранные ячейки при помощи комбинации клавиш CTRL+Enter:

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

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

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

Готово!

Excel: Как заполнить пустые ячейки снизу значениями Microsoft Excel, Лайфхак, Пособие, Видео, Длиннопост

Вот мы и рассмотрели простейший способ дополнения и протягивания значений в таблицах Excel без применения Power Query или VBA.


Вот в этом видео я еще более подробно и наглядно рассмотрел тему сегодняшнего поста, так что предлагаю тебе его также посмотреть:

Показать полностью 9 1
Отличная работа, все прочитано!