На примере 0 варианта разберем пользовательские функции в Excel - создание и использование. Остальные варианты будут описаны в следующих постах. Ссылки на первую часть – внизу сообщения.
Задание
1. Создать на рабочем листе таблицу, структура которой приведена на рисунке.
2. Столбцы с первого по предпоследний заполнить произвольными данными (примерно 15 – 20 строк).
Последний столбец не заполняется, его данные рассчитываются в следующем задании.
Учесть, что одно и то же предприятие обычно выпускает разные виды продукции, одна и та же продукция может выпускаться на различных предприятиях. Поэтому и продукция, и предприятия повторяются в таблице.
3. В столбце I (последнем) для каждой строки вывести номера месяцев с максимальным выпуском (таких номеров может быть несколько). Если номеров несколько, то они должны разделяться пробелом.
Для вычисления создать и использовать пользовательскую функцию, возвращающую номера максимальных элементов числового массива.
Решение
Составим таблицу в Excel и заполним ее данными:
Перейдем в редактор Visual Basic (Alt+F11) и создадим модуль (Insert->Module). В модуль добавим нашу функцию – MaxMonths. Функцию можно вставить через меню – Insert->Procedure или написать вручную. При вставке через меню нужно изменить тип с Sub на Function, поскольку она будет возвращать значение.
Передавать массив в функцию можно несколькими способами, например, выбирать отдельные значения. Тогда аргументом функции будет именно массив значений заданного размера. Возможно, задание как раз на это и рассчитано, потому что предусмотрено всего 6 месяцев (да и в остальных вариантах тоже по 6 значений). Или можно передавать массив переменного размера. Оба эти способа мы рассматривали в первой части задания, когда применяли функции в Access.
Но сейчас мы находимся в Excel, здесь все гораздо проще, и надо этим пользоваться. Вообще на Excel можно сделать неплохую базу данных, если знать его возможности.
Так вот, аргументом функции будет диапазон ячеек. То есть Excel все сделает за нас, нам остается только написать функционал, а при использовании выбрать диапазон ячеек.
Диапазон – это объект Range. По сути, это двухмерный массив, состоящий из строк и столбцов, для перебора ячеек в строках и столбцах используются циклы. Для доступа к ячейкам можно использовать синтаксис rng(r, c), где rng – диапазон ячеек, r – номер строки, c – номер столбца. Отсчет строк и столбцов ведется с первой строки и с первого столбца диапазона (а не рабочего листа).
Число строк в диапазоне rng – rng.Rows, Count. Число столбцов – rng.Columns.Count.
Текст функции:
На рабочем листе установим курсор во вторую строку последнего столбца и нажмем кнопку Fx – мастер функций. В категории выберем «Определенные пользователем»
В списке функций выберем нашу функцию:
Появится вот такое окно:
Предлагается выбрать диапазон ячеек. Выделим мышью шесть ячеек с выпуском в первой строке:
Жмем ОК и получаем результат.
Исправим еще кое-что. Как видно из рисунка, наша функция не имеет описания, то есть пользователю непонятно, что она делает. Добавим в нее описание.
Для этого нужно создать и один раз выполнить процедуру:
Видим, что у функции появилось описание. Понятно, что оно не обязано быть именно таким, можете сделать, какое надо.
Сейчас копируем эту функцию на все ячейки нашего диапазона месяцев и получаем результат:
Продолжение следует
Общий список всех сообщений по этой работе
Задание
1. Создать на рабочем листе таблицу, структура которой приведена на рисунке.
2. Столбцы с первого по предпоследний заполнить произвольными данными (примерно 15 – 20 строк).
Последний столбец не заполняется, его данные рассчитываются в следующем задании.
Учесть, что одно и то же предприятие обычно выпускает разные виды продукции, одна и та же продукция может выпускаться на различных предприятиях. Поэтому и продукция, и предприятия повторяются в таблице.
3. В столбце I (последнем) для каждой строки вывести номера месяцев с максимальным выпуском (таких номеров может быть несколько). Если номеров несколько, то они должны разделяться пробелом.
Для вычисления создать и использовать пользовательскую функцию, возвращающую номера максимальных элементов числового массива.
Решение
Составим таблицу в Excel и заполним ее данными:
Перейдем в редактор Visual Basic (Alt+F11) и создадим модуль (Insert->Module). В модуль добавим нашу функцию – MaxMonths. Функцию можно вставить через меню – Insert->Procedure или написать вручную. При вставке через меню нужно изменить тип с Sub на Function, поскольку она будет возвращать значение.
Передавать массив в функцию можно несколькими способами, например, выбирать отдельные значения. Тогда аргументом функции будет именно массив значений заданного размера. Возможно, задание как раз на это и рассчитано, потому что предусмотрено всего 6 месяцев (да и в остальных вариантах тоже по 6 значений). Или можно передавать массив переменного размера. Оба эти способа мы рассматривали в первой части задания, когда применяли функции в Access.
Но сейчас мы находимся в Excel, здесь все гораздо проще, и надо этим пользоваться. Вообще на Excel можно сделать неплохую базу данных, если знать его возможности.
Так вот, аргументом функции будет диапазон ячеек. То есть Excel все сделает за нас, нам остается только написать функционал, а при использовании выбрать диапазон ячеек.
Диапазон – это объект Range. По сути, это двухмерный массив, состоящий из строк и столбцов, для перебора ячеек в строках и столбцах используются циклы. Для доступа к ячейкам можно использовать синтаксис rng(r, c), где rng – диапазон ячеек, r – номер строки, c – номер столбца. Отсчет строк и столбцов ведется с первой строки и с первого столбца диапазона (а не рабочего листа).
Число строк в диапазоне rng – rng.Rows, Count. Число столбцов – rng.Columns.Count.
Текст функции:
'Функция возвращает номера месяцев с максимальным выпускомСначала определяем, как максимум, выпуск 1 месяца (это первая ячейка диапазона). В строку-результат вносим цифру «1» - первый месяц. Затем в цикле перебираем все остальные ячейки и ищем значения, бОльшие или равные текущему максимуму. Если значение больше, то устанавливаем новый максимум и начинаем строку снова, с номера этого месяца. Если значение равно максимуму, то номер месяца добавляем в строку. В заключение, смотрим, если максимальный выпуск равен 0, то есть в полугодии ничего не выпускалось, то возвращаем «0», иначе возвращаем строку с номерами месяцев.
Public Function MaxMonths(rng As Range) As String
Dim i As Integer 'счетчик цикла
Dim str As String 'строка с номерами месяцев
Dim max As Integer 'максимальный выпуск
'начинаем с первого месяца
str = "1"
'устанавливаем максимум как объем 1 месяца
max = CInt(rng(1, 1))
'цикл по всем месяцам диапазона
For i = 2 To rng.Columns.Count
'если максимум меньше текущего значения, то устанавливаем новый
'максимум и начинаем строку с номерами сначала
If max < CInt(rng(1, i)) Then
max = CInt(rng(1, i))
str = CStr(i)
'если максимум равен текущему значению, то нужно в строку
'добавить номер еще одного месяца
ElseIf max = CInt(rng(1, i)) Then str = str + " " + CStr(i)
End If
Next i
'если продаж не было в течение всего полугодия, то возвращаем 0
If max = 0 Then
MaxMonths = "0"
'иначе возвращаем строку с номерами месяцев
Else: MaxMonths = str
End If
End Function
На рабочем листе установим курсор во вторую строку последнего столбца и нажмем кнопку Fx – мастер функций. В категории выберем «Определенные пользователем»
В списке функций выберем нашу функцию:
Появится вот такое окно:
Предлагается выбрать диапазон ячеек. Выделим мышью шесть ячеек с выпуском в первой строке:
Жмем ОК и получаем результат.
Исправим еще кое-что. Как видно из рисунка, наша функция не имеет описания, то есть пользователю непонятно, что она делает. Добавим в нее описание.
Для этого нужно создать и один раз выполнить процедуру:
Sub InstallFunc()Добавим ее в модуль и выполним. После этого снова возвращаемся на рабочий лист и запускаем Мастер функций:
Application.MacroOptions Macro:="MaxMonths", _
Description:="возвращает номера месяцев с максимальным выпуском"
End Sub
Видим, что у функции появилось описание. Понятно, что оно не обязано быть именно таким, можете сделать, какое надо.
Сейчас копируем эту функцию на все ячейки нашего диапазона месяцев и получаем результат:
Продолжение следует
Общий список всех сообщений по этой работе