Эксель формула и: Функция И — Служба поддержки Майкрософт

Содержание

Как в Excel создавать имена для констант, диапазонов и формул — Трюки и приемы в Microsoft Excel

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

Рис. 1.5. Диалоговое окно «Создание имени»

Проделайте следующие шаги для наименования объекта:

  1. Выберите вкладку Формулы ленты инструментов, далее пункт Присвоить имя. Вы увидите диалоговое окно присвоения имени — см. рис. 1.5.
  2. В поле Имя введите желаемое имя для использования.
  3. В поле Диапазон введите необходимую константу, формулу или имя диапазона. Обратите внимание, что по умолчанию поле содержит имя текущего выделенного диапазона.
  4. Нажмите на кнопку ОК.

Теперь вы можете использовать созданное имя объекта вместо непосредственного его ввода. 3)/3.

Функция ПИ() в Excel возвращает значение р.

Работа с именами в формулах: вставка имен в формулы

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

Рис. 1.6. Выбор имени из списка

  1. Перейдите на вкладку Формулы ленты инструментов, далее нажмите на кнопку Использовать в формуле, и затем из раскрывающегося списка вы сможете выбрать необходимое имя.
  2. Перейдите на вкладку Формулы ленты инструментов, далее нажмите на кнопку Использовать в формуле и выберите последний пункт Вставить имена… Вы увидите диалоговое окно вставки имен, показанное на рис. 1.7. Также вы можете использовать клавишу F3 для вызова данного окна.
  3. При вводе первых букв имени Excel автоматически предложит вам использовать подходящие созданные имена. При этом переместитесь на необходимое имя и нажмите на клавишу Tab клавиатуры.

Рис. 1.7. Диалоговое окно «Вставка имени»

Применение имен к формулам

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

  1. Выберите несколько ячеек, если вы хотите применить имена к ним, или одну ячейку, если вы хотите применить созданные имена ко всему листу.
  2. Перейдите на вкладку Формулы ленты инструментов, далее раскрывающееся меню справа от кнопки Присвоить имя и далее из меню пункт Применить имена… Вы увидите диалоговое окно Применение имен (см. рис. 1.8).
  3. Выберите имя или имена для применения.
  4. Поставьте галочку Игнорировать тип ссылки для игнорирования относительной и абсолютной связи при присвоении имен.
  5. Поставьте галочку Использовать имена строк и столбцов для указания Excel использовать символические имена строк и столбцов (если это возможно) для указания отдельных ячеек.
  6. Нажмите ОК для присвоения имен.

Рис. 1.8. Диалоговое окно «Применение имен»

Игнорирование типа ссылки

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

Например, если формула содержит выражение =СУММ(А1:А10), а имя Продажи присвоено диапазону $A$1:$А$10. В случае выключения флажка Игнорировать тип ссылки, Excel не применит имя к диапазону в ячейке; имя Продажи указывает на абсолютный диапазон, в то время как формула в ячейке содержит относительное значение.

формулы, стандартные функции. Построение графиков.

 Создание
и удаление формулы

Скрыть
все

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

С
помощью констант
(Константа. Постоянное (не вычисляемое)
значение. Например, число 210 и текст
«Квартальная премия» являются константами.
Формула и результат вычисления формулы
константами не являются.)
и операторов
(Оператор. Знак или символ, задающий тип
вычисления в выражении. Существуют
математические, логические операторы,
операторы сравнения и ссылок. )
вычисления можно создать простую
формулу. Например, формула =5+2*3
позволяет умножить два числа, а затем
прибавить число к результату. В Microsoft
Office
Excel
используется обычный порядок математических
операций. В предыдущем примере операция
умножения (2*3) выполняется в первую
очередь, а затем к результату умножения
прибавляется число 5.

Также
формулу можно создать с помощью функции
(Функция. Стандартная формула, которая
возвращает результат выполнения
определенных действий над значениями,
выступающими в качестве аргументов.
Функции позволяют упростить формулы в
ячейках листа, особенно, если они длинные
или сложные.).
Например, в формулах =СУММ(A1:A2)
и СУММ(A1,A2)
для сложения значений в ячейках A1
и A2
используется функция СУММ.

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

Функции.
Любая функция, например ПИ(),
всегда начинается со знака равенства
(=).
(крышка) возводит число в степень, а
оператор *
(звездочка) перемножает числа

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

Создание
простой формулы с константами и
операторами

  1. Щелкните
    ячейку, в которую требуется ввести
    формулу.

  2. Введите
    =
    (знак равенства).

  3. Чтобы
    ввести формулу, выполните одно из
    указанных ниже действий.

    • Введите константы и операторы, которые
      необходимо использовать для вычисления. 2

      Возведение
      5 в квадрат

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

      Пример
      формулы

      Описание

      =A1+A2

      Сложение
      значений в ячейках A1
      и A2

      =A1-A2

      Вычитание
      значения в ячейке A2
      из значения в ячейке A1

      =A1/A2

      Деление
      значения в ячейке A1
      на значение в ячейке A2

      =A1*A2

      Умножение
      значения в ячейке A1
      на значение в ячейке A2

      =A1^A2

      Возведение
      значения в ячейке A1
      в степень, заданную значением в ячейке
      A2

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

  5. Нажмите
    клавишу ВВОД.

Создание
формулы со ссылками на ячейки и именами

Примеры
формул, приведенные в конце этого
раздела, содержат относительные
ссылки
(Относительная ссылка. Адрес ячейки в
формуле, определяемый на основе
расположения этой ячейки относительно
ячейки, содержащей ссылку. При копировании
ячейки относительная ссылка автоматически
изменяется. Относительные ссылки
задаются в форме A1.)
на другие ячейки и их имена
(Имя. Слово или строка знаков, представляющие
ячейку, диапазон ячеек, формулу или
константу. Понятные имена, такие как
«Продукты», используют для ссылок на
диапазоны, названия которых трудно
запомнить, например, Продажи!C20:C30.).
Ячейка, содержащая формулу, называется
зависимой, если ее значение зависит от
значений в других ячейках. Например,
ячейка B2
является зависимой, если она содержит
формулу =C2.

  1. Щелкните
    ячейку, в которую требуется ввести
    формулу.

  2. В
    строку
    формул
    (Строка формул. Панель в верхней части
    окна Microsoft
    Excel,
    которая используется для ввода или
    изменения значений или формул в ячейках
    или на диаграммах. На ней отображается
    константа или формула, содержащаяся в
    активной ячейке.)

    введите
    =
    (знак равенства).

  3. Выполните
    одно из указанных ниже действий.

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

Первая
ссылка на ячейку — B3,
цвет — синий, и диапазон ячеек имеет
синюю границу с квадратными углами.

Вторая
ссылка на ячейку — C3,
цвет — зеленый, и диапазон ячеек имеет
зеленую границу с квадратными углами.

Примечание.
Если
на цветной границе нет квадратного
угла, значит это ссылка на именованный
диапазон.

    • Для
      ввода ссылки на именованный диапазон,
      нажмите клавишу F3, выберите имя в поле
      Имя
      и нажмите кнопку ОК.

      Пример
      формулы

      Описание

      =C2

      Использует
      значение в ячейке C2

      =Лист2!B2

      Использует
      значение в ячейке B2
      на Лист2

      =Ответственность-Актив

      Вычитает
      значение в ячейке «Ответственность»
      из значения в ячейке «Актив»

    Может ли Excel одновременно отображать формулу и ее результат?

    Я очень мало знаю о программировании, и мой ответ не элегантен. Мне как будущему инженеру нужна была функция в Excel, которая могла бы отображать мне формулу. Проблема, которую я обнаружил в решении Indrek, заключается в том, что его функция возвращает мне такую ​​​​формулу: «C9 * C9 * pi ()», и я хотел, чтобы имена ячеек в отображении формулы (C9) были изменены на значения эти клетки; и это будет автоматически меняться всякий раз, когда эти числа изменяются в исходном C9.клетка.

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

    Первая функция вообще не моя. Он был получен с:

    https://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array

     Функция IsInArray(ar, item$) As Boolean
    Затемнить разделитель$, список$
    ' Chr(7) - это символ ASCII "Колокольчик". 
    ' Он был выбран из-за того, что его вряд ли можно найти в обычном массиве.
    разделитель = Хр(7)
    ' Создать строку списка, содержащую все элементы массива, разделенные разделителем.
    список = разделитель и соединение (ar, разделитель) и разделитель
    IsInArray = InStr (список, разделитель, элемент и разделитель) > 0
Конечная функция
Функция ГЕТФОРМУЛА(ячейка)
Dim alfabeto As String
Dim alfabetos() как строка
Dim формула как строка
Dim celda As String
Тусклый резак As String
Dim cutterarray() как строка
'Формула взята из ячейки
формула = ячейка.формула
'И объявлен алфавит для сравнения с формулой
alfabeto = "A,B,C,D,E,F,G,H,I,J,K,L,M,O,P,Q,R,S,T,U,V,W,X,Y ,З"
'Объявлен массив с элементами строки алфавита
альфабетос = Разделить (алфабетос, ",")
'Замена общих функций символом, иначе код рухнет
формула = Заменить(формула, "LOG", "#")
формула = Заменить(формула, "КОРЕНЬ", "?")
формула = Заменить(формула, "PI", "ñ")
'MsgBox (формула)
Отладка.Распечатать формулу
'Объявлены символы, указывающие, где может заканчиваться имя ячейки. ,(,)"
'Массив объявляется с символами из только что объявленной строки
cutterarray = Разделить (резак, ",")
Dim nuevaformula As String
'Для позиции i в длине формулы
Dim индекс как целое число
Для i = 1 To Len(формула)
    Debug.Print "Para iterativo=", i
    Debug.Print "Se tiene el digito", Mid(формула, i, 1)
        'если элемент i не является буквой, то добавить его в цепочку символов
        Если IsInArray(alfabetos, Mid(формула, i, 1)) = False Тогда
        Debug.Print "Que es un numero"
        новая формула = новая формула + середина (формула, я, 1)
        Debug.Print новая формула
        'если элемент буква, то надо получить название ячейки, найдены все числа
        'ячейки, пока не появится символ для вырезания, например: "*","/","+","-"
        Еще
        Debug.Print "Encontramos una letra"
        'Empezamos a buscar más números
        'Числа в имени ячейки будут найдены, чтобы изолировать номер ячейки
            Для s = от 2 до 7
            celda = Mid (формула, i, s)
            Debug. Print "Incrementamos una quedando position", celda
                Если (i + s - 1 = Len(формула)) = Ложь Тогда
                    'если символ для вырезания не найден в последнем приращении, значит, последнее число не достигнуто
                    'и нужно продолжать искать его
                    Если IsInArray(cutterarray, Right(celda, 1)) = False Тогда
                    celda = Mid (формула, i, s)
                    Debug.Print "En el incremento no se encontró cutter. La i correcta es", i + s - 1
                    Еще
                    'если он найден, имя ячейки
                    celda = Mid (формула, i, s - 1)
                    Debug.Print "Se encontró un cutter la celda es", celda
                    'цикл поиска должен быть нарушен
                    Debug.Print s, i
                    я = я + с - 2
                    Debug.Print "Daremos salto a i=", i
                    Debug.Print celda
                    новая формула = новая формула + CStr(Range(celda).Value)
                    Выход для
                    Конец, если
                Еще
                Debug. Print "se alcanzó la maxima cantidad de iteraciones posibles"
                celda = Mid (формула, i, s)
                Debug.Print "La celda encontrada fue", celda
                новая формула = новая формула + CStr(Range(celda).Value)
                Debug.Print новая формула
                nuevaformula = Заменить(nuevaformula, "#", "LOG10")
                nuevaformula = Заменить(nuevaformula, "?", "raiz")
                nuevaformula = Заменить(nuevaformula, "ñ", "pi")
                ISAAC = новая формула
                Debug.Print (новая формула)
                Выход из функции
                Конец, если
            'MsgBox (новая формула)
            Следующая с
        Конец, если
Далее я
nuevaformula = Заменить(nuevaformula, "#", "LOG10")
nuevaformula = Заменить(nuevaformula, "?", "raiz")
nuevaformula = Заменить(nuevaformula, "ñ", "pi")
ГЕТФОРМУЛА = новая формула
Debug.Print (новая формула)
Конечная функция

    8 советов и рекомендаций по вводу формул Excel

    Эти полезные советы позволяют легко вводить формулы Excel

    Автор: Avantix Learning Team | Обновлено 10 апреля 2021 г. ввод формул в Excel . Ниже приведены некоторые распространенные, а также малоизвестные советы по быстрому и простому вводу формул в Excel.

    Рекомендуемая статья: Совет по Microsoft Excel: разверните панель формул

    Хотите узнать больше об Excel?  Посетите наш виртуальный класс или живой класс Курсы Excel >

    1. Используйте автозаполнение и Tab для ввода функций

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

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

    2. Пусть Excel добавит закрывающие скобки или квадратные скобки

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

    =AVERAGE(b1:b20

    Обратите внимание, что это не будет работать, если ваша формула содержит более одного набора скобок или квадратных скобок.

    3. Скопируйте формулы вниз, дважды щелкнув маркер заполнения

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

    4. Быстрое изменение относительных ссылок на ячейки на абсолютные ссылки

    Тип ссылки на ячейку по умолчанию в Excel является относительным (если копируется формула, она изменится относительно местоположения, в котором она скопирована). Например, A1 является относительной ссылкой. Если вы не хотите, чтобы ссылка на ячейку в формуле менялась при ее копировании, вы можете сделать ее абсолютной, добавив знак доллара ($) перед номером столбца и строки. Например, $A$1 является абсолютной ссылкой и не изменится при копировании. Чтобы быстро изменить выбранные ячейки в редактируемой формуле на абсолютные ссылки, нажмите F4.

    5. Скопируйте формулу с помощью перетаскивания и не допускайте изменения ссылок

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

    Чтобы временно изменить формулу на текст, в ячейке или строке формул введите апостроф (‘) в начале формулы и нажмите клавишу ВВОД. Чтобы использовать перетаскивание для копирования, поместите курсор на границу ячейки с формулой (указатель изменится на четыре стрелки, что является указателем перетаскивания), нажмите Control и левую кнопку мыши и перетащите границу ячейки. ячейку, содержащую формулу, в новое место, затем отпустите Control и кнопку мыши. Верните формулу в нормальное состояние, удалив апостроф (‘) и нажав Enter.

    6. Используйте всплывающую подсказку формулы для выбора аргументов

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

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

    7. Быстрая вставка заполнителей аргументов функций

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

    После того, как вы начали вводить формулу и набираете или выбираете допустимую функцию Excel, нажмите Control + Shift + A. Excel введет все аргументы, необходимые для функции. Затем вы можете дважды щелкнуть каждый аргумент и перейти к соответствующему значению (ячейкам), ячейке (ячейкам) или диапазону ячеек.

    Например, если вы введите:

    = vlookup

    , а затем нажмите Control + Shift + A, Excel будет вставить:

    = vlookup (lookup_value, table_array, col_index_num, range_lookup)

    Замените каждый аргумент (ячейки), ячейка (ячейки) или диапазон ячеек.

    8. Переместите всплывающую подсказку формулы в сторону

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

    Вот несколько отличных способов более эффективного ввода формул Excel.

    Подпишитесь, чтобы получать больше статей, подобных этой

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

    Дополнительные ресурсы

    Как заменить пустые ячейки в Excel значением из ячейки выше

    Как использовать мгновенное заполнение в Excel (4 способа)

    Как быстро удалить пустые строки в Excel (5 способов)

    Как конвертировать текст в числа в Excel (5 способов)

    Как конвертировать фунты в килограммы в Excel (фунты в кг)

    Связанные курсы

    Microsoft Excel: средний/высший уровень

    Microsoft Excel: анализ данных с функциями, информационными панелями и инструментами анализа «что, если»

    Microsoft Excel: введение на Visual Basic для приложений (VBA)

    ПОСМОТРЕТЬ ВСЕ КУРСЫ >

    Наши курсы под руководством инструктора проводятся в формате виртуального класса или в нашем центре Торонто по адресу 18 King Street East, Suite 1400, Торонто, Онтарио, Канада (некоторые в занятия в классе для одного человека также могут проводиться в другом месте в центре Торонто). Свяжитесь с нами по адресу [email protected], если вы хотите организовать индивидуальный виртуальный класс под руководством инструктора или обучение на месте в удобное для вас время.

    Copyright 2023 Avantix ® Обучение

    Функция XLOOKUP заменяет традиционную функцию Excel VLOOKUP (а также функции HLOOKUP и INDEX/MATCH). Он имеет новый набор аргументов и доступен в Excel 2021 и 365. Он позволяет искать значение в массиве в диапазоне или таблице и возвращать один или несколько результатов. Одно из основных преимуществ XLOOKUP заключается в том, что он может искать в столбцах слева в наборе данных и возвращать диапазон.

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

    Читайте также: