Архив

Публикации с меткой ‘Формулы’

Как оставить в ячейке только цифры или только текст?

 

Вот бывает так: есть у Вас в ячейке некий текст. Допустим «Было доставлено кусков мыла 763шт.». Вам нужно из этого только 763 — чтобы можно было провести с этим некие математические действия. Если это только одна ячейка — проблем тут нет, а если таких ячеек пару тысяч? И к тому же все разные?

  • Было доставлено кусков мыла 763шт.
  • Всего пришло 34
  • Тюбики — 54 доставлено
  • и т.д.

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

Function Extract_Number_from_Text(sWord As String, Optional Metod As Integer)
'sWord = ссылка на ячейку или непосредственно текст
'Metod = 0 – числа
'Metod = 1 – текст
    Dim sSymbol As String, sInsertWord As String
    Dim i As Integer
 
    If sWord = "" Then Extract_Number_from_Text = "Нет данных!": Exit Function
    sInsertWord = ""
    sSymbol = ""
    For i = 1 To Len(sWord)
        sSymbol = Mid(sWord, i, 1)
        If Metod = 1 Then
            If Not LCase(sSymbol) Like "*[0-9]*" Then
                If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then
                    If Mid(sWord, i - 1, 1) Like "*[0-9]*" And Mid(sWord, i + 1, 1) Like "*[0-9]*" Then
                        sSymbol = ""
                    End If
                End If
                sInsertWord = sInsertWord & sSymbol
            End If
        Else
            If LCase(sSymbol) Like "*[0-9.,;:-]*" Then
                If LCase(sSymbol) Like "*[.,]*" And i > 1 Then
                    If Not Mid(sWord, i - 1, 1) Like "*[0-9]*" Or Not Mid(sWord, i + 1, 1) Like "*[0-9]*" Then
                        sSymbol = ""
                    End If
                End If
                sInsertWord = sInsertWord & sSymbol
            End If
        End If
    Next i
    Extract_Number_from_Text = sInsertWord
End Function
Function Extract_Number_from_Text(sWord As String, Optional Metod As Integer)
'sWord = ссылка на ячейку или непосредственно текст
'Metod = 0 – числа
'Metod = 1 – текст
    Dim sSymbol As String, sInsertWord As String
    Dim i As Integer
 
    If sWord = "" Then Extract_Number_from_Text = "Нет данных!": Exit Function
    sInsertWord = ""
    sSymbol = ""
    For i = 1 To Len(sWord)
        sSymbol = Mid(sWord, i, 1)
        If Metod = 1 Then
            If Not LCase(sSymbol) Like "*[0-9]*" Then
                If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then
                    If Mid(sWord, i - 1, 1) Like "*[0-9]*" And Mid(sWord, i + 1, 1) Like "*[0-9]*" Then
                        sSymbol = ""
                    End If
                End If
                sInsertWord = sInsertWord & sSymbol
            End If
        Else
            If LCase(sSymbol) Like "*[0-9.,;:-]*" Then
                If LCase(sSymbol) Like "*[.,]*" And i > 1 Then
                    If Not Mid(sWord, i - 1, 1) Like "*[0-9]*" Or Not Mid(sWord, i + 1, 1) Like "*[0-9]*" Then
                        sSymbol = ""
                    End If
                End If
                sInsertWord = sInsertWord & sSymbol
            End If
        End If
    Next i
    Extract_Number_from_Text = sInsertWord
End Function

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

=Extract_Number_from_Text(A1; 0) или =Extract_Number_from_Text(A1) — если надо оставить только числа

=Extract_Number_from_Text(A1; 1) — если надо оставить только текст

Подробнее про создание пользовательских функции и их применении можно почитать здесь


Помимо функции решил выложить и вариант с использованием UserForm.

рис.1

Выбрать ячейку или диапазон с текстом(Лист1!$A$2:$A$10) - здесь указывается диапазон с исходными значениями, который нужно преобразовать.

Выберите ячейку для вывода данных(Лист1!$A$2) — указывается одна ячейка, с которой начать вывод преобразованных значений. В качестве этой ячейки можно выбрать первую ячейку дипазона с текстом(исходного) если Вам необходимо произвести изменения сразу в этих же ячейках(как на рис.1). Осторожнее с таким указанием, т.к. результат работы кода может быть не совсем таким, какой Вы ожидали. Вернуть прежние данные уже не получится — если только не закрыть файл без сохранения изменений.

Оставить только цифры, Оставить только текст- думаю не надо пояснять. Здесь выбираем, что оставить в качестве результата.

Скачать пример »

  Tips_Macro_Number_From_Text.xls (80,0 KiB, 3 071 скачиваний)

Также см.:
?Извлечение числа из текста
?Что такое функция пользователя(UDF)?

Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли

 

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

Function СцепитьЕсли(ByRef Диапазон As Range, ByVal Критерий As String, ByRef Диапазон_сцепления As Range, Optional Разделитель As String = " ", Optional БезПовторов As Boolean = False) As String
    Dim li As Long, sStr As String, avItem, avDateArr(), avRezArr(), lUBnd As Long
    If Диапазон.Count > 1 Then
        avDateArr = Intersect(Диапазон, Диапазон.Parent.UsedRange).Value
        avRezArr = Intersect(Диапазон_сцепления, Диапазон_сцепления.Parent.UsedRange).Value
        If Диапазон.Rows.Count = 1 Then
            avDateArr = Application.Transpose(avDateArr)
            avRezArr = Application.Transpose(avRezArr)
        End If
    Else
        ReDim avDateArr(1, 1):ReDim avRezArr(1, 1)
        avDateArr(1, 1) = Диапазон.Value
        avRezArr(1, 1) = Диапазон_сцепления.Value
    End If
    lUBnd = UBound(avDateArr, 1)
    'Опрееделяем вхождение операторов сравнения в Критерий
    Dim objRegExp As Object, objMatches As Object
    Set objRegExp = CreateObject("VBScript.RegExp")
    objRegExp.Global = False: objRegExp.Pattern = "=|<>|=>|>=|<=|=<|>|<"
    Set objMatches = objRegExp.Execute(Критерий)     'Если есть вхождения
    If objMatches.Count > 0 Then
        Dim sStrMatch As String
        sStrMatch = objMatches.Item(0)
        Критерий = Replace(Replace(Критерий, sStrMatch, "", 1, 1), Chr(34), "", 1, 2)
        Select Case sStrMatch
        Case "="
            For li = 1 To lUBnd
                If avDateArr(li, 1) = Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case "<>"
            For li = 1 To lUBnd
                If avDateArr(li, 1) <> Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case ">=", "=>"
            For li = 1 To lUBnd
                If avDateArr(li, 1) >= Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case "<=", "=<"
            For li = 1 To lUBnd
                If avDateArr(li, 1) <= Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case ">"
            For li = 1 To lUBnd
                If avDateArr(li, 1) > Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case "<"
            For li = 1 To lUBnd
                If avDateArr(li, 1) < Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        End Select
    Else    'Если нет вхождения
        For li = 1 To lUBnd
            If avDateArr(li, 1) Like Критерий Then
                If Trim(avRezArr(li, 1)) <> "" Then _
                   sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
            End If
        Next li
    End If
    If БезПовторов Then
        Dim oDict As Object, sTmpStr
        Set oDict = CreateObject("Scripting.Dictionary")
        sTmpStr = Split(sStr, Разделитель)
        On Error Resume Next
        For li = LBound(sTmpStr) To UBound(sTmpStr)
            oDict.Add sTmpStr(li), sTmpStr(li)
        Next li
        sStr = ""
        sTmpStr = oDict.keys
        For li = LBound(sTmpStr) To UBound(sTmpStr)
            sStr = sStr & IIf(sStr <> "", Разделитель, "") & sTmpStr(li)
        Next li
    End If
    СцепитьЕсли = sStr
End Function
Function СцепитьЕсли(ByRef Диапазон As Range, ByVal Критерий As String, ByRef Диапазон_сцепления As Range, Optional Разделитель As String = " ", Optional БезПовторов As Boolean = False) As String
    Dim li As Long, sStr As String, avItem, avDateArr(), avRezArr(), lUBnd As Long
    If Диапазон.Count > 1 Then
        avDateArr = Intersect(Диапазон, Диапазон.Parent.UsedRange).Value
        avRezArr = Intersect(Диапазон_сцепления, Диапазон_сцепления.Parent.UsedRange).Value
        If Диапазон.Rows.Count = 1 Then
            avDateArr = Application.Transpose(avDateArr)
            avRezArr = Application.Transpose(avRezArr)
        End If
    Else
        ReDim avDateArr(1, 1):ReDim avRezArr(1, 1)
        avDateArr(1, 1) = Диапазон.Value
        avRezArr(1, 1) = Диапазон_сцепления.Value
    End If
    lUBnd = UBound(avDateArr, 1)
    'Опрееделяем вхождение операторов сравнения в Критерий
    Dim objRegExp As Object, objMatches As Object
    Set objRegExp = CreateObject("VBScript.RegExp")
    objRegExp.Global = False: objRegExp.Pattern = "=|<>|=>|>=|<=|=<|>|<"
    Set objMatches = objRegExp.Execute(Критерий)     'Если есть вхождения
    If objMatches.Count > 0 Then
        Dim sStrMatch As String
        sStrMatch = objMatches.Item(0)
        Критерий = Replace(Replace(Критерий, sStrMatch, "", 1, 1), Chr(34), "", 1, 2)
        Select Case sStrMatch
        Case "="
            For li = 1 To lUBnd
                If avDateArr(li, 1) = Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case "<>"
            For li = 1 To lUBnd
                If avDateArr(li, 1) <> Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case ">=", "=>"
            For li = 1 To lUBnd
                If avDateArr(li, 1) >= Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case "<=", "=<"
            For li = 1 To lUBnd
                If avDateArr(li, 1) <= Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case ">"
            For li = 1 To lUBnd
                If avDateArr(li, 1) > Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        Case "<"
            For li = 1 To lUBnd
                If avDateArr(li, 1) < Критерий Then
                    If Trim(avRezArr(li, 1)) <> "" Then _
                       sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
                End If
            Next li
        End Select
    Else    'Если нет вхождения
        For li = 1 To lUBnd
            If avDateArr(li, 1) Like Критерий Then
                If Trim(avRezArr(li, 1)) <> "" Then _
                   sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1)
            End If
        Next li
    End If
    If БезПовторов Then
        Dim oDict As Object, sTmpStr
        Set oDict = CreateObject("Scripting.Dictionary")
        sTmpStr = Split(sStr, Разделитель)
        On Error Resume Next
        For li = LBound(sTmpStr) To UBound(sTmpStr)
            oDict.Add sTmpStr(li), sTmpStr(li)
        Next li
        sStr = ""
        sTmpStr = oDict.keys
        For li = LBound(sTmpStr) To UBound(sTmpStr)
            sStr = sStr & IIf(sStr <> "", Разделитель, "") & sTmpStr(li)
        Next li
    End If
    СцепитьЕсли = sStr
End Function

Для применения функции в своем файле достаточно создать стандартный модуль(как это сделать написано здесь) и просто вставить приведенный код. После этого в диспетчере функций появиться новая категория(если до этого её не было)Определенные пользователем. В ней эта функция — СцепитьЕсли.

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

По умолчанию Разделитель слов — пробел, но можно задать любой другой символ/символы.

Диапазон — диапазон с критериями(указывается один столбец)

Критерий — критерий. Собственно то, с чем сравниваем. Может содержать символы подстановки — * и ?. Просматривается Диапазон. Можно указать символы сравнения с самим критерием сравнения(<>"", <23, >0, "<>"&A1 и т.п.)

Диапазон_сцепления — из этого диапазона берется значение для сцепления, если значение в аргументе Диапазон совпадает с аргументом Критерий(указывается один столбец)

БезПовторов — если указать 1 или ИСТИНА, то в резульате получится строка, в которой нет одинковых значений. Если указать 0 или ЛОЖЬ, то будут выведены все значения. По умолчанию значение ЛОЖЬ.
Примечание: для работы функции должны быть разрешены макросы.

Скачать пример »

  Tips_Macro_CoupleIf.xls (49,0 KiB, 3 413 скачиваний)

Также см.:
[[Couple_Cells - быстрое сцепление диапазона ячеек]]
Что такое функция пользователя(UDF)?

Что такое функция пользователя(UDF)?

 

Функция пользователя(UDF) — правильней, конечно, Функция, Определенная Пользователем, т.к. в оригинале она звучит как: User Defined Function.

Такие функции вызываются через диспетчер функций-категория Определенные пользователем.

Так что же это? Это функция, написанная при помощи языка Visual Basic for Application(VBA) и вызываемая как обычная функция с листа. Как это сделать. Естественно, для написания UDF понадобятся хотя бы базовые знания языка VBA. Как писать функции я не буду описывать, ибо это сводится к обучению самому языку программирования. Здесь мы рассмотрим лишь основные моменты и  то, как уже написанные функции применить к своей книге.

Самое главное, что необходимо усвоить — функция пользователя:

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

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

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

  1. в отличие от процедуры(Sub) функция всегда начинается именно со слова Function, а не Sub;
  2. в теле функции всегда должно быть присвоение ей значения, иначе функция не вернет необходимый результат;
  3. функция должна располагаться в стандартном модуле или в модуле книги, если Вы планируете вызывать её непосредственно с листа Excel.
Function MySum(vArg1 As Double, vArg2 As Double)
    MySum = vArg1 + vArg2
End Function
Function MySum(vArg1 As Double, vArg2 As Double)
    MySum = vArg1 + vArg2
End Function

В приведенном выше коде мы упростили стандартную функция СУММ до двух аргументов. Записанная на лист функция будет иметь такой вид: =Mysum(A1;A2). A1 — первый аргумент(vArg1)A2 — второй аргумент(vArg2). Функция вернет #ЗНАЧ!, если в качестве одного из аргументов передано не числовое значение.

Как вставить уже имеющийся код функции к себе в книгу

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

Если Вы используете версию Excel 2007 и выше, то книгу необходимо будет сохранить с поддержкой макросов: Меню-Сохранить как-Книга Excel с поддержкой макросов.

Categories: Tags:

Что такое формула?

 

В первую очередь Excel, конечно же, таблица. Но таблицы можно рисовать и в Word. Главное преимущество Excel — это функции и формулы. Поистине мощный инструмент этого приложения и любой, кто начал пользоваться Excel, рано или поздно начинает использовать формулы для решения своих задач. Здесь я приведу основные понятия. Если Вы знаете что такое функция, где её найти и как записать в ячейку, то этот раздел Вам читать явно не надо.

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

  • Арифметический оператор – предназначен для выполнения арифметических действий и возвращающий числовое значение;
  • Оператор сравнения – предназначен для сравнения данных и возвращающий логическое значение ИСТИНА или ЛОЖЬ(TRUE или FALSE);
  • Текстовый оператор – применяется для объединения данных(в Excel им является амперсанд — &).

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

Вот пара примеров функций:

  • ЕСЛИ(логическое_выражение;значение_если_ИСТИНА;значение_если_ЛОЖЬ)
  • ВПР(Искомое_значение;таблица;номер_столбца;интервальный _просмотр)

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


  • ТДАТА() — возвращает текущее время и дату в формате даты и времени — 01.01.2001 10:00
  • СЕГОДНЯ() — возвращает текущую дату в формате даты - 01.01.2001
  • ИСТИНА() — возвращает логическое значение ИСТИНА
  • ЛОЖЬ() — возвращает логическое значение ЛОЖЬ
  • НД() — возвращает неопределенное значение #Н/Д
  • ПИ() - возвращает округленное до 15 знаков число Пи — 3,14159265358979
  • СЛЧИС() — возвращает равномерно распределенное случайное число большее или равное нулю и меньше единицы — 0,376514074162531

Полный перечень доступных функций можно посмотреть, нажав на значек (диспетчер функций), расположенный чуть левее строки формул или здесь.

Особенность формул — они обновляют свой результат(вычисляются) как только ячейка, задействованная в формуле(влияющая ячейка) поменяет значение. Например, если в ячейке А1 записать такую формулу: =D1, то при изменении в ячейке D1 значения оно так же поменяется и в А1. Вы, наверное, уже встречали выражение «ссылка на ячейку»? Вот приведенный пример как раз именно ссылкой на ячейку и называется. Но следует справедливо заметить, что ссылка на ячейки может быть не только в таком виде, но и в составе более сложных формул и функций. Проще говоря — ссылка на ячейку это указание ячейки или диапазона в другой формуле. При этом, если выделить такую формулу, разные диапазоны и ячейки будут выделены разными цветами как внутри самой формулы, так и на листе. Ниже в статье я пострался имитировать реальную расцветку ссылок на ячейки — так же, как она выглядит в реальных формулах.

Записать формулу в ячейку можно двумя способами:

  • поставить в ячейке знак «=» и вписать имя функции и ссылки на аргументы прямо в ячейке(например: =ЕСЛИ(A1=B1;A1;B1));
  • выделить нужную ячейку->вызвать диспетчер функций, нажав значек , выбрать категорию и затем саму функцию. Появится окно ввода параметров с краткими пояснениями к каждому параметру. Ввести параметры и нажать Ок.

Есть в списке категория — «10 недавно использовавшихся«. Она самая первая в диспетчере и выбирается по умолчанию. Список функций этой категории можно посмотреть и вызвать любую из списка функцию можно так же и не входя в диспетчер. Для этого в нужную ячейку вводим знак «=». В поле адреса ячейки(на рис. там написано — ПРОСМОТР) будет отображена последняя использовавшаяся функция. А если нажать на стрелочку чуть правее имени функции, то откроется выпадающий список с 10 последними использовавшимися функциями. Просто выбираем оттуда нужную и появиться окно ввода параметров функции.

Диспетчер функций

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

Excel так же будет распознавать как формулу данные в ячейке, если они будут начинаться с — или +. Если далее будет идти текст — Excel вернет в ячейку #ИМЯ?. Если числа — Excel постарается совершить математические действия над числами(сложить, вычесть, умножить, разделить и т.д. — в зависимости от того, будут ли соответствующие символы +-*/)

Естественно, результат можно получить, используя только одну функцию, но чаще всего используются именно всякие комбинации из нескольких функций. С помощью формул можно решить многие задачи, не прибегая к помощи Visual Basic for Application(VBA).

Также см.:
Как удалить в ячейке формулу, оставив значения?
Что такое формула массива

Categories: Tags:

Как узнать есть ли формула в ячейке?

 

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

Способ первый — создание именованной формулы(без применения VBA — Visual Basic for Application)

  • Выделяете ячейку B1
  • жметe Ctrl+F3(вызов диспетчера имен) Если по каким-то причинам Ctrl+F3 не работает, то Диспетчер имен можно вызвать через меню:
    Excel 2003:Вставка-Имя-Присвоить.
    Excel 2007-2010:вкладка Формулы-Диспетчер имен.
  • в поле имя пишите — HasFormula(или любое понравившееся Вам название, но не противоречащее правилам создания имен в Excel)
  • в поле Диапазон вписываете такую формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!A1)

Теперь, записав в ячейку В1: =HasFormula Вы узнаете, есть ли в ячейке А1 формула или же там константа.

Так же при помощи функций листа макросов можно получить и сам текст формулы. Для этого достаточно поменять формулу в поле Диапазон на такую:
=ПОЛУЧИТЬ.ФОРМУЛУ(Лист1!A1)

Недостатки данного метода — Вы привязаны к «левостороннему» определению формулы. Т.е. таким методом Вы можете узнать есть ли формула(или какая формула) в ячейке, расположенной слева от ячейки с именованной формулой. Для того, чтобы «узнать формулу справа» именованная формула должна выглядеть так: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!C1);для работы формулы обязательно должны быть разрешены макросы. Чтобы не зависеть от левостороннего или правостороннего определения можно закрепить столбцы(выделяете ссылку на ячейку - А1 и нажимаете F4. Ссылка будет меняться, появятся знаки доллара $. Если знак стоит перед именем столбца - $А1 — то смещение по столбцу происходить не будет. Так же со строками)
Для ПОЛУЧИТЬ.ФОРМУЛУ есть еще недостаток: формула будет отображаться в стиле ссылок R1C1, что не совсем удобно.
.

Для работы такой функции должны быть разрешены макросы.


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

  • Открываете редактор VBA
  • Вставляете стандартный модуль(про модули см.здесь)
  • В модуль копируете следующий код:
    Function IsFormula(ByVal Cell As Range, Optional ShowFormula As Boolean = False)
        'Application.Volatile True
        If ShowFormula Then
            If Cell.HasFormula Then
                IsFormula = "Формула: " & IIf(Cell.HasArray, "{" & Cell.FormulaLocal & "}", Cell.FormulaLocal)
            Else
                IsFormula = "Значение: " & Cell.Value
            End If
        Else
            IsFormula = Cell.HasFormula
        End If
    End Function
    Function IsFormula(ByVal Cell As Range, Optional ShowFormula As Boolean = False)
        'Application.Volatile True
        If ShowFormula Then
            If Cell.HasFormula Then
                IsFormula = "Формула: " & IIf(Cell.HasArray, "{" & Cell.FormulaLocal & "}", Cell.FormulaLocal)
            Else
                IsFormula = "Значение: " & Cell.Value
            End If
        Else
            IsFormula = Cell.HasFormula
        End If
    End Function

Теперь в ячейку записываете =IsFormula(A2).

Данная функция имеет два аргумента:

  • Cell — ссылка на проверяемую ячейку;
  • ShowFormula — необязательный аргумент. Если присвоено значение ЛОЖЬ или 0 или опущен(не указан) — то функция вернет значение ИСТИНА или ЛОЖЬ(в зависимости от наличия или отсутствия формулы в ячейке). Если присвоено значение ИСТИНА или 1, то в функция вернет не просто ИСТИНА или ЛОЖЬ, а еще и текст самой формулы.

Формулу можно так же найти в диспетчере функций в категории Определенные пользователемIsFormula.
Чтобы при изменении формулы в целевой ячейке сразу же изменялось её отображение в ячейке с данной функцией необходимо убрать апостраф(‘) перед Application.Volatile True (в файле-примере это уже сделано).

Недостатки данного метода — для работы функции обязательно должны быть разрешены макросы.

Скачать пример »

  Tips_All_HasFormula.xls (31,0 KiB, 2 412 скачиваний)

Categories: Tags:

Как получить список уникальных(не повторяющихся) значений?

 

Есть у Вас большой список всяких там наименований. А Вам надо составить список поменьше: всех этих же наименований, но чтобы они не повторялись — т.е. удалить из этого списка все дублирующие записи. Другими словами — создать список уникальных элементов. Для этого существует несколько способов: встроенными средствами Excel, встроенными формулами и, наконец, при помощи кода Visual Basic for Application(VBA).


Начнем с самого простого: со встроенных возможностей и команд Excel. В Excel 2007 и 2010 это вообще просто — есть специальная команда, которая так и называется — «Удалить дубликаты«. Расположена она на вкладке «Данные» подраздел «Работа с данными«(рис.1)
рис.1

Как использовать данную команду. Выделяете столбец(или несколько) с теми данными, в которых надо удалить дублирующие записи. Идете на вкладку «Данные» — «Удалить дубликаты«.

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

Появится окно с параметрами удаления дубликатов(рис.2)
рис.2

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


В случае с Excel 2003 все посложнее. Там нет такого инструмента, как »Удалить дубликаты». Но зато есть такой замечательный инструмент, как «Расширенный фильтр«. В 2003 этот инструмент можно найти в Данные-Фильтр-Расширенный фильтр. Прелесть этого метода в том, что при его помощи можно не портить исходные данные, а создать список в другом дипазоне. В 2007-2010 Excel, он тоже есть, но немного запрятан. Расположен на той же вкладке «Данные», подраздел «Сортировка и фильтр» — «Дополнительно». Как сделать: запускаем указанный инструмент — появляется такое вот окошко:

  • Обработка: Выбираем «Скопировать результат в другое место».
  • Исходный диапазон: Выбираем диапазон с данными(в нашем случае это А1:А51).
  • Поместить результат в диапазон: указываем первую ячейку для вывода данных — любую пустую(на картинке — Е2).
  • Ставим галочку Только уникальные записи.
  • Жмем Ок.

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

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

Для этого надо просто в пункте Обработка выбрать «Фильтровать список на месте«.


Способ 2. Формулы.

Этот способ сложнее в понимании для неопытных пользователей, но зато он создает список уникальных значений, не изменяя при этом исходные данные. Ну и он более динамичен: если Вы измените данные в исходной таблице, то изменится и результат. Иногда это бывает полезно. Попытаюсь объяснить на пальцах что и к чему: допустим, список с данными у Вас расположен в столбце А(А1:А51, где А1 — заголовок). Выводить список мы будем в столбец С, начиная с ячейки С2. Формула в C2 будет следующая:

{=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))}

Надо отметить, что эта формула является формулой массива. Об этом могут сказать фигурные скобки, в которые заключена данная формула. А вводится такая формула в ячейку сочетанием клавиш — Ctrl+Shift+Enter. После того, как мы ввели эту формулу в C2 мы её должны скопировать и вставить в несколько строк так, чтобы точно отобразить все уникальные элементы. Как только формула в нижних ячейках вернет #ЧИСЛО! - это значит все элементы отображены и ниже протягивать формулу нет смысла. Чтобы ошибку избежать можно использовать нехитрую проверку:

{=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));"";ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1)))}

Тогда вместо ошибки #ЧИСЛО! у Вас будут пустые ячейки(не совсем пустые, конечно — с формулами :-)).


Ну и способ 3: код VBA.

Исходные данные оставим в том же порядке - список с данными расположен в столбце «А«(А1:А51, где А1 — заголовок). Только выводить список мы будем не в столбец С, а в столбец Е, начиная с ячейки Е2:

Sub Extract_Unique()
    Dim vItem, avArr, li As Long
    ReDim avArr(1 To Rows.Count, 1 To 1)
    With New Collection
        On Error Resume Next
        For Each vItem In Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
            'Cells(Rows.Count, 1).End(xlUp) – определяет последнюю заполненную ячейку в столбце А
            .Add vItem, CStr(vItem)
            If Err = 0 Then
                li = li + 1: avArr(li, 1) = vItem
            Else: Err.Clear
            End If
        Next
    End With
    If li Then [E2].Resize(li).Value = avArr
End Sub
Sub Extract_Unique()
    Dim vItem, avArr, li As Long
    ReDim avArr(1 To Rows.Count, 1 To 1)
    With New Collection
        On Error Resume Next
        For Each vItem In Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
            'Cells(Rows.Count, 1).End(xlUp) – определяет последнюю заполненную ячейку в столбце А
            .Add vItem, CStr(vItem)
            If Err = 0 Then
                li = li + 1: avArr(li, 1) = vItem
            Else: Err.Clear
            End If
        Next
    End With
    If li Then [E2].Resize(li).Value = avArr
End Sub

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

Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value

указать Selection.Value, то Вы получите список уникальных элементов из выделенного на активном листе диапазона. Только тогда неплохо бы и ячейку вывода значений изменить — вместо [E2] поставить ту, в которой данных нет.

Вот, вроде бы и все. В приложенном примере Вы, помимо описанных приемов, найдете еще чуть более сложную вариацию извлечения уникальных элементов, а именно: извлечение уникальных элементов по критерию. Т.е. если в одном стобце у Вас фамилии, а во втором(В) данные, а надо Вам извлечь только уникальные значения столбца В для Иванова. Это реализовано и с помощью формул и с помощью кода VBA. Сами примеры данных расположены на листе «Извлечение по критерию».

Скачать пример »

  Tips_All_ExtractUnique.xls (79,5 KiB, 4 047 скачиваний)

Также см.:
Работа с дубликатами

Categories: Tags:

Текстовые функции

Текстовые функции

  • БАТТЕКСТ() - BAHTTEXT() — Преобразует чисто в текст (бат) на тайском языке
  • СИМВОЛ() — CHAR() — Определяет знак по заданному коду.
  • ПЕЧСИМВ() — CLEAN() — Удаляет все непечатаемые знаки из текста.
  • КОДСИМВ() — CODE() - Определяет числовой код первого знака в текстовой строке.
  • СЦЕПИТЬ() — CONCATENATE() — Объединяет несколько текстовых элементов в один.
  • РУБЛЬ() — DOLLAR() — Преобразует число в текст, используя денежный формат доллара.
  • СОВПАД() — EXACT() — Проверяет идентичность двух текстов.
  • НАЙТИ() — FIND() — Ищет вхождение одного текста в другой (с учетом регистра).
  • ФИКСИРОВАННЫЙ() — FIXED() - Форматирует число и преобразует его в текст с заданным числом десятичных знаков.
  • ЛЕВСИМВ() — LEFT() — Выдает нужное количество самых левых знаков в строке.
  • ДЛСТР() — LEN() — Определяет количество знаков в текстовой строке.
  • СТРОЧН() — LOWER() — Делает все буквы в тексте строчными.
  • ПСТР() — MID() — Выдает определенное число знаков из строки текста, начиная с указанной позиции.
  • ПРОПНАЧ() — PROPER() — Делает прописной первую букву в каждом слове текста.
  • ЗАМЕНИТЬ() — REPLACE() — Заменяет знаки в тексте.
  • ПОВТОР() — REPT() — Повторяет текст заданное число раз.
  • ПРАВСИМВ() — RIGHT() — Выдает самые правые знаки текстовой строки.
  • ПОИСК() — SEARCH() — Ищет вхождение одного текста в другой (без учета регистра).
  • ПОДСТАВИТЬ() — SUBSTITUTE() - Заменяет в текстовой строке старый текст новым.
  • Т() — T() — Преобразует аргумент в текст.
  • ТЕКСТ() — TEXT() — Форматирует число и преобразует его в текст.
  • СЖПРОБЕЛЫ() — TRIM() — Удаляет из текста лишние пробелы.
  • ПРОПИСН() — UPPER() — Делает все буквы в тексте прописными.
  • ЗНАЧЕН() — VALUE() —  Преобразует текстовый аргумент в число.

Статистические функции

Статистические функции

  • СРОТКЛ() — AVEDEV() — Вычисляет среднее абсолютных значений отклонений точек данных от среднего.
  • СРЗНАЧ() — AVERAGE() — Вычисляет среднее арифметическое аргументов.
  • СРЗНАЧА() — AVERAGEA() — Вычисляет среднее арифметическое аргументов, включая числа, текст и логические значения.
  • БЕТАРАСП() — BETADIST() — Определяет интегральную функцию плотности бета-вероятности.
  • БЕТАОБР() — BETAINV() — Определяет обратную функцию к интегральной функции плотности бета-вероятности.
  • БИНОМРАСП() — BINOMDIST() — Вычисляет отдельное значение биномиального распределения.
  • ХИ2РАСП() — CHIDIST() — Вычисляет одностороннюю вероятность распределения хи-квадрат.
  • ХИ2ОБР() — CHIINV() — Вычисляет обратное значение односторонней вероятности распределения хи-квадрат.
  • ХИ2ТЕСТ() — CHITEST() — Определяет тест на независимость.
  • ДОВЕРИТ() — CONFIDENCE() — Определяет доверительный интервал для среднего значения по генеральной совокупности.
  • КОРРЕЛ() — CORREL() — Находит коэффициент корреляции между двумя множествами данных.
  • СЧЁТ() — COUNT() — Подсчитывает количество чисел в списке аргументов.
  • СЧЁТЗ() — COUNTA() — Подсчитывает количество значений в списке аргументов.
  • СЧИТАТЬПУСТОТЫ() — COUNTBLANK() - Подсчитывает количество пустых ячеек в заданном диапазоне.
  • СЧЁТЕСЛИ() — COUNTIF() — Подсчитывает количество непустых ячеек, удовлетворяющих заданному условию внутри диапазона.
  • КОВАР() — COVAR() — Определяет ковариацию, то есть среднее произведений отклонений для каждой пары точек.
  • КРИТБИНОМ() — CRITBINOM() — Находит наименьшее значение, для которого биномиальная функция распределения меньше или равна заданному значению.
  • КВАДРОТКЛ() — DEVSQ() — Вычисляет сумму квадратов отклонений.
  • ЭКСПРАСП() — EXPONDIST() — Находит экспоненциальное распределение.
  • FРАСП() — FDIST() — Находит F-распределение вероятности.
  • FРАСПОБР() — FINV() — Определяет обратное значение для F-распределения вероятности.
  • ФИШЕР() — FISHER() — Находит преобразование Фишера.
  • ФИШЕРОБР() — FISHERINV() — Находит обратное преобразование Фишера.
  • ПРЕДСКАЗ() — FORECAST() — Вычисляет значение линейного тренда.
  • ЧАСТОТА() — FREQUENCY() — Находит распределение частот в виде вертикального массива.
  • ФТЕСТ() — FTEST() - Определяет результат F-теста.
  • ГАММАРАСП() — GAMMADIST() — Находит гамма-распределение.
  • ГАММАОБР() — GAMMAINV() — Находит обратное гамма-распределение.
  • ГАММАНЛОГ() — GAMMALN() — Вычисляет натуральный логарифм гамма функции.
  • СРГЕОМ() — GEOMEAN() — Вычисляет среднее геометрическое.
  • РОСТ() — GROWTH() — Вычисляет значения в соответствии с экспоненциальным трендом.
  • СРГАРМ() — HARMEAN() - Вычисляет среднее гармоническое.
  • ГИПЕРГЕОМЕТ() — HYRGEOMDIST() — Определяет гипергеометрическое распределение.
  • ОТРЕЗОК() — INTERCEPT() — Находит отрезок, отсекаемый на оси линией линейной регрессии.
  • ЭКСЦЕСС() — KURT() — Определяет эксцесс множества данных.
  • НАИБОЛЬШИЙ() — LARGE() — Находит k-ое наибольшее значение из множества данных.
  • ЛИНЕЙН() — LINEST() — Находит параметры линейного тренда.
  • ЛГРФПРИБЛ() — LOGEST() — Находит параметры экспоненциального тренда.
  • ЛОГНОРМОБР() — LOGINV() — Находит обратное логарифмическое нормальное распределение.
  • ЛОГНОРМРАСП() — LOGNORMDIST() — Находит интегральное логарифмическое нормальное распределение.
  • МАКС() — MAX() — Определяет максимальное значение из списка аргументов.
  • МАКСА() — MAXA() — Определяет максимальное значение из списка аргументов, включая числа, текст и логические значения.
  • МЕДИАНА() — MEDIAN() — Находит медиану заданных чисел.
  • МИН() — MIN() - Определяет минимальное значение из списка аргументов.
  • МИНА() — MINA() —  Определяет минимальное значение из списка аргументов, включая числа, текст и логические значения.
  • МОДА() — MODE() — Определяет значение моды множества данных.
  • ОТРБИНОМРАСП() — NEGBINOMDIST() — Находит отрицательное биномиальное распределение.
  • НОРМРАСП() — NORMDIST() — Выдает нормальную функцию распределения.
  • НОРМОБР() — NORMINV() — Выдает обратное нормальное распределение.
  • НОРМСТРАСП() — NORMSDIST() — Выдает стандартное нормальное интегральное распределение.
  • НОРМСТОБР() — NORMSINV() — Выдает обратное значение стандартного нормального распределения.
  • ПИРСОН() — PEARSON() — Определяет коэффициент корреляции Пирсона.
  • ПЕРСЕНТИЛЬ() — PERCENTILE() — Определяет k-ую персентиль для значений из интервала.
  • ПРОЦЕНТРАНГ() — PERCENTRANK() - Определяет процентную норму значения в множестве данных.
  • ПЕРЕСТ() — PERMUT() - Находит количество перестановок для заданного числа объектов.
  • ПУАССОН() — POISSON() — Выдает распределение Пуассона.
  • ВЕРОЯТНОСТЬ() — PROB() — Определяет вероятность того, что значение из диапазона находится внутри заданных пределов.
  • КВАРТИЛЬ() — QUARTILE() — Определяет квартиль множества данных.
  • РАНГ() — RANK() - Определяет ранг числа в списке чисел.
  • КВПИРСОН() — RSQ() — Находит квадрат коэффициента корреляции Пирсона.
  • СКОС() — SKEW() — Определяет асимметрию распределения.
  • НАКЛОН() — SLOPE() — Находит наклон линии линейной регрессии.
  • НАИМЕНЬШИЙ() — SMALL() — Находит k-ое наименьшее значение в множестве данных.
  • НОРМАЛИЗАЦИЯ() — STANDARDIZE() — Вычисляет нормализованное значение.
  • СТАНДОТКЛОН() — STDEV() — Оценивает стандартное отклонение по выборке.
  • СТАНДОТКЛОНА() — STDEVA() - Оценивает стандартное отклонение по выборке, включая числа, текст и логические значения.
  • СТАНДОТКЛОНП() — STDEVP() — Определяет стандартное отклонение по генеральной совокупности.
  • СТАНДОТКЛОНПА() — STDEVPA() - Определяет стандартное отклонение по генеральной совокупности, включая числа, текст и логические значения.
  • СТОШYX() — STEYX() - Определяет стандартную ошибку предсказанных значений y для каждого значения x в регрессии.
  • СТЬЮДРАСП() — TDIST() — Выдает t-распределение Стьюдента.
  • СТЬЮДРАСПОБР() — TINV() — Выдает обратное t-распределение Стьюдента.
  • ТЕНДЕНЦИЯ() — TREND() — Находит значения в соответствии с линейным трендом.
  • УРЕЗСРЕДНЕЕ() — TRIMMEAN() —  Находит среднее внутренности множества данных.
  • ТТЕСТ() — TTEST() — Находит вероятность, соответствующую критерию Стьюдента.
  • ДИСП() — VAR() — Оценивает дисперсию по выборке.
  • ДИСПА() — VARA() — Оценивает дисперсию по выборке, включая числа, текст и логические значения.
  • ДИСПР() — VARP() — Вычисляет дисперсию для генеральной совокупности.
  • ДИСПРА() — VARPA() — Вычисляет дисперсию для генеральной совокупности, включая числа, текст и логические значения.
  • ВЕЙБУЛЛ() — WEIBULL() — Выдает распределение Вейбулла.
  • ZТЕСТ() — ZTEST() —  Выдает двустороннее P-значение z-теста.

Математические и тригонометрические функции

Математические и тригонометрические функции

  • ABS() — ABS() - Находит модуль (абсолютную величину) числа.
  • ACOS() — ACOS() - Вычисляет арккосинус числа.
  • ACOSH() — ACOSH() — Вычисляет гиперболический арккосинус числа.
  • ASIN() — ASIN() — Вычисляет арксинус числа.
  • ASINH() — ASINH() — Вычисляет гиперболический арксинус числа.
  • ATAN() — ATAN() - Вычисляет арктангенс числа.
  • ATAN2() — ATAN2() — Вычисляет арктангенс для заданных координат x и y.
  • ATANH() — ATANH() — Вычисляет гиперболический арктангенс числа.
  • ОКРВВЕРХ() — CEILING() — Округляет число до ближайшего целого или до ближайшего кратного указанному значению.
  • ЧИСЛКОМБ() — COMBIN() — Находит количество комбинаций для заданного числа объектов.
  • COS() — COS() — Вычисляет косинус числа.
  • COSH() — COSH() — Вычисляет гиперболический косинус числа.
  • ГРАДУСЫ() — DEGREES() — Преобразует радианы в градусы.
  • ЧЁТН() — EVEN() — Округляет число до ближайшего четного целого.
  • EXP() — EXP() — Вычисляет число e, возведенное в указанную степень.
  • ФАКТР() — FACT() - Вычисляет факториал числа.
  • ОКРВНИЗ() — FLOOR() — Округляет число до ближайшего меньшего по модулю целого.
  • НОД() — GCD() — Находит наибольший общий делитель.
  • ЦЕЛОЕ() — INT() — Округляет число до ближайшего меньшего целого.
  • НОК() — LCM() — Находит наименьшее общее кратное.
  • LN() — LN() — Вычисляет натуральный логарифм числа.
  • LOG() — LOG() — Вычисляет логарифм числа по заданному основанию.
  • LOG10() — LOG10() - Вычисляет десятичный логарифм числа.
  • МОПРЕД() — MDETERM() — Вычисляет определитель матрицы, хранящейся в массиве.
  • МОБР() — MINVERSE() — Определяет обратную матрицу (матрица хранится в массиве).
  • МУМНОЖ() — MMULT() — Вычисляет произведение матриц, хранящихся в массивах.
  • ОСТАТ() — MOD() - Вычисляет остаток от деления.
  • ОКРУГЛТ() — MROUND() — Находит число, округленное с требуемой точностью.
  • МУЛЬТИНОМ() — MULTINOMIAL() — Вычисляет мультиномиальный коэффициент множества чисел.
  • НЕЧЁТ() — ODD() — Округляет число до ближайшего нечетного целого.
  • ПИ() — PI() - Вставляет число «пи».
  • СТЕПЕНЬ() — POWER() — Вычисляет результат возведения числа в степень.
  • ПРОИЗВЕД() — PRODUCT() - Вычисляет произведение аргументов.
  • ЧАСТНОЕ() — QUOTIENT() - Вычисляет целую часть частного при делении.
  • РАДИАНЫ() — RADIANS() — Преобразует градусы в радианы.
  • СЛЧИС() — RAND() — Выдает случайное число в интервале от 0 до 1.
  • СЛУЧМЕЖДУ() — RANDBETVEEN() — Выдает случайное число в заданном интервале.
  • РИМСКОЕ() — ROMAN() — Преобразует число в арабской записи к числу в римской как текст.
  • ОКРУГЛ() — ROUND() — Округляет число до указанного количества десятичных разрядов.
  • ОКРУГЛВНИЗ() — ROUNDDOWN() — Округляет число до ближайшего меньшего по модулю целого.
  • ОКРУГЛВВЕРХ() — ROUNDUP() — Округляет число до ближайшего по модулю большего целого.
  • РЯД.СУММ() — SERIESSUM() - Вычисляет сумму степенного ряда по заданной формуле.
  • ЗНАК() — SIGN() — Определяет знак числа.
  • SIN() — SIN() - Вычисляет синус заданного угла.
  • SINH() — SINH() — Вычисляет гиперболический синус числа.
  • КОРЕНЬ() — SQRT() - Вычисляет положительное значение квадратного корня.
  • КОРЕНЬПИ() — SQRTPI() — Вычисляет значение квадратного корня из числа «пи».
  • ПРОМЕЖУТОЧНЫЕ.ИТОГИ() — SUBTOTAL() — Вычисляет промежуточные итоги.
  • СУММ() — SUM() — Суммирует аргументы.
  • СУММЕСЛИ() — SUMIF() — Суммирует ячейки, удовлетворяющие заданному условию(читать подробнее).
  • СУММПРОИЗВ() — SUMPRODUCT() — Вычисляет сумму произведений соответствующих элементов массивов(читать подробнее).
  • СУММКВ() — SUMSQ() - Вычисляет сумму квадратов аргументов.
  • СУММРАЗНКВ() — SUMX2MY2() — Вычисляет сумму разностей квадратов соответствующих значений в двух массивах.
  • СУММСУММКВ() — SUMX2PY2() — Вычисляет сумму сумм квадратов соответствующих элементов двух массивов.
  • СУММКВРАЗН() — SUMXMY2() — Вычисляет сумму квадратов разностей соответствующих значений в двух массивах.
  • TAN() — TAN() — Вычисляет тангенс числа.
  • TANH() — TANH() — Вычисляет гиперболический тангенс числа.
  • ОТБР() — TRUNC() - Отбрасывает дробную часть числа. 

Ссылки и массивы

Ссылки и массивы

  • АДРЕС() — ADRESS() — Выдает ссылку на отдельную ячейку рабочего листа в виде текста.
  • ОБЛАСТИ() — AREAS() — Определяет количество областей в ссылке.
  • ВЫБОР() — CHOOSE() — Выбирает значение из списка значений по индексу.
  • СТОЛБЕЦ() — COLUMN() — Определяет номер столбца, на который указывает ссылка.
  • ЧИСЛСТОЛБ() — COLUMNS() - Определяет количество столбцов в массиве или ссылке.
  • ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() — GETPIVOTDATA() — Возвращает данные, хранящиеся в сводной таблице.
  • ГПР() — HLOOKUP() - Ищет значение в первой строке массива и выдает значение из ячейки в найденном столбце и указанной строке.
  • ГИПЕРССЫЛКА() — HYPERLINK() - Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете.
  • ИНДЕКС() — INDEX() - По индексу получает значение из ссылки или массива.(читать подробнее)
  • ДВССЫЛ() — INDIRECT() — Определяет ссылку, заданную текстовым значением.
  • ПРОСМОТР() — LOOKUP() — Ищет значения в векторе или массиве.
  • ПОИСКПОЗ() — MATCH() — Ищет значения в ссылке или массиве.(читать подробнее)
  • СМЕЩ() — OFFSET() — Определяет смещение ссылки относительно заданной ссылки.
  • СТРОКА() — ROW() - Определяет номер строки, определяемой ссылкой.
  • ЧСТРОК() — ROWS() — Определяет количество строк в ссылке.
  • ДРВ() — RTD() - Извлекает данные реального времени из программ, поддерживающих автоматизацию COM.
  • ТРАНСП() — TRANSPOSE() — Выдает транспонированный массив.
  • ВПР() — VLOOKUP() - Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце.(читать подробнее)