Системный и Бизнес-анализ | Microsoft Excel

2021-11-29 10:00:18 ​​Создание собственной функции

Функция Знак_числа(), приведенная в данном примере, имеет один аргумент. Она возвращает текстовую строку Положительное, если ее аргумент больше 0, Отрицательное, если он меньше 0, и Нуль, если он равен 0.

Function Знак_числа(num)
If IsNumeric(num) Then
Select Case num
Case Is < 0
Знак_числа = "Отрицательное"
Case 0
Знак_числа = "Нуль"
Case Is > 0
Знак_числа = "Положительное"
End Select
Else
Знак_числа = ""
End If
End Function

Если ввести на рабочий лист формулу, в которой используется функция Знак_числа(), то Excel вычислит эту функцию и отобразит результат. Таким образом, эта функция работает аналогично любым другим встроенным функциям рабочих листов. Чтобы вставить ее в формулу, нужно воспользоваться командой Формулы => Библиотека функций => Вставить функцию, после выбора которой откроется диалоговое окно Вставка функции. В этом окне все пользовательские функции находятся в категории Определенные пользователем. Выберите функцию Знак_числа и щелкните на кнопке ОК. Откроется диалоговое окно Аргументы функции для выбранной функции. Допускается также выполнять вложение пользовательских функций и их комбинирование с другими элементами в формулах.

#VBA Открыть/Комментировать

2021-11-26 10:17:36 ​​Программа создания списка формул

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

Sub СписокФормул()
'Объявление используемых переменных
Dim InputRange As Range
Dim OutputSheet As Worksheet
Dim OutputRow As Long
Dim Cell As Range
'Создание объекта диапазона
Set InputRange = ActiveSheet.UsedRange
'Добавление нового рабочего листа
Set OutputSheet = Worksheets.Add
'Переменная для строки вывода
OutputRow = 1
'Цикл по всем ячейкам входного диапазона
For Each Cell In InputRange
If Cell.HasFormula Then
OutputSheet.Cells(OutputRow, 1) = "'" & Cell.Address
OutputSheet.Cells(OutputRow, 2) = "'" & Cell.Formula
OutputRow = OutputRow + 1
End If
Next Cell
End Sub

Хотя код этой программы кажется сложным, на самом деле он относительно прост, если его последовательно проанализировать:
1. В макросе создается переменная InputRange. Этой переменной соответствует используемый диапазон ячеек активного рабочего листа (что позволяет проверить каждую ячейку этого листа).
2. В рабочую книгу вставляется новый лист, который присваивается переменной- объекту Outputsheet. Затем переменной OutputRow присваивается значение 1. В дальнейшем значение этой переменной будет возрастать с шагом 1.
3. В цикле For...Each проверяется каждая ячейка диапазона InputRange. Если ячейка содержит формулу, то ее адрес и формула записываются на лист Outputsheet, при этом значение переменной OutputRow увеличивается на 1.

#VBA Открыть/Комментировать

2021-11-25 12:01:17 black BLUE FRIDAY: онлайн-курсы по дашбордам в Excel со скидкой -55%.

Только на этой неделе Excel-комбо из четырех курсов всего за 9900 рублей вместо 23100 руб.:

Дашборд в Excel — азы визуализации данных
Обработка данных в Excel — лайфхаки и формулы
Отчет для управленца в Excel — бизнес-мышление в отчетах, 12 кейсов-дашбордов, создание дашбордов в Excel
Визуализация данных в презентациях — работа со слайдами

Кроме этого - скидки на курсы по Power BI и очные тренинги

Распродажа действуют до вечера воскресенья, 28 ноября

Успевайте на скидку -55%: https://bit.ly/bluefriday_excel_ms Открыть/Комментировать

2021-11-24 11:01:17 ​​Конструкция Select...Case

Конструкцию Select...Case удобно использовать, если нужно выбрать один из нескольких возможных вариантов. Следующий пример иллюстрирует использование конструкции Select...Case. Здесь проверяется значение в активной ячейке рабочего листа. И если оно меньше 0, то значение в ячейке выводится красным цветом, если равно 0 - синим цветом, а если больше 0 - зеленым цветом.

Sub Проверка_ячейки()
Select Case ActiveCell.Value
Case Is < 0
ActiveCell.Font.Color = vbRed 'Красный
Case 0
ActiveCell.Font.Color = vbBlue 'Синий
Case Is > 0
ActiveCell.Font.Color = vbGreen 'Зеленый
End Select
End Sub

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

#VBA Открыть/Комментировать

2021-11-23 10:01:28 Конструкция With...End With

Еще одна конструкция, с которой вы встретитесь при записи макросов, - With...End With. Это вариант сокращения записи, удобный в тех случаях, когда требуется обратиться сразу к нескольким свойствам или методам одного и того же объекта.

Sub Выравнивание_ячеек()
With Selection
.HorizontalAlignment = xlCenter 'По центру
.VerticalAlignment = xlCenter 'По центру
.WrapText = False 'Ложь
.Orientation = xlHorizontal 'По горизонтали
End With
End Sub

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

Sub Выравнивание_ячеек()
Selection.HorizontalAlignment = xlCenter 'По центру
Selection.VerticalAlignment = xlCenter 'По центру
Selection.WrapText = False 'Ложь
Selection.Orientation = xlHorizontal 'По горизонтали
End Sub

#VBA Открыть/Комментировать

2021-11-22 12:00:16 ​​Цикл Do...loop

В циклах For...Next заключенный в рамки цикла набор операторов выполняется определенное количество раз. В циклах Do...loops выполнение заключенного в него набора операторов будет продолжаться до тех пор, пока выполняется определенное условие либо программа не будет остановлена.

Sub Сумма_квадратов_до_500()
Total = 0
num = 0
Do
num = num + 1
Total = Total + (num ^ 2)
Loop Until Total >= 500
MsgBox num & Space(1) & Total
End Sub

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

Do Until (Выполнять до...)
Do While (Выполнять пока...)
Loop Until (Повторять до...)
Loop While (Повторять пока...)

#VBA Открыть/Комментировать

2021-11-22 09:00:27 «Google Таблицы» — очень многогранный и функциональный инструмент с кучей возможностей и сценариев использования.
По одним только формулам можно написать отдельную книгу.
Кстати, Евгений Намоконов и Ренат Шагабутдинов, авторы первой в России книги о Google Таблицах, ведут Telegram канал, где в простой форме делятся всеми хитростями работы с этой программой.

В @google_sheets ребята покажут, что вообще можно делать в «Google Таблицах», помогут найти способы применения в своей работе и понять, в каком направлении копать, чтобы научиться работать в программе эффективно. Открыть/Комментировать

2021-11-19 10:00:16 ​Цикл For...Next

Цикл For...Next можно использовать, например, для обработки нескольких элементов определенное количество раз. Ниже приведен пример использования цикла For...Next.

Sub Сумма_квадратов ()
Total = 0
For Num = 1 To 10
Total = Total + (Num ^ 2)
Next Num
MsgBox Total
End Sub

В этом примере между операторами For и Next находится только один оператор. Этот единственный оператор выполняется в цикле 10 раз - переменная Num последовательно принимает значения от 1 до 10. Переменной Total присваивается сумма квадратов целых чисел от 1 до 10 (при этом последующее значение прибавляется к сумме предыдущих). Результат отображается в окне сообщения.

#VBA Открыть/Комментировать

2021-11-18 12:01:00 ​​Условный оператор If…Then

Одним из наиболее важных элементов управления в языке VBA является конструкция If...Then. Этот часто используемый оператор используется в тех случаях, когда в программе необходимо принять определенное решение в зависимости от сложившейся ситуации. Основной синтаксис структуры If...Then выглядит следующим образом:

If Условие Then Операторы [Else Операторы1]

Это означает, что если Условие выполняется, то будет выполнена группа Операторы. Если будет задано и предложение Else, то в случае, если Условие не выполняется, будет выполнена группа Операторы1, которая находится в предложении Else.
Ниже приведен пример использования этой конструкции (без ветви Else). Данная подпрограмма проверяет содержимое активной ячейки. Если в этой ячейке содержится отрицательное число, то оно выводится красным цветом. В противном случае ничего не происходит.

Sub Проверка_ячейки()
If ActiveCell.Value < 0 Then ActiveCell.Font.Color = vbRed ' Красный
End Sub

Вот еще один пример использования данного оператора, но уже с предложением Else. Так как здесь используется несколько строк, необходимо указать и оператор End If. Эта процедура окрашивает содержимое активных ячеек в красный цвет, если они содержат отрицательное число, и в зеленый цвет, если они содержат положительное число.

Sub Проверка_ячейки()
If ActiveCell.Value < 0 Then
ActiveCell.Font.Color = vbRed ' Красный
Else
ActiveCell.Font.Color = vbGreen ' Зеленый
End If
End Sub

#VBA Открыть/Комментировать

2021-11-18 10:01:00 Онлайн-курс Excel. 3.5 часа практики. Начните бесплатно
https://excel-pro.ru/ Открыть/Комментировать