Как добавлять подсказки и описание в функции excel: Как поместить подсказку в пользовательскую функцию

Содержание

Как поместить подсказку в пользовательскую функцию

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

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

   = interpolateLinear() 
or
   = interpolateLinear_Help()

откроется окно msgBox с текстом справки. MsgBox ограничен ~1000 символами, возможно, это 1024. Но этого достаточно (едва 8^/) для моей чрезмерно обманутой функции интерполяции. Если это не так, вы всегда можете открыть форму и идем в город.

при первом открытии окна сообщения он выглядел как успех. Но есть пара проблем. Во-первых, конечно, пользователь должен знать, чтобы ввести функцию без аргументов (+1 для суффикса _Help UDF).

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

Я не знаю, почему Excel чувствует необходимость пересчитывать формулу снова и снова; ни автономный _Help, ни полная версия (в режиме справки) не имеют прецедентов или зависимых. Нет приложение.Летучий заявление в любом месте. Конечно, функция возвращает значение вызывающей ячейке. Может быть, это запускает recalc? Но это то, что делают UDFs. Я не думаю, что вы можете не возврат a значение.

Так как вы не можете изменить рабочий лист формула из UDF я попытался вернуть определенную строку —a стоимостью —к вызывающей ячейке (единственной, которую вы можете изменить значение из UDF), полагая, что я проверю значение ячейки с помощью приложение.абонент в следующем цикле найдите мою строку и знайте, что не нужно повторно отображать сообщение справки. В то время это казалось хорошей идеей-не сработало. Может быть, я сделал что-то глупое в мой сплю государства. Мне все еще нравится эта идея. Я обновлю это, когда (если) я исправлю проблему. Моим быстрым решением было добавить строку в поле справки:»обратиться за помощью только в чрезвычайной ситуации. Удалите оскорбительную формулу, чтобы положить конец страданиям.

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

Как добавить описание аргументов функции в Excel — Трюки и приемы в Microsoft Excel

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

Вы можете использовать диалоговое окно Мастер функций для вставки пользовательской функции VBA в формулу, но для описания аргументов не откроется окно Аргументы функции. Однако новая возможность Excel позволяет создавать описания аргументов для пользовательских функций VBA.

Чтобы продемонстрировать, как это работает, я буду использовать функцию EXTRACTELEMENT, приведенную в статье «Как в Excel при помощи VBA создать простую функцию листа«:

Function EXTRACTELEMENT(Txt, n, Separator) As String
'	Возвращает n-й элемент текстовой строки
'	где элементы разделены определенным символом-разделителем
    Dim AllElements As Variant
    AllElements = Split(Txt, Separator)
    EXTRACTELEMENT = AllElements(n - 1)
End Function

Function EXTRACTELEMENT(Txt, n, Separator) As String
‘ Возвращает n-й элемент текстовой строки
‘ где элементы разделены определенным символом-разделителем
Dim AllElements As Variant
AllElements = Split(Txt, Separator)
EXTRACTELEMENT = AllElements(n — 1)
End Function

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

Sub SpecifyDescriptions()
    Dim D0 As String, D1 As String
    Dim D2 As String, D3 As String
        D0 = "Возвращает указанный элемент из строки, которая использует символ-разделитель"
        D1 = "Текстовая строка, из которой вы выполняете извлечение"
        D2 = "Целое число, обозначающее элемент, который будет извлечен"
        D3 = "Отдельный символ, используемый в качестве разделителя"
        Application.MacroOptions _
            Macro:="EXTRAELEMENT", _
            Description:=D0, _
            ArgumentDescriptions:=Array(D1, D2, D3)
End Sub

Sub SpecifyDescriptions()
Dim D0 As String, D1 As String
Dim D2 As String, D3 As String
D0 = «Возвращает указанный элемент из строки, которая использует символ-разделитель»
D1 = «Текстовая строка, из которой вы выполняете извлечение»
D2 = «Целое число, обозначающее элемент, который будет извлечен»
D3 = «Отдельный символ, используемый в качестве разделителя»
Application.MacroOptions _
Macro:=»EXTRAELEMENT», _
Description:=D0, _
ArgumentDescriptions:=Array(D1, D2, D3)
End Sub

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

Как добавить описание(подсказку) к UDF и аргументам UDF

Хитрости »
27 Август 2020 Дмитрий 467 просмотров

Почти каждый, кто освоил написание собственных функций пользователя(UDF)(если еще не знаете что это такое — Что такое функция пользователя(UDF)?), рано или поздно сталкивается с тем, что справка для них недоступна, описание функции и её аргументов тоже. Т.е. по умолчанию окно вызова UDF, в отличии от встроенных функций, выглядит очень неинформативно:

А хотелось бы как минимум так:

И возникает вопрос – а возможно ли вообще добавить описание к функции и аргументам? Можно. И есть как разные способы, так и нюансы у каждого из них:

Добавление описания к UDF без «кодинга»
Это самый простой способ. Покажу на примере простой функции:

Function ТекущаяДата()
    ТекущаяДата = Date
End Function

Function ТекущаяДата()
ТекущаяДата = Date
End Function

Что надо сделать? Для начала надо заменить Function на Sub:

Sub ТекущаяДата()
    ТекущаяДата = Date
End Sub

Sub ТекущаяДата()
ТекущаяДата = Date
End Sub

После этого переходим в окно Excel -вкладка Разработчик(Developer) —Макросы(Macros). Находим там переименованную в Sub функцию -нажимаем кнопку Параметры(Options) и в появившемся окне заполняем поле Описание(Description):

Нажимаем Ок, закрываем первое окно(с перечнем макросов). После этого переименовываем обратно Sub в Function. Сохраняем файл. Переходим в диспетчер функций и в категории Определенные пользователем(User Defined) находим нашу функцию «ТекущаяДата». Выделяем и видим, что для неё теперь есть описание:

Добавление описания к UDF кодом VBA(все версии)

Тоже самое можно сделать и при помощи кода. Он несложный и работает(как и следовало ожидать) во всех версиях:

Sub RegisterUDF_AllVersions()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату"
End Sub

Sub RegisterUDF_AllVersions()
Application.MacroOptions _
Macro:=»ТекущаяДата», _
Description:=»Возвращает в ячейку текущую дату»
End Sub

Основные моменты:

Macro – здесь записываем имя функции. Текст буковка в буковку должен совпадать с именем нашей функции, иначе получим ошибку «Метод ˈMacro Optionsˈ of object ˈ_Applicationˈ failed», т.к. VBA не найдет сопоставления записанного текста с реальной процедурой или функцией.
Description – это и есть наше описание. Здесь маленький нюанс: текст описания не должен превышать 255 символов. Иначе получим все ту же ошибку «Метод ˈMacro Optionsˈ of object ˈ_Applicationˈ failed».

И очень важный момент: вызов Application.MacroOptions должен происходить исключительно тогда, когда открыта и отображена хотя бы одна книга. Иначе опять же получим все ту же ошибку «Метод ˈMacro Optionsˈ of object ˈ_Applicationˈ failed». И конечно, код регистрации функций таким образом надо делать каждый раз при открытии книги с UDF, т.к. сразу после закрытия книги с UDF регистрация функций сбрасывается(что логично, т.к. сами UDF становятся недоступными). Поэтому процедуру RegisterUDF_AllVersions правильнее всего запускать в момент открытия книги через событие Workbook_Open(о том где искать это событие можно почитать в этой статье: Модуль книги)
В отличии от «ручного» метода – через MacroOptions можно указать в какую категорию добавить свою функцию. Добавить можно как в существующие(Математический, Дата и время и т.п.), так и в собственную. Правильнее всего либо не указывать категорию, либо делать это в собственной(чтобы не было путаницы среди встроенных функций).
Код добавления в собственную категорию:

Sub RegisterUDF_AllVersions()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату", _
            Category:="Excel-VBA.ru"
End Sub

Sub RegisterUDF_AllVersions()
Application.MacroOptions _
Macro:=»ТекущаяДата», _
Description:=»Возвращает в ячейку текущую дату», _
Category:=»Excel-VBA.ru»
End Sub

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

Номер	Категория	Категория на англ
1	Финансовые	Financial
2	Дата и время	Date & Time
3	Математические	Math & Trig
4	Статистические	Statistical
5	Ссылки и массивы	Lookup & Reference
6	Работа с базой данных	Database
7	Текстовые	Text
8	Логические	Logical
9	Информация	Information
14	Определенные пользователем	User Defined

Например, чтобы добавить нашу функцию «ТекущаяДата» в категорию Дата и Время необходимо указать номер категории 2:

Sub RegisterUDF_AllVersions()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату", _
            Category:=2
End Sub

Sub RegisterUDF_AllVersions()
Application.MacroOptions _
Macro:=»ТекущаяДата», _
Description:=»Возвращает в ячейку текущую дату», _
Category:=2
End Sub

Добавление описания к UDF и её аргументам в Excel(Excel 2010 и выше)

Однако описанными способами можно добавить описание только к самой функции – аргументы так и останутся без пояснений.
Но начиная с версии 2010 в Excel появилась возможность через MacroOptions добавить описание и к аргументам. Это делается несложно.
Добавим для нашей функции ТекущаяДата аргумент:

Function ТекущаяДата(Только_месяц As Boolean)
    If Только_месяц Then
        ТекущаяДата = Month(Date)
    Else
        ТекущаяДата = Date
    End If
End Function

Function ТекущаяДата(Только_месяц As Boolean)
If Только_месяц Then
ТекущаяДата = Month(Date)
Else
ТекущаяДата = Date
End If
End Function

Аргумент Только_месяц будет отвечать за то, как записать дату: либо как дату в формате даты-времени Excel, либо только номер месяца. Т.к. тип назначен Boolean, то и указывать при записи функции мы должны либо ИСТИНА(TRUE) либо ЛОЖЬ(FALSE). Добавим описание через MacroOptions:

Sub RegisterUDF_2010()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату", _
            Category:="Excel-VBA.ru", _
            ArgumentDescriptions:=Array("ИСТИНА, если в ячейку необходимо записать только номер месяца")
End Sub

За описания к аргументам отвечает параметр ArgumentDescriptions. Обращаю внимание, что передавать описания необходимо именно через массив(Array). И текст внутри кавычек не должен превышать 255 символов. Иначе явной ошибки выполнения хоть и не будет, но и описание к аргументам не добавится.
Если у нас более одного аргумента – то в Array мы записываем текст в кавычках через запятую. Например, добавим еще один аргумент: МесяцКакЧисло. Он отвечает за то, записать месяц его порядковым числом или записать его имя. Функция примет вид:

Function ТекущаяДата(Только_месяц As Boolean, МесяцКакЧисло As Boolean)
    If Только_месяц Then
        If МесяцКакЧисло Then
            ТекущаяДата = Month(Date)
        Else
            ТекущаяДата = MonthName(Month(Date))
        End If
    Else
        ТекущаяДата = Date
    End If
End Function

Function ТекущаяДата(Только_месяц As Boolean, МесяцКакЧисло As Boolean)
If Только_месяц Then
If МесяцКакЧисло Then
ТекущаяДата = Month(Date)
Else
ТекущаяДата = MonthName(Month(Date))
End If
Else
ТекущаяДата = Date
End If
End Function

И соответственно, добавляем описание для второго аргумента:

Sub RegisterUDF_2010()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату", _
            Category:="Excel-VBA.ru", _
            ArgumentDescriptions:= _
                Array("ИСТИНА, если в ячейку необходимо записать только номер месяца", _
                      "ИСТИНА, если в ячейку необходимо записать имя месяца(если аргумент 'Только_месяц' = ИСТИНА)")
End Sub

Sub RegisterUDF_2010()
Application.MacroOptions _
Macro:=»ТекущаяДата», _
Description:=»Возвращает в ячейку текущую дату», _
Category:=»Excel-VBA.ru», _
ArgumentDescriptions:= _
Array(«ИСТИНА, если в ячейку необходимо записать только номер месяца», _
«ИСТИНА, если в ячейку необходимо записать имя месяца(если аргумент ‘Только_месяц’ = ИСТИНА)»)
End Sub

Здесь важно помнить, что описания к аргументам должны идти в том же порядке, в котором записаны сами аргументы для функции. Иначе описания будут назначены неправильно. Длина описания для каждого аргумента(не всего текста внутри Array!) не должна превышать 255 символов.

Добавление описания к UDF и её аргументам в Excel(Excel 2007 и ниже)

А как быть с п

Как сделать подсказку в excel?

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

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

Как сделать всплывающую подсказку?

Чтобы сделать всплывающую подсказку для ячейки выберем на панели вкладок Данные -> Работа с данными -> Проверка данных и в открывшемся окне перейдем в центральную вкладку:

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

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

В MS Excel есть ряд очень полезных инструментов для удивительно простого создания по-настоящему сложных форм сбора информации. Однако, вот незадача — чаще всего камнем преткновения во взаимодействии с ними служат не возможности табличного редактора, а человеческая «косорукость». Пользователи стирают формулы из ячеек, вносят некорыстные данные, в общем, портят плоды наших трудов.

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

Вот и моя демонстрационная excel-таблица. Как её заполнять?

Подсказки в MS Excel

Первый способ вставки подсказки

Первым делом разберемся c подсказками. Взгляните на мою табличку. Вроде бы все просто, однако даже при такой простоте можно нагородить солидный огород. К примеру, поле «номер документ» — это №1, 1, или 22.03-1?

Самый проcтой способ вставить примечание в лист MS Excel

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

Примечания вставлено

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

Удалить примечания можно только из контекстного меню правой кнопки мыши

Обратите внимание: нажатие кнопки «Delete» в ячейке не удалит примечание. Избавиться от него (или изменить его текст) можно повторно щелкнув в ячейке правой кнопкой мыши и выбрав пункт «Удалить примечание» или «Изменить примечание».

Второй способ вставки подсказки

Ещё один способ ввести подсказку интересен нам только с точки зрения того, о чем пойдет разговор дальше (фильтрации ввода), тем не менее знать о нем полезно.

Примечания в Excel с помощью проверки данных

Поставьте курсор мыши в выбранную ячейку, на панели «Данные» в группе «Работа с данным» откройте инструмент «Проверка данных«, и заполните поля «заголовок» и «текст сообщения» на вкладке «Сообщение для ввода«. Нажмите «Ок».

Теперь, при щелчке на ячейке с подсказкой (обратите внимание — отмеченная таким способом ячейка не помечается красным треугольником, и выглядит так же как любая другая) также будет выведен его текст в желтом прямоугольнике. Удалить и изменить примечание введенное через «Проверку данных» с помощью правой кнопки мыши, нельзя.

Проверка по числу введенных символов

Подсказки — это хорошо, но что делать с самыми «прогрессивными» пользователями, которые подсказок не читают? Остается одно — бить по рукам… то есть использовать фильтрацию ввода, я хотел сказать.

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

Проверка по числу введенных символов в MS Excel

Первое, что может оказаться полезным, это проверка по длине введенного текста. Выбираем пункт «Длина текста» и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.

Слишком длинный текст! Excel стоит на страже и не пропустит такой очевидной ошибки

Проверка по числу

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

Проверка по числу в Excel

Фильтрация на ввод чисел работает ничуть не хуже

Выбор из имеющегося списка значений

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

А теперь, сделаем проверку по выпадающему списку в MS Excel. Данные введем вручную, через точку с запятой.

Выбираем в «Проверке данных» вкладку «Параметры«, и указываем «Тип данных» → «Список». Ввести «предустановленные» значения можно двумя способами:

Выбрать диапазон данных с помощью кнопки «Диапазон данных».
Вписать значения выпадающего списка вручную, через точку с запятой.

…или выберем из диапазона

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

Выбор из списка на листе MS Excel. Других данных ввести не удастся.

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

Кстати, можно изменить и её текст — для этого перейдите на вкладку «Сообщение для вывода» в «Проверке данных», как мы уже делали при «втором способе» вставке подсказок, измените текст на требуемый.

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

01.01.2001;
01/01/2001;
1 января 2001 года и т.д.

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

Где находится?

Для настройки параметров проверки вводимых значений необходимо на вкладке «Данные» в области «Работа с данными» кликнуть по иконке «Проверка данных» либо выбрать аналогичный пункт из раскрывающегося меню:

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

Настройка условия проверки

Изначально требуется выбрать тип проверяемых данных, что будет являться первым условием. Всего предоставлено 8 вариантов:

Целое число;
Действительное число;
Список;
Дата;
Время;
Длина текста;
Другой.

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

Самым необычным видом является выпадающий список.

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

Всплывающая подсказка ячейки Excel

Функционал проверки данных в Excel позволяет настраивать всплывающие подсказки для ячеек листа. Для этого следует перейти на вторую вкладку окна проверки вводимых значений – «Сообщение для ввода».

На изображении показан пример возможного сообщения для ячейки, в которую вносятся целые числа от 1 до 31 (настройки вкладки «Параметры»). Заголовок и сообщения указываются по Вашему усмотрению, каких-либо правил к их оформлению нет. Не забудьте установить галочку на поле «Отображать подсказку, если ячейка является текущей», иначе сообщение будет деактивировано.

Пример всплывающей подсказки в Excel:

Вывод сообщения об ошибке

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

Существует три варианта сообщений, отличающихся по поведению:

Останов;
Предупреждение;
Сообщение.

Останов является сообщением об ошибке и позволяет произвести только 2 действия: отменить ввод и повторить ввод. В случае отмены новое значение будет изменено на предыдущее. Повтор ввода дает возможность скорректировать новое значение.

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

Сообщение выводить ошибку в виде простой информации и дает возможность отменить последнее действие.

Заголовок и сообщение заполняются по Вашему желанию.

Пример вывода одной и той же ошибки, но под разными видами:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

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

Способ 1 — горячие клавиши и раскрывающийся список в excel

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

Этот же пункт меню можно запустить сочетанием клавиш Alt+»Стрелка вниз» и программа автоматически предложит в выпадающем списке значения ячеек, которые вы ранее заполняли данными. На изображении ниже программа предложила 4 варианта заполнения (дублирующиеся данные Excel не показывает). Единственное условие работы данного инструмента — это между ячейкой, в которую вы вводите данные из списка и самим списком не должно быть пустых ячеек.

Использование горячих клавиш для раскрытия выпадающего списка данных

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

Выпадающий список может работать и в верхней части с данными, которые ниже ячейки

Способ 2 — самый удобный, простой и наиболее гибкий

Данный способ предполагает создание отдельных данных для списка. При чем данные могут находится как на листе с таблицей, так и на другом листе файла Excel.

Сперва необходимо создать список данных, который будет источником данных для подстановки в выпадающий список в excel. Выделите данные и нажмите правой кнопкой мыши. В выпадающем списке выберите пункт «Присвоить имя…».
Создание набора данных для списка
В окне «Создание имени» задайте имя для вашего списка (это имя дальше будет использоваться в формуле подстановки). Имя должно быть без пробелов и начинаться с буквы.
Введите имя для набора данных
Выделите ячейки (можно сразу несколько ячеек), в которых планируется создать выпадающий список. Во вкладке «ДАННЫЕ» вверху документа нажмите на «Проверка данных».
Создать выпадающий список можно сразу для нескольких ячеек
В окне проверка вводимых значение в качестве типа данных задайте «Список». В строке «Источник:» введите знак равно и имя для ранее созданного списка. Данная формула позволит ввести значения только из списка, т.е. произведет проверку введенного значения и предложит варианты. Эти варианты и будут выпадающим списком.

Для создания проверки вводимых значений введите имя ранее созданного списка

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

Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку

А при нажатии на кнопку выпадающего списка в ячейке вы увидите перечень значений из созданного ранее.

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

Нажмите на «Файл» в левом верхнем углу приложения.
Выберите пункт «Параметры» и нажмите на него.
В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

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

Но нас на этапе создания интересуют только три основных:

ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

Как видите получился выпадающий список в excel с подстановкой данных из второго столбца с данными «Поставщик».

Всплывающие подсказки в excel Excelka.ru

Отображение и скрытие всплывающих подсказок

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

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

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

Отображение и скрытие всплывающих подсказок

Откройте вкладку Файл.

Выберите пункт Параметры.

Выберите категорию Общие.

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

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

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

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

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

Примечание: В видео показан пользовательский интерфейс Office 2010, но он по-прежнему предоставляет вам хорошее представление о том, как работает функция в Office 2013.

В следующих программах выпуск 2007 системы Microsoft Office: Access, Excel, PowerPoint или Word

Нажмите кнопку Microsoft Office и выберите пункт Параметры Access, Параметры Excel, Параметры PowerPointили Параметры Word.

Выберите категорию Основные.

В разделе Основные параметры работы с Access, основныепараметры работы с Excel, Основные параметрыработы с приложением PowerPoint и основные параметры работы с приложением Word в списке стиль всплывающих подсказок выберите нужный параметр. :

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

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

Не показывать всплывающие подсказки Этот параметр отключает всплывающие подсказки и улучшенные всплывающие подсказки.

В следующих программах выпуск 2007 системы Microsoft Office: Visio, InfoPath, OneNote, Publisher, SharePoint Designer или Outlook

В меню Сервис выберите пункт Настройка.

Откройте вкладку Параметры .

В разделе другиеустановите или снимите флажок Показывать подсказки для кнопок .

В Microsoft Office Project 2007

В меню Сервис наведите указатель мыши на пункт Настройкаи выберите пункт панели инструментов.

На вкладке Параметры в группе другиеустановите или снимите флажок Показывать подсказки для кнопок .

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Создание всплывающих подсказок в Exсel

Прочее. Программное обеспечение

Наткнулся в интернете на файл тов. Яркина по расчету оснований и фундаментов и обнаружил очень полезную вещь-при наведении на ячейку появляется всплывающая подсказка.
Подскажите как это сделать??

PS: Файлик прилагается

Сообщение от Sleekka:
Это называется аннотации — сделать просто уже не помню как единственное что могу сказать что они сворачиваются в ноль, когда строки вставляешь, так что не очень советую их использовать.

Спасибо. Строки вставлять необходимости нет, так как делаю что-то наподобие этого калькулятора для внутреннего пользовании на работе.
Как их сделать пока не нашел,у меня 2007 Exel

Сообщение от уDAV:
Выбрать ячейку. Нажать правую кнопку мыши. В контекстном меню выбрать- Вставить примечание

Не понял? Дак чего надо то? TIK, поясни?

P.S. Исправь опечатку в названии, пишется не Exel а Excel, если кто то будет искать ответ на подобный вопрос, то он не сможет найти этой темы из-за ошибки.

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

Сообщение от TIK:
Да к моему сожалению именно так🙂 Мы занимаемся малоэтажным строительством, от проектировщиков требуют считать кол-во саморезов,шурупов,гвоздей и монтажной пены. Вроде бы все просто,но многие проектировщики не могут выполнить правильно даже этого, поэтому я и занимаюсь вот таким вот калькулятором🙂

От халатности и раздолбайства калькулятор вас не спасёт. Поможет только назначение финансовой ответственности. Накосячил – оплачивай свой ошибки из собственного кармана, хотя и это не во всех случаях действует.

Сообщение от Pavel Samofalov:
От халатности и раздолбайства калькулятор вас не спасёт. Поможет только назначение финансовой ответственности. Накосячил – оплачивай свой ошибки из собственного кармана, хотя и это не во всех случаях действует.

Понимаете дело даже не в халатности, вот сидит серьезный человек и не может понять как рассчитать кол-во баллонов монтажной пены обьемом 0,75л с заданным расходом в 150м.п. прямолинейного участка запенивания. Обьяснять начинаешь,понимает вроде,а как доходит дело до проекта так опять неправильно. И ничего сделать с этим не можем

Проверка данных Excel

01.01.2001;
01/01/2001;
1 января 2001 года и т.д.

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

Где находится?

Настройка условия проверки

Целое число;
Действительное число;
Список;
Дата;
Время;
Длина текста;
Другой.

Самым необычным видом является выпадающий список.

Всплывающая подсказка ячейки Excel

Пример всплывающей подсказки в Excel:

Вывод сообщения об ошибке

Существует три варианта сообщений, отличающихся по поведению:

Сообщение выводить ошибку в виде простой информации и дает возможность отменить последнее действие.

Заголовок и сообщение заполняются по Вашему желанию.

Пример вывода одной и той же ошибки, но под разными видами:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

Всплывающая подсказка в ячейке

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

На создание такой подсказки меня навел однажды вопрос на одном из форумов — как можно сделать удобный перевод текста из ячейки, чтобы он показывался при наведении на ячейку мышью? Не выделении — именно наведении. Можно использовать примечания(вкладка Рецензирование -Создать примечание). Но в случае с примечаниями есть несколько не очень удобных моментов: размеры примечания придется подгонять для каждой подсказки отдельно; примечания как правило будут показываться справа от ячейки, а не чуть ниже; при большом количестве примечаний файл может значительно увеличиться в размерах и некоторые возможности будут мешать их корректному отображению(например, если закрепить области, то примечание может и съехать и «срезаться»). Первое, что пришло в голову — а что если сделать как в гиперссылках (Что такое гиперссылка?)? Если точнее — что если именно эти подсказки использовать? Но как же тогда делать гиперссылку? Куда? Все просто — я решил, что можно создать гиперссылку на ячейку, в которой сама гиперссылка. Тогда даже при нажатии на ячейку с гиперссылкой нас никуда не перекинет. Я покажу как это можно сделать вручную и как это можно сделать при помощи кода VBA.
Для начала немного об исходных данных. У нас есть лист с ячейками, в которых надо создать подсказки и есть лист «справочник», в котором указано для каких значений какие должны быть подсказки.

РУЧНОЕ СОЗДАНИЕ ПОДСКАЗКИ
Выделяем ячейку, подсказку для которой хотим создать -правый клик мыши -Гиперссылка (Hyperlink). Откроется окно добавления гиперссылки.

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

В этом способе все хорошо, кроме двух вещей:

Если подсказок много, но создавать их весьма непростое занятие. Необходимость листа «справочник» при ручном методе так же ставится под сомнение
При создании гиперссылки формат ячейки автоматом изменяется. А для нашей цели это в большинстве случаев не надо. Это можно обойти, создавая подсказки вторым способом — при помощи кодов VBA.

СОЗДАНИЕ ПОДСКАЗКИ АВТОМАТИЧЕСКИ КОДОМ VBA
Здесь особо рассказывать нечего — лучше сначала скачать файл, приложенный к статье. Там есть лист «справочник», в котором забита пара значений для подсказок и лист, в котором подсказки создаются.
Как это работает. Выделяются ячейки для создания подсказок(в примере это D15:D16 ). И кнопкой запускается код, который в выделенных ячейках создаст всплывающие подсказки. Сам принцип: код просматривает каждую выделенную ячейку и если она не пустая запоминает её значение. Ищет это значение в первом столбце листа «справочник» и если находит — создает гиперссылку и значение из второго столбца листа «справочник»(в строке с найденным значением) вставляет в качестве подсказки к гиперссылке.
Но помимо этого код перед созданием гиперссылки запоминает большую часть форматирования текста в ячейке и после создания гиперссылки возвращает его. Хотя и здесь не без ложки дегтя — код не сможет корректно обработать и вернуть смешанное форматирование (например, разный цвет шрифта в одной ячейке, разный стиль и т.п.). Но подсказки при этом все равно будут созданы.
Что следует знать: код ищет каждое значение ячейки полностью, а не каждое слово отдельно. Это значит, что если в ячейке будет записано «аннуитентный платеж», а не просто «аннуитентный» как в примере — то подсказка для такой ячейки не будет создана.
Если добавить или изменить значения в листе «справочник», то для того, чтобы подсказки обновились необходимо запустить код заново. Внесение изменений в сам код при этом не требуется.
Созданные подсказки останутся в файле даже если удалить сам код. Т.е. если создать подсказки в файле, а затем файл переслать другому человеку — он сможет использовать подсказки без дополнительных действий.

Всплывающая подсказка в ячейке (59,0 KiB, 1 697 скачиваний)

Если кому-то лень качать сам пример — привожу так же код:

‘————————————————————————————— ‘ Author : The_Prist(Щербаков Дмитрий) ‘ Профессиональная разработка приложений для MS Office любой сложности ‘ Проведение тренингов по MS Excel ‘ http://www.excel-vba.ru ‘ Purpose: ‘————————————————————————————— Option Explicit ‘ Sub CreateTooltip() Dim rr As Range, rc As Range, rf As Range Dim wsDic As Worksheet Dim aParams(1 To 12) ‘лист в котором указано для каких значений какие должны быть подсказки Set wsDic = ThisWorkbook.Sheets(«справочник») On Error Resume Next ‘определяем в выделенном диапазоне ячейки только внутри рабочего диапазона Set rr = Intersect(Selection, ActiveSheet.UsedRange) ‘если лист пустой — завершаем код If rr Is Nothing Then Exit Sub On Error GoTo 0 ‘отключаем обновление экрана для быстродействия Application.ScreenUpdating = False ‘цикл по выделенным ячейкам For Each rc In rr.Cells With rc If Len(.Value) Then ‘если в ячейке есть значение(не пустая) ‘ищем значение ячейки на листе «справочник» Set rf = wsDic.Columns(1).Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole) ‘если соответствие найдено If Not rf Is Nothing Then On Error Resume Next ‘если вдруг есть смешанное форматирование текста ‘запоминаем форматирование ячейки до создания гиперссылки With .Font aParams(1) = .Color aParams(2) = .Bold aParams(3) = .FontStyle aParams(4) = .Italic aParams(5) = .Name aParams(6) = .Size aParams(7) = .Strikethrough aParams(8) = .Subscript aParams(9) = .Superscript aParams(10) = .ThemeFont aParams(11) = .TintAndShade aParams(12) = .Underline End With ‘добавляем гиперссылку с подсказкой из ячейки второго столбца ‘листа «справочник» .Hyperlinks.Add rc, «», «», (rf.Offset(, 1).Value), (.Value) ‘отменяем стандартное форматирование ячейки после создания гиперссылок With .Font .Color = aParams(1) .Bold = aParams(2) .FontStyle = aParams(3) .Italic = aParams(4) .Name = aParams(5) .Size = aParams(6) .Strikethrough = aParams(7) .Subscript = aParams(8) .Superscript = aParams(9) .ThemeFont = aParams(10) .TintAndShade = aParams(11) .Underline = aParams(12) End With On Error GoTo 0 End If End If End With Next Application.ScreenUpdating = True End Sub

Поиск по меткам

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

Всплывающие подсказки в excel

Извратил Вашу идею. Эстетики нуль, но работает. Спасибо)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set WorkRange = Intersect(Target, Range(«C2:C21»))
If Not WorkRange Is Nothing Then
For Each WorkCell In WorkRange
With WorkCell.Validation

‘DoEvents
Set Comment = Sheet1.Columns(2).Find(what:=Cells(Target.Row, 2))
If Not Comment Is Nothing Then
.Modify AlertStyle:=xlValidAlertInformation, Operator _
:=xlBetween

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = «УПС»
.InputMessage = Comment.Offset(, 1)
.ShowInput = True
.ShowError = False
End If
End With
Next
DoEvents
End If

If Not WorkRange Is Nothing Then
For Each WorkCell In WorkRange
With WorkCell.Validation

‘DoEvents
Set Comment = Sheet1.Columns(2).Find(what:=Cells(Target.Row, 2))
If Comment Is Nothing Then
.Modify AlertStyle:=xlValidAlertInformation, Operator _
:=xlBetween

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = «»
.InputMessage = «»
.ShowInput = False
.ShowError = False
End If
End With
Next
DoEvents
End If

Могу не смотреть?:-)
Если во всех ячейках была проверка данных и надо только подправить коммент, то

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(«C2:H7»)) Is Nothing And Target.Count = 1 Then
With Target.Validation
‘.Delete
‘DoEvents
Set Comment = Sheet1.Columns(2).Find(what:=Cells(Target.Row, 2))
If Not Comment Is Nothing Then
‘.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
‘:=xlBetween

‘.IgnoreBlank = True
‘.InCellDropdown = True
‘.InputTitle = «»
.InputMessage = Comment.Offset(, 1)
‘.ShowInput = True
‘.ShowError = False
End If
End With
End If
End Sub

Почему же Обернул условие и добавил вставку пустого примечания при удалении человека из комментариев. Вроде работает)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(«C2:h31»)) Is Nothing And Target.Count = 1 Then
With Target.Validation
‘.Delete
‘DoEvents
Set Comment = Sheet1.Columns(2).Find(what:=Cells(Target.Row, 2))
If Comment Is Nothing Then
.InputTitle = «»
.InputMessage = «»
.ShowInput = True
Else
‘.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
‘:=xlBetween

.IgnoreBlank = True
‘.InCellDropdown = True
.InputTitle = «Описание»
.InputMessage = Comment.Offset(, 1)
.ShowInput = True
‘.ShowError = False
End If
End With
End If
End Sub

А не подскажите, какой командой регламентируется положение всплывающей подсказки? А то подсказка при случайном перемещении мышью остается на заданной позиции до перезапуска файла
и есть ли операторы способные изменить её визуально (увеличить/изменить шрифт, цвет и т.д.)?

Вставляем функцию в Excel — Microsoft Excel для начинающих

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

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

СУММ – эта функция суммирует значения всех аргументов.
СРЗНАЧ – определяет среднее арифметическое величин, содержащихся в аргументах. Функция вычисляет сумму значений ячеек, а затем делит результат на их количество.
СЧЁТ – подсчитывает количество чисел в списке аргументов. Функция полезна для быстрого подсчета числа элементов в диапазоне.
МАКС – определяет максимальное значение из списка аргументов.
МИН – определяет минимальное значение из списка аргументов.

Как вставить функцию в Excel

В следующем примере мы создадим простую формулу для расчета средней цены за единицу заказанных товаров, используя функцию СРЗНАЧ.

Выделите ячейку, в которую необходимо вставить формулу. В нашем примере мы выделим ячейку C11.
Введите знак равенства (=) и нужное имя функции. Вы также можете вставить функцию из списка, который появится при вводе ее названия (автозавершение в Excel). В нашем случае мы введем =СРЗНАЧ.
Введите диапазон ячеек в качестве аргумента в круглых скобках. В нашем примере мы введем (C3:C10). Эта формула суммирует значения в диапазоне С3:С10, а затем делит результат на количество ячеек в этом диапазоне, тем самым определяя среднее значение.
Нажмите Enter на клавиатуре. Функция будет вычислена, и Вы увидите результат. В данном примере средняя цена за единицу заказанных товаров составила $15,93.

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

Вставка функции с помощью команды Автосумма

Команда Автосумма позволяет автоматически вставлять наиболее распространенные функции в формулы Excel, включая СУММ, СРЗНАЧ, СЧЁТ, МИН и МАКС. В следующем примере мы создадим формулу для расчета полной стоимости недавно заказанных товаров, используя функцию СУММ.

Выделите ячейку, в которую необходимо вставить формулу. В нашем примере мы выделим ячейку D12.
В группе команд Редактирование на вкладке Главная найдите и нажмите стрелку рядом с командой Автосумма, а затем выберите нужную функцию в раскрывающемся меню. В нашем случае мы выберем Сумма.
Выбранная функция появится в ячейке. Команда Автосумма автоматически определяет диапазон ячеек для аргумента. В данном примере диапазон D3:D11 был выбран автоматически, а значения просуммированы, чтобы вычислить полную стоимость. Вы также можете вручную ввести нужный диапазон.
Нажмите Enter на клавиатуре. Функция будет вычислена, и Вы увидите результат. В нашем примере сумма значений диапазона D3:D11 составляет $606,05.

В Microsoft Excel команду Автосумма можно также найти на вкладке Формулы.

Оцените качество статьи. Нам важно ваше мнение:

Подсказки и проверки ввода данных в MS Excel

Вот и моя демонстрационная excel-таблица. Как её заполнять?

Подсказки в MS Excel

Первый способ вставки подсказки

Самый проcтой способ вставить примечание в лист MS Excel

Примечания вставлено

Удалить примечания можно только из контекстного меню правой кнопки мыши

Второй способ вставки подсказки

Примечания в Excel с помощью проверки данных

Проверка по числу введенных символов

Проверка по числу введенных символов в MS Excel

Первое, что может оказаться полезным, это проверка по длине введенного текста. Выбираем пункт «Длина текста» и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.

Слишком длинный текст! Excel стоит на страже и не пропустит такой очевидной ошибки

Проверка по числу

Проверка по числу в Excel

Фильтрация на ввод чисел работает ничуть не хуже

Выбор из имеющегося списка значений

А теперь, сделаем проверку по выпадающему списку в MS Excel. Данные введем вручную, через точку с запятой.

Выбрать диапазон данных с помощью кнопки «Диапазон данных».
Вписать значения выпадающего списка вручную, через точку с запятой.

…или выберем из диапазона

Выбор из списка на листе MS Excel. Других данных ввести не удастся.

Способы добавления значений в электронную таблицу

Одним из быстрых и простых способов добавления значений в Excel является использование автосуммы. Просто выберите пустую ячейку прямо под столбцом данных. Затем на вкладке Формула щелкните AutoSum > Sum . Excel автоматически определит диапазон для суммирования. (Автосумма также может работать по горизонтали, если вы выберете пустую ячейку справа от ячеек для суммирования.)

Автосумма создает формулу за вас, так что вам не нужно вводить текст.Однако, если вы предпочитаете вводить формулу самостоятельно, см. Функцию СУММ.

Добавить на основе условий

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

Сложить или вычесть даты

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

Сложить или вычесть время

Обзор того, как добавить или вычесть время, см. В разделе «Сложение или вычитание времени». Чтобы узнать о других расчетах времени, см. Функции даты и времени.

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

как определять и использовать в формулах

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

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

Что означает имя в Excel?

В повседневной жизни имена широко используются для обозначения людей, предметов и географических мест. Например, вместо того, чтобы сказать «город, расположенный на широте 40,7128 ° северной широты и 74,0059 ° западной долготы, вы просто скажете« Нью-Йорк ».

Точно так же в Microsoft Excel вы можете дать удобочитаемое имя отдельной ячейке или диапазону ячеек и ссылаться на эти ячейки по имени, а не по ссылке.

Например, чтобы найти общую сумму продаж (B2: B10) для определенного товара (E1), вы можете использовать следующую формулу:

= СУММЕСЛИ (2 доллара США: 10 австралийских долларов, 1 доллар E $, 2 доллара США: 10 млрд долларов США)

Или вы можете дать значимые имена диапазонам и отдельным ячейкам и указать эти имена в формуле:

= СУММЕСЛИ (список_товаров, товар, продажи)

Глядя на приведенный ниже снимок экрана, какую из двух формул вам легче понять?

Типы имен Excel

В Microsoft Excel вы можете создавать и использовать два типа имен:

Определенное имя — имя, которое относится к отдельной ячейке, диапазону ячеек, постоянному значению или формуле.Например, когда вы определяете имя для диапазона ячеек, оно называется именованным диапазоном или определенным диапазоном . Эти имена являются предметом сегодняшнего урока.

Имя таблицы — имя таблицы Excel, которая создается автоматически, когда вы вставляете таблицу в рабочий лист (Ctrl + T). Для получения дополнительных сведений о таблицах Excel см. Раздел Как создать и использовать таблицу в Excel.

Как создать именованный диапазон Excel

В целом, существует 3 способа определить имя в Excel: Поле имени , Кнопка определения имени и Диспетчер имен Excel .

Введите имя в поле «Имя»

Поле имени в Excel — самый быстрый способ создать именованный диапазон:

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

Вуаля, новый именованный диапазон Excel создан!

Создайте имя с помощью параметра «Определить имя»

Другой способ создать именованный диапазон в Excel:

Выберите ячейку (и).
На вкладке Формулы в группе Определить имена нажмите кнопку Определить имя .
В диалоговом окне Новое имя укажите три вещи:
В поле Имя введите имя диапазона.
В раскрывающемся списке Scope задайте область имени ( Workbook по умолчанию).
В поле Относится к отметьте ссылку и при необходимости исправьте ее.
Нажмите ОК , чтобы сохранить изменения и закрыть диалоговое окно.

Примечание. По умолчанию Excel создает имя с абсолютными ссылками . Если вы предпочитаете относительный именованный диапазон, удалите знак $ из ссылки (прежде чем вы это сделаете, убедитесь, что вы полностью понимаете, как относительные имена ведут себя на листах).

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

Создайте именованный диапазон с помощью диспетчера имен Excel

Обычно Диспетчер имен в Excel используется для работы с существующими именами. Однако это также может помочь вам создать новое имя. Вот как:

Перейдите на вкладку Формулы > группу Определенные имена и щелкните Диспетчер имен Или просто нажмите Ctrl + F3 (мой предпочтительный способ).
В верхнем левом углу диалогового окна Name Manager нажмите кнопку New… :
Откроется диалоговое окно New Name , в котором вы настраиваете имя, как показано в предыдущем разделе.

Наконечник. Чтобы быстро проверить вновь созданное имя, выберите его в раскрывающемся списке Name Box . Как только вы отпустите кнопку мыши, диапазон на листе будет выбран.

Как создать имя в Excel для константы

В дополнение к именованным диапазонам Microsoft Excel позволяет определять имя без ссылки на ячейку, которое будет работать как именованная константа .Чтобы создать такое имя, используйте функцию «Определить имя» в Excel или Диспетчер имен, как описано выше.

Например, вы можете присвоить имя USD_EUR (курс конвертации USD — EUR) и присвоить ему фиксированное значение. Для этого введите значение, которому предшествует знак равенства (=), в поле Относится к полю , например = 0,93:

И теперь вы можете использовать это имя в любом месте своих формул для преобразования долларов США в евро:

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

Как определить имя для формулы

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

= COUNTA (Sheet5! $ A: $ A) -1

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

Теперь, когда вы хотите узнать, сколько элементов находится в столбце A на Листе 5, не включая заголовок столбца, просто введите знак равенства, а затем имя вашей формулы в любой ячейке, например: = Items_count

Как назвать столбцы в Excel (имена из выбора)

Если ваши данные организованы в табличной форме, вы можете быстро создать имена для каждого столбца и / или строки на основе их меток:

Выберите всю таблицу, включая заголовки столбцов и строк.
Перейдите на вкладку Formulas > группу Define Names и нажмите кнопку Create from Selection . Или нажмите сочетание клавиш Ctrl + Shift + F3.
В любом случае откроется диалоговое окно Create Names from Selection . Вы выбираете столбец или строку с заголовками или и то, и другое, и нажимаете OK .

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

В результате Excel создаст 7 именованных диапазонов, автоматически выбирая имена из заголовков:

Яблоки , Бананы , Лимоны и Апельсины для рядков и
янв , фев и мар для столбцов.

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

Динамический именованный диапазон Excel

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

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

Подробное руководство по созданию динамического именованного диапазона в Excel можно найти здесь:

Правила именования Excel

При создании имени в Excel следует помнить несколько правил:

Имя Excel не должно превышать 255 символов.
Имена Excel не могут содержать пробелы и большинство знаков пунктуации.
Имя должно начинаться с буквы, подчеркивания (_) или обратной косой черты (\). Если имя начинается с чего-либо еще, Excel выдаст ошибку.
Имена Excel не чувствительны к регистру. Например, «Яблоки», «яблоки» и «ЯБЛОКИ» будут обрабатываться как одно и то же имя.
Вы не можете называть диапазоны, как ссылки на ячейки. То есть диапазону нельзя присвоить имя «A1» или «AA1».
Вы можете использовать одну букву для обозначения диапазона, например «a», «b», «D» и т. Д., За исключением букв «r», «R», «c» и «C» (эти символы используются как ярлыки для выбора строки или столбца для выбранной в данный момент ячейки при вводе их в поле Имя Поле ).

Область имен Excel

С точки зрения имен Excel, область действия — это местоположение или уровень, в котором имя распознается. Это может быть:

Конкретный рабочий лист — локальный уровень рабочего листа
Workbook — глобальная рабочая тетрадь уровня

Имена уровней рабочего листа

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

Чтобы иметь возможность использовать имя на уровне рабочего листа в другом рабочем листе , вы должны префикс имени рабочего листа, за которым следует восклицательный знак (!), Например:

Лист1! Items_list

Чтобы указать имя уровня в другой книге , вы также должны включить имя книги, заключенное в квадратные скобки:

[Sales.xlsx] Sheet1! Items_list

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

‘[Продажи 2017.xlsx] Sheet1 ‘! items_list

Имена уровней книги

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

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

Book1.xlsx! Items_list

Приоритет области действия

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

Если существует несколько диапазонов с одинаковыми именами и разными областями действия, и вы хотите использовать имя уровня книги, добавьте к имени префикс имени книги, как если бы вы ссылались на имя в другой книге, например: Book1.xlsx! Data . Таким образом, конфликт имен можно переопределить для всех листов, кроме первого листа, который всегда использует имя уровня локального листа.

Excel Name Manager — быстрый способ редактирования, удаления и фильтрации имен

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

Есть два способа попасть в диспетчер имен в Excel:

На вкладке Формулы в группе Определить имена щелкните Диспетчер имен
Нажмите сочетание клавиш Ctrl + F3.

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

Как редактировать именованный диапазон в Excel

Чтобы изменить существующее имя Excel, откройте Диспетчер имен , выберите имя и нажмите кнопку Изменить… . Откроется диалоговое окно Edit Name , в котором вы можете изменить имя и ссылку. Объем имени не может быть изменен.

Наконечник. Чтобы отредактировать ссылку на имя , вам не нужно открывать диалоговое окно Редактировать имя . Просто выберите интересующее имя в Excel Name Manager и введите новую ссылку прямо в поле Refers to или нажмите кнопку справа и выберите нужный диапазон на листе. После нажатия кнопки Закрыть Excel спросит, хотите ли вы сохранить изменения, и вы нажмете Да .

Как фильтровать имена в Excel

Если у вас много имен в определенной книге, нажмите кнопку Фильтр в правом верхнем углу окна Диспетчера имен Excel, чтобы просмотреть только имена, актуальные в данный момент.Доступны следующие фильтры:

Имена на листе или в книге
Имена с ошибками или без них
Определенные имена или имена таблиц

Как удалить именованный диапазон в Excel

Чтобы удалить именованный диапазон , выберите его в Диспетчере имен и нажмите кнопку Удалить вверху.

Чтобы удалить несколько имен , щелкните первое имя, затем нажмите клавишу Ctrl и удерживайте ее, щелкая другие имена, которые вы хотите удалить.Затем нажмите кнопку Удалить , и все выбранные имена будут удалены за один раз.

Чтобы удалить все определенные имена в книге, выберите первое имя в списке, нажмите и удерживайте клавишу Shift, а затем щелкните фамилию. Отпустите клавишу Shift и нажмите Удалить .

Как удалить определенные имена с ошибками

Если у вас есть несколько недопустимых имен с ошибками ссылок, нажмите кнопку Фильтр > Имена с ошибками , чтобы отфильтровать их:

После этого выберите все отфильтрованные имена, как описано выше (с помощью клавиши Shift), и нажмите кнопку Удалить .

Примечание. Если какое-либо из ваших имен Excel используется в формулах, обязательно обновите формулы перед удалением имен, иначе ваши формулы вернут #NAME? ошибки.

5 основных преимуществ использования имен в Excel

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

1. Названия Excel упрощают составление и чтение формул

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

2. Имена Excel позволяют создавать расширяемые формулы

Используя динамические именованные диапазоны, вы можете создать «динамическую» формулу, которая автоматически включает новые данные в вычисления, без необходимости обновлять каждую ссылку вручную.

3. Имена Excel упрощают повторное использование формул

Имена

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

Наконечник. Чтобы форма Excel не создавала новые имена на лету, скопируйте формулу как текст в строке формул вместо копирования ячейки формулы.

4.Именованные диапазоны упрощают навигацию

Чтобы быстро перейти к определенному именованному диапазону, просто щелкните его имя в поле «Имя». Если именованный диапазон находится на другом листе, Excel автоматически переведет вас на этот лист.

Примечание. Динамические именованные диапазоны не отображаются в поле Имя в Excel. Чтобы увидеть динамические диапазоны , откройте диспетчер имен Excel (Ctrl + F3), в котором отображаются полные сведения обо всех именах в книге, включая их область действия и ссылки.

5. Именованные диапазоны позволяют создавать динамические раскрывающиеся списки

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

Именованный диапазон в Excel — советы и рекомендации

Теперь, когда вы знаете основы создания и использования имен в Excel, позвольте мне поделиться еще несколькими советами, которые могут оказаться полезными в вашей работе.

Как получить список всех имен в книге

Чтобы получить более понятный список всех имен в текущей книге, выполните следующие действия:

Выберите самую верхнюю ячейку диапазона, в котором должны отображаться имена.
Перейдите на вкладку Formulas > группу Define Names , щелкните Use in Formulas , а затем щелкните Paste Names… Или просто нажмите клавишу F3.
В диалоговом окне Вставить имена щелкните Вставить список .

Это вставит все имена Excel вместе со ссылками на них в текущий рабочий лист, начиная с выбранной ячейки.

Абсолютные имена Excel и относительные имена Excel

По умолчанию имена Excel ведут себя как абсолютные ссылки — привязаны к определенным ячейкам. Однако можно создать именованный диапазон относительно позиции активной ячейки во время определения имени. Относительные имена ведут себя как относительные ссылки — меняются при перемещении или копировании формулы в другую ячейку.

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

Выберите ячейку B1.
Нажмите Ctrl + F3, чтобы открыть Диспетчер имен Excel, и щелкните Новый…
В поле Имя введите желаемое имя, например item_left .
В поле Ссылается на поле , введите = A1 .
Щелкните ОК .

Теперь давайте посмотрим, что произойдет, когда мы используем имя item_left в формуле, например:

= СУММЕСЛИ (список_элементов, левый_элемент, продажи)

Где items_list относится к $ A $ 2: $ A $ 10 и продаж относится к $ B $ 2: $ B $ 10 в таблице ниже.

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

Как применять имена Excel к существующим формулам

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

Выберите одну или несколько ячеек формулы, которые вы хотите обновить.
Перейдите на вкладку Формулы > группу Определить имена и нажмите Определить имя > Применить имена…
В диалоговом окне Apply Names щелкните по именам, которые вы хотите применить, а затем щелкните OK . Если Excel сможет сопоставить любое из существующих имен со ссылками, используемыми в ваших формулах, имена будут выбраны для вас автоматически:

Дополнительно доступны еще две опции (выбраны по умолчанию):

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

Сочетания клавиш в Excel

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

Ошибки имени Excel (# ССЫЛКА и # ИМЯ)

По умолчанию Microsoft Excel делает все возможное, чтобы ваши определенные имена были согласованными и действительными, автоматически настраивая ссылки на диапазоны при вставке или удалении ячеек в существующем именованном диапазоне.Например, если вы создали именованный диапазон для ячеек A1: A10, а затем вставили новую строку в любое место между строками 1 и 10, ссылка на диапазон изменится на A1: A11. Точно так же, если вы удалите любые ячейки между A1 и A10, ваш именованный диапазон будет соответственно сокращаться.

Однако, если вы удаляете все ячейки , которые составляют именованный диапазон Excel, имя становится недействительным и отображает #REF! ошибка в диспетчере имен . Та же ошибка появится в формуле со ссылкой на это имя:

Если формула относится к несуществующему имени (ошибочно набрана или удалена), #NAME? появится ошибка .В любом случае откройте диспетчер имен Excel и проверьте правильность заданных вами имен (самый быстрый способ — отфильтровать имена с ошибками).

Вот как вы создаете и используете имена в Excel. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

Пример надстройки функции Excel в C

Введение

Это образец XLL (DLL для Microsoft Excel), который добавляет возможность линейной интерполяции в список функций Excel. Функциональность XLL в Excel чрезвычайно быстра и позволяет расширять базовые функции любым желаемым образом. Этот XLL был написан для решения проблемы, в которой я выполнял многие тысячи интерполяций через VBA в электронной таблице, и на расчет уходило около 20 минут (по какой-то причине линейная интерполяция отсутствует во встроенном наборе функций Excel) .Включение этой функции интерполяции для замены кода VBA сократило время вычисления до нескольких секунд. Обратите внимание, что скорость — не единственное преимущество; функции, установленные в XLL, могут быть постоянно включены в Excel, поэтому они всегда доступны и легко доступны.

Фон

Надстройки

Excel (особенно на C) в некоторой степени загадочны, и их трудно найти в документации. Первоначально он был задокументирован (вроде) в наборе разработчика Microsoft Excel (доступен здесь).Однако при первоначальной разработке этой надстройки я обнаружил, что в документации неясно многие важные детали. Я так много получил от The Code Project — я подумал, что это будет хорошим способом для меня внести небольшой вклад.

Эта конкретная надстройка выполняет линейную интерполяцию набора данных в Excel. Для данной кривой, которая определяется таблицей пар дискретных (X, Y) значений, интерполяция — это процесс оценки зависимого (Y) значения вдоль кривой, которое не обязательно находится в определенных точках.

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

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

Синтаксис функции интерполяции следующий:

Интерп (X, Xarray, Yarray, extrapFlag)
где:
X : аргумент X _и.
Xarray : список значений X, определяющих кривую.
Yarray : список значений Y, определяющих кривую.
extrapFlag : логическое значение; true , чтобы разрешить экстраполяцию, false , чтобы запретить экстраполяцию.

Используя код

XLL — это, по сути, функция, которая просто ожидает вызова из Excel при каждом пересчете электронной таблицы. XLL добавит новые функции в список функций Excel.2,7

И функцию можно использовать в электронной таблице сколь угодно часто. Данные передаются в Excel и из него с использованием структуры XLOPER struct , которая на самом деле просто инкапсулирует вариантную структуру с объявлением C union . Базовая структура XLL предоставляется Microsoft здесь. Этот фреймворк включает все необходимые включаемые файлы, библиотеки и некоторый базовый код для создания XLL (однако я не нашел его очень простым в использовании).

Объявление XLOPER выглядит так:

 typedef struct xloper
{
   союз
   {
      двойное число;
      ЛПСТР ул.
      WORD bool;
      WORD err;
      короткий int w;
      структура
      {
         Количество слов;
         XLREF ref;
      } sref;
      структура
      {
         XLMREF far * lpmref;
         DWORD idSheet;
      } mref;
      структура
      {
         struct xloper far * lparray;
         WORD строки;
         Столбцы WORD;
      } массив;
      структура
      {
         союз
         {
            короткий уровень int;
            короткий int tbctrl;
            DWORD idSheet;
         } valflow;
         WORD rw;
         BYTE col;
         BYTE xlflow;
      } течь;
      структура
      {
         союз
         {
            BYTE far * lpbData;
            HANDLE hdata;
         } ч;
         long cbData;
      } большое количество данных;
   } val;
   WORD xltype;
} XLOPER

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

Generic.c

Код XLL сначала инициализируется через точку входа DllMain () . Эта функция вызывается Windows только один раз при первой загрузке библиотеки DLL. В нашем случае эта процедура просто инициализирует некоторые строки Паскаля (которые требуют подсчета байтов в первом байте). Мы отвечаем только на код причины DLL_PROCESS_ATTACH . После инициализации строк код просто ожидает вызова из Excel.

Код «подключается» к Excel с помощью процесса регистрации.Excel вызовет функцию xlAutoOpen () при добавлении XLL в Excel (через диспетчер надстроек, команду REGISTER, VBA и т. Д.). Затем процедура xlAutoOpen () «регистрирует» каждую функцию в Excel. Excel предоставляет Excel4 () и Excel4v () (версия varargs), чтобы обеспечить доступ к широкому спектру функций. Первым аргументом этой процедуры является код функции, который определяет поведение (и остальные аргументы). xlAutoOpen () сначала вызывает Excel4 (xlGetName) , чтобы получить имя XLL.Это имя затем используется в качестве аргумента кода функции xlfRegister , который регистрирует каждую из наших функций. Параметр xlfRegister передает информацию об имени каждой функции, типах переданных параметров, типе возвращаемого значения, идентификаторе справки, описании функции и описании каждого параметра. Эта информация используется Excel для помощи во всплывающих подсказках, когда функция вводится в электронную таблицу, и полезного текста, когда используется мастер вставки функций. Взгляните на файл interface.h ‘, чтобы получить список информации о функциях:

 LPSTR functionParms [kFunctionCount] [kMaxFuncParms] =
{
// название функции, типы аргументов, имя функции, имена аргументов,
// тип (1 = func, 2 = cmd),
// имя группы (мастер функций), горячая клавиша, идентификатор справки,
//        описание функции,
// (повторяем) описание каждого аргумента

    {"Интерп", "РРРРА", "Интерп",
        «x, xArray, yArray, extrapFlag», «1»,
        "Надстройка интерполяции", "",
        "интерп.hlp! 300 ",
        «Выполняет линейную интерполяцию. Это общая версия»
                 "который может обрабатывать отдельные значения или массивы.",
        «Значения X для интерполяции. Может быть одно значение или»
                 "массив (каждое значение интерполируется индивидуально)",
        «Таблица значений, определяющих X. Должна быть отсортирована (увеличивается»
                 "или убывающий).",
        «Таблица значений, определяющих Y (для каждого значения таблицы X).»,
        "Если ИСТИНА, экстраполяция за пределы таблицы разрешена.Если опущено "
                 "или FALSE, результат обрезается по границам таблицы". },

    {"InterpX", "RBRRA", "InterpX",
        «x, xArray, yArray, extrapFlag», «1»,
        "Надстройка интерполяции", "",
        "interp.hlp! 310",
        «Выполняет линейную интерполяцию. Эта версия интерполирует»
                 "только одно значение X за раз.",
        «Значение X для интерполяции. Допускается только одно значение»
                 «чтобы воспользоваться преимуществом« неявного пересечения »Excel.",
        «Таблица значений, определяющих X. Должна быть отсортирована (увеличивается»
                 "или убывающий).",
        «Таблица значений, определяющих Y (для каждого значения таблицы X).»,
        «Если ИСТИНА, экстраполяция за пределы таблицы разрешена. Если опущено»
                 "или FALSE, результат обрезается по границам таблицы". },
};

В Excel передается следующая информация:

Название XLL
Имя функции, как оно объявлено в коде C (т.е.е., «Интерп»)
Типы аргументов, начиная с самого возвращаемого значения функции. (R = XLOPER (вариант), A = логическое, B = double, подробнее см. Комплект разработчика.)
Имя функции, которое должно отображаться в списке мастера функций.
Строка, в которой перечислены имена аргументов функции (только для информации пользователя).
Тип функции; 1 = функция (возвращает значение), 2 = команда.
Название категории, к которой принадлежит эта функция (например, «Финансы», «Математика и триггеры» и т. Д.). Вы можете придумать новое имя или использовать существующую категорию Excel.
Назначение горячих клавиш для команд.
Имя файла и идентификатор справки для этой функции.
Описание самой функции.
Описание каждого аргумента функции.

Обратите внимание, что эта надстройка фактически содержит две версии функции Interp () . Первый, Interp () , представляет собой общую версию, которая может обрабатывать массивы Excel для входного аргумента «X».То есть он может получать несколько значений X и возвращать несколько значений Y (по одному для каждого X). Это обеспечивает максимальную гибкость. Однако в Excel есть функция под названием «неявное пересечение», которая позволяет использовать имена диапазонов в формулах в упрощенном виде. Например, вы можете объявить диапазон электронной таблицы с именем «foo» и диапазоном C1: C10. Если затем вы используете имя «foo» в формуле электронной таблицы, которая ожидает одно значение, Excel автоматически будет использовать значение, соответствующее строке или столбцу, которые соответствуют расположению формулы.То есть, если формула находится в A3, то использование имени «foo» даст значение из ячейки C3 (та же строка). Если вы пытаетесь использовать неявное пересечение с общей процедурой Interp () , она попытается вычислить массив результатов, поскольку весь массив будет передан в Interp () для аргумента X. Поэтому предоставляется вторая версия под названием InterpX () , которая принимает только одно значение для аргумента X и, следовательно, работает с неявным пересечением.

Другая функция, на которую следует обратить внимание в generic.c , — это функция xlAutoAdd () , которая вызывается только один раз во время добавления надстройки через диспетчер надстроек. Это дает вам возможность открыть диалоговое окно с указанием авторских прав и т. Д.

Interp2.c

Файл Interp2.c содержит сам код функции. Код хорошо прокомментирован, но вот подробности. При входе в Interp () код сначала проверяет, что тип аргумента Xarray является приемлемым.Поскольку он принимает тип XLOPER (вариант), необходимо убедиться, что тип, введенный пользователем для функции, является разумным. Типом должен быть ссылочный массив ( xlTypeRef или xlTypeSRef ) или мульти-массив ( xlTypeMulti ). Затем вызывается Excel4 (xlCoerce) для «приведения» данных к типу xlTypeMulti , так что последующие вычисления должны иметь дело только с одним типом. Затем выполняется проверка, не были ли какие-либо данные Xarray еще рассчитаны с помощью электронной таблицы.Если да, просто вернитесь и подождите позже. Эта процедура проверки данных повторяется для данных Yarray . Затем выполняется проверка, чтобы убедиться, что данные X монотонно увеличиваются или уменьшаются (отсортированы), и что все данные Xarray и Yarray являются числовыми значениями. Последняя проверка — это аргумент X, который аналогичен аргументам Xarray и Yarray .

После проверки всех входных аргументов выполняются вычисления интерполяции.Это просто процесс определения, какие два значения в аргументе Xarray являются границами вокруг аргумента X (для интерполяции). Для экстраполяции код определяет, является ли аргумент X меньше первого значения Xarray или больше последнего. Расчет интерполяции прост:

 результат = (X - xlo) / (xhi - xlo) * (yhi - ylo) + ylo;

//где:
// X = X аргумент
// xlo, xhi = ограничивающие значения X
// ylo, yhi = значения Y, соответствующие ограничивающим значениям X

Еще одно интересное замечание — вам может потребоваться выделить память для возврата значений в Excel.Затем это нужно будет освободить с помощью функции xlAutoFree () . Excel вызовет эту функцию, если вы вернете структуру XLOPER struct с установленным битом xlbitDLLFree . Кроме того, иногда Excel необходимо освободить память, которую он выделяет при передаче вам параметров. Вы можете освободить эту память с помощью вызова функции Excel4 (xlFree) .

Установка

Эта надстройка XLL включает файл справки и файл XLL. Чтобы добавить его в Excel, введите interp32.xll в папку Program Files / Microsoft Office / Office10 / Library . Поместите файл справки interp.hlp в папку Program Files / Microsoft Office / Office10 . Это сделает справку доступной с помощью кнопки «Справка» в мастере функций. Чтобы сделать надстройку доступной в Excel, установите флажок «Надстройка интерполяции» в меню «Инструменты / Надстройки». Это нужно сделать только один раз, и добавленные функции будут постоянно доступны в Excel (если они не будут отключены позже).

Обновление

Этот код изначально был написан для Visual C ++ 6.0. Часть процесса инициализации требует добавления счетчика байтов в начало некоторых статических строк, что разрешено в Visual C ++ 6.0, но вызывает исключение в более поздних версиях. Эта статья была обновлена, чтобы исправить эту проблему (плюс две другие незначительные проблемы). Теперь файл решения настроен для Visual Studio 2002 .NET.

Апрель 2024
Пн	Вт	Ср	Чт	Пт	Сб	Вс
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30