B 1 в excel что это
Имя — это осмысленное краткое название, которое упрощает понимание назначения ссылка на ячейку, константа, формула или таблица, каждое из которых может быть сложно понять с первого взгляда. В следующих сведениях показаны распространенные примеры имен и показано, как они могут улучшить четкость.
Тип примера
Пример без имени
Пример с именем
Дополнительно об использовании имен
Существует несколько типов имен, которые можно создавать и использовать.
Определенное имя Имя, представляющее ячейку, диапазон ячеек, формулу или константное значение. Вы можете создать собственное определенное имя или Excel создать определенное имя, например при создании области печати.
Имя таблицы Имя таблицы Excel, которая является набором данных по определенной теме, которые хранятся в записях (строках) и полях (столбцах). Excel создает таблицу Excel "Таблица1", "Таблица2" и так далее при каждой вставке Excel таблицы. Вы можете изменить имя таблицы, чтобы сделать ее более осмысленной. Дополнительные сведения о Excel таблицах см. в этой Excel таблицах.
Все имена имеют область действия: это может быть определенный лист (локальный уровень листа) или вся книга (глобальный уровень книги). Область действия имени — это место, в котором имя распознается без требования. Например:
Если вы определили имя, например Budget_FY08, и его область действия — Лист1, это имя распознается только на листе "Лист1", но не на других листах.
Чтобы использовать локальное имя на другом, вы можете квалифицировать его перед именем. Например:
Если вы определили имя, например Sales_Dept_Goals, а область действия — книгу, это имя распознается для всех книг в книге, но не для других книг.
Имя должно быть уникальным в пределах своей области действия. Excel не позволяет определить имя, которое уже существует в пределах области действия. При этом можно использовать одинаковые имена в разных областях действия. Например, можно определить имя «ВаловойДоход» в областях действия «Лист1», «Лист2» и «Лист3» в одной и той же книге. Несмотря на одинаковость, каждое имя уникально в пределах своей области действия. Это можно сделать, чтобы формула с именем "ВаловойПримещение" всегда ссылалась на одинаковые ячейки на локальном уровне.
Можно даже определить это же имя «ВаловойДоход» на глобальном уровне книги, но область действия опять будет уникальной. Однако в этом случае может возникнуть конфликт имен. Чтобы устранить этот конфликт, по умолчанию Excel использует имя, определенное для этого таблицы, так как локальный уровень книги имеет приоритет над глобальным уровнем книги. Если вы хотите переопрепредидить приоритет и использовать имя книги, можно сделать это с неоднозначным, задав для этого имя книги. Например:
Локальный уровень можно переопрепредидить для всех книг. Исключением является первый таблица, которая всегда использует локальное имя, если конфликт имен не может быть переопределен.
Имя определяется с помощью:
Поле "Определенные имена" в области формул Этот способ лучше всего использовать для создания имени на уровне книги для выделенного диапазона.
Определение имени из выделения Можно удобно создавать имена из существующих имен строк и столбцов с помощью выделения ячеек на листе.
Диалоговое окно «Создание имени». Это лучше всего использовать, если нужна большая гибкость при создании имен, например, задание области определения на локальном уровне листа или создание примечания к имени.
Примечание. По умолчанию имена используют абсолютные ссылки на ячейки.
Имя можно ввести указанными ниже способами.
Ввода Введите имя, например, в качестве аргумента формулы.
Выбором из команды «Использовать в формуле». Выберите определенное имя на вкладке Формулы в группе Определенные имена из списка Использовать в формуле.
Вы также можете создать список определенных имен в книге. Выберите на листе место с двумя пустыми столбцами (список будет содержать два столбца: один для имен и один для описаний имен). Выберите ячейку, которая будет верхним левым углом списка. На вкладке Формулы в группе Определенные имена нажмите кнопку Использовать в формуле ,выберите вкладки Вировать, а затем в диалоговом окне В таблицу имена нажмите кнопку Список вкладки.
Ниже приводится список синтаксиса правил для создания и изменения имен.
Допустимые символы. Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания.
Совет. В качестве определенного имени нельзя использовать буквы "C", "c", "R" и "r", поскольку они используются в качестве краткого текста для выбора строки или столбца для выбранной ячейки при их вводе в текстовое поле Имя или Перейти.
Имена в виде ссылок на ячейки запрещены. Имена не могут быть такими же, как ссылки на ячейки, например, Z$100 или R1C1.
Пробелы не допустимы Использовать в имени пробелы нельзя. Используйте знаки подчеркиваия (_) и точка (.) в качестве разных слов, таких как Sales_Tax или Первый.квартал.
Длина имени. Имя может содержать до 255 символов.
Создание на листе имени для ячейки или диапазона ячеек
Выделите ячейку, диапазон ячеек или несмежные ячейки, которым вы хотите присвоить имя.
Щелкните поле Имя у левого края строка формул.
Введите имя, которое будет ссылаться на выбор. Длина имени не может превышать 255 знаков.
Нажмите клавишу ВВОД.
Примечание: При изменении содержимого ячейки ей нельзя присвоить имя.
Можно преобразовать в имена существующие заголовки строк и столбцов.
Выберите диапазон, которому вы хотите присвоить имя, включая заголовки строк и столбцов.
На вкладке Формулы в группе Определенные имена нажмите кнопку Создать из выделенного фрагмента.
В диалоговом окне Создание имен из выделенного диапазона укажите место, содержащее заголовки, установив флажок в строке выше, в столбце слева, в строке ниже или в столбце справа. Имя, созданное с помощью этой процедуры, ссылается только на ячейки, содержащие значения, и исключает существующие названия строк и столбцов.
На вкладке Формулы в группе Определенные имена выберите пункт Присвоить имя.
В диалоговом окне Новое имя в поле Имя введите имя, которое вы хотите использовать для ссылки.
Примечание. Длина имени не может быть больше 255 знаков.
Для задания области действия имени в поле со списком Область выберите элемент Книга или имя листа в книге.
Также в поле Примечание можно ввести описание длиной до 255 знаков.
В поле Диапазон выполните одно из указанных ниже действий.
Чтобы указать ссылку на ячейки, введите ссылку на ячейки.
Совет. По умолчанию в этом режиме ввели текущий выбор. Чтобы ввести другие ссылки на ячейки в качестве аргумента, нажмите кнопку Свернуть диалоговое окно (диалоговое окно будет временно сжато), выйдите ячейки на этом сайте и нажмите кнопку Развернуть .
Чтобы указать константу, введите = (знак равенства), а затем значение константы.
Чтобы указать формулу, введите =, а затем формулу.
Чтобы закончить и вернуться на лист, нажмите кнопку ОК.
Совет: Чтобы расширить или удлинить диалоговое окно Создание имени, щелкните и перетащите маркер захвата, расположенный внизу.
Управление именами с помощью диалогового окна диспетчера имен
Диалоговое окно Диспетчер имен используется для работы со всеми определенными именами и именами таблиц в книге. Например, может потребоваться найти имена с ошибками, подтвердить значение и ссылку на имя, просмотреть или изменить описательные комментарии или определить область действия. Вы также можете сортировать и фильтровать список имен, а также легко добавлять, изменять и удалять имена из одного расположения.
Чтобы открыть диалоговое окно Диспетчер имен, на вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
В диалоговом окне Диспетчер имен отображаются следующие сведения о каждом имени в списке.
Этот столбец:
Одно из следующих значений:
определенное имя, которое обозначается значком определенного имени;
имя таблицы, которое обозначается значком имени таблицы.
Текущее значение имени, такое как результаты формулы, строковая константа, диапазон ячеек, ошибка, массив значений или знаки-заполнители, если формулу не удается вычислить. Вот типичные примеры.
"это моя строковая константа"
Текущая ссылка для имени. Вот типичные примеры.
имя листа, если область действия — локальный уровень листа;
"Книга", если областью является глобальный уровень.
Дополнительные сведения об имени длиной до 255-ти знаков. Вот типичные примеры.
Срок действия этого значения истекает 2 мая 2007 г.
Не удалять! Критическое имя!
Основано на числах экзамена на сертификацию ISO.
При изменении содержимого ячейки нельзя использовать диалоговое окно Диспетчер имен.
В диалоговом окне Диспетчер имен не отображаются имена, определенные в Visual Basic для приложений (VBA) или скрытые имена (свойство Visible имени имеет вид False).
Чтобы автоматически вписать в столбец самое длинное значение в этом столбце, дважды щелкните правую часть его заглавного колонок.
Чтобы отсортировать список имен в порядке возрастания или убыния, щелкните заглавный колонок столбца.
Используйте команды раскрывающегося списка Фильтр для быстрого отображения подмножества имен. Выбор каждой команды отключает или отключает операцию фильтрации, что делает ее легкой и удобной для объединения или удаления различных операций фильтрации для получения нужных результатов.
Для фильтрации списка имен выполните одно или несколько следующих действий.
Имена на листе
Отобразить только локальные имена листа.
Имена в книге
Отобразить только глобальные имена в книге.
Имена с ошибками
Имена без ошибок
Отобразить только те имена, в значениях которых нет ошибок.
Определенные имена
Отобразить только те имена, которые определены пользователем или Excel, такое как имя области печати.
Имена таблиц
Отобразить только имена таблиц.
При изменении определенного имени или имени таблицы также изменяются все его использование в книге.
На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
В диалоговом окне Диспетчер имен щелкните имя, которое вы хотите изменить, и нажмите кнопку Изменить.
Совет. Вы также можете дважды щелкнуть имя.
В диалоговом окне Изменение имени введите новое имя для ссылки в поле Имя.
Измените ссылку в поле Диапазон и нажмите кнопку ОК.
В диалоговом окне Диспетчер имен в поле Диапазон измените ячейку, формулу или константу, представленную этим именем.
Чтобы отменить нежелательные или случайные изменения, нажмите кнопку Отмена или нажмите клавишу ESC.
Чтобы сохранить изменения, нажмите кнопку или нажмите ввод.
Кнопка Закрыть закрывает диалоговое окно Диспетчер имен. Он не требуется для уже внесенных изменений.
На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.
Выделите одно или несколько имен одним из способов, указанных ниже.
Для выделения имени щелкните его.
Чтобы выбрать несколько имен в группе, щелкните и перетащите их или нажмите клавишу SHIFT и нажмите кнопку мыши для каждого имени в группе.
Чтобы выбрать несколько имен в несмнимой группе, нажмите клавишу CTRL и, нажав кнопку мыши для каждого имени в группе.
Кнопка Закрыть закрывает диалоговое окно Диспетчер имен. Он не требуется для уже внесенных изменений.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
На прошлом шаге мы создали целый столбец значений просто воспользовавшись функцией автозаполнения Excel.
Программа автоматически «поняла» нашу задумку и значения адресов ячеек в формулы были подставлены правильные, но ка это произошло?
Практически во всех формулах используются адреса ячеек, а правильнее сказать - ссылки на ячейки, что позволяет нам использовать в формулах значения, которые находятся или рассчитываются в других ячейках.
Если в формуле сделана ссылка на ячейку В3 (в которой находится какое-то число) и мы поменяем значение в этой ячейке, то автоматически будет пересчитана и формула.
Поясню на примере.
В ячейку B3 введем цифру 2 , а в ячейку B4 вставим следующую формулу: = B3+3
Какой будет результат вычислений?
Правильно, после нажатия клавиши Enter в ячейке B4 отобразится вычисленное значение - 5 .
Что произойдет, если мы изменим значение ячейки B3 с 2 на 4 ?
Верно! Значение в ячейке B4 автоматически пересчитается и будет равным 7 . Это означает, что формула будет рассчитываться в соответствии с новым значением ячейки В3.
Если бы не было ссылок (в нашем примере это ссылка на ячейку B3), то нам бы пришлось вручную изменять все формулы при изменении какого-то одного ее компонента.
Итак, в формулах у нас могут участвовать ссылки на адреса ячеек таблицы. Эти ссылки могут быть относительными или абсолютными . Как показывает время, тема эта не очень проста для понимания, поэтому давайте с ней тщательно разберемся.
Мы с вами научились использовать автозаполнение для тиражирования однотипных формул в таблице. По сути автозаполнение нам позволяет скопировать формулу из одной ячейки и вставить ее в следующую. То же самое происходит и при фактическом копировании. Чтобы в этом убедиться, давайте скопируем ячейку В8. Для этого выделим ячейку (1) и щелкнем на ней правой кнопкой мыши для вызова контекстного меню (2).
Данное меню называется контекстным, так как его содержимое изменяется в зависимости от того, на каком объекте оно вызывается, то есть от контекста конкретной ситуации.
Выберем из контекстного меню пункт Копировать . Ячейка выделилась динамической рамкой.
Это нам указывает на то, что содержимое данной ячейки только что было скопировано.
Теперь установим табличный курсор в пустую ячейку В19 и вставим скопированную информацию. Для этого мы также можем воспользоваться контекстным меню, но мы задействуем панель инструментов Буфер обмена на вкладке Главная - нажимаем на кнопку Вставить и получаем результат.
Если мы посмотрим на формулу в ячейке B19, то увидим в ней ссылку на ячейка В18.
Понимаете что произошло? Это очень важно понять!
Мы копировали ячейку В8, в которой в формуле использовалась ссылка на вышестоящую ячейку В7 (1, см. рис. выше), то есть использовалась ссылка на ячейку, которая была выше ячейки с формулой. Именно так она и была скопирована - в ячейке В19 (2, см. рис. выше) в формуле используется ссылка на вышестоящую ячейку В18. То есть адрес ячейки в формуле берется относительно местоположения ячейки с формулой .
Для проверки попробуйте ответить на вопрос - если сейчас скопировать формулу из ячейки В7 (см. рис. выше), которая у нас несколько отличается от формул в других ячейках, и вставить ее в ячейку В20, то ссылка на какую ячейку мы увидим в итоге?
Давайте проверим - в формулу войдет ячейка B16, которая находится выше на четыре ячейки, как и ячейка В3, относительно ячейки В7.
Вот такие ссылки называются относительными , так как в формулу подставляется значение из ячейки, расположенной на определенном расстоянии относительно ячейки, с которой мы работаем.
А что делать, если нужно, чтобы в формуле участвовало значение из какой-то конкретной ячейки? Все логично - нужно использовать абсолютные ссылки , то есть ссылки на конкретные ячейки таблицы.
Как мы знаем, адрес ячейки состоит из обозначения столбца и строки, например, ячейка B3 находится на пересечении третьей строки и столбца B.
Чтобы сделать ссылку на ячейку абсолютной, нужно поставить знак $ перед обозначением столбца и строки (знак доллара ставится с помощью сочетаний клавиш Shift + 4 ). То есть, если я хочу, чтобы в формуле у меня всегда участвовало значение из ячейки В3, то изменю В3 на $B$3.
В формулах EXCEL можно сослаться на значение другой ячейки используя ее адрес (=А1). Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы введете адрес в формулу, будет зависеть, как он будет модифицироваться при ее копировании в другие ячейки листа. Это пригодится при как построении обычных формул на листе, так и при создании Именованных формул , задания правил Условного форматирования и при формировании условий Проверки данных .
В подавляющем большинстве формул EXCEL используются ссылки на ячейки. Например, если в ячейке В1 содержится формула =А1+5 , то означает, что в ячейку В1 будет помещено значение ячейки А1 находящейся на пересечении столбца А и строки 1 , к которому прибавлено число 5. Также в формулах используются ссылки на диапазоны ячеек, например, формула =СУММ(А2:А11) вычисляет сумму значений из ячеек А2 , А3 , . А11 . Однако, формула =СУММ($А$2:$А$11) также вычисляет сумму значений из тех же ячеек. Тогда в чем же разница? Разница проявляется при копировании этой формулы в соседние ячейки.
Абсолютная адресация (абсолютные ссылки)
Для создания абсолютной ссылки используется знак $. Ссылка на диапазона записывается ввиде $А$2:$А$11 . Абсолютная ссылка позволяет при копировании формулы зафиксировать адрес диапазона или адрес ячейки. Рассмотрим пример.
Пусть в ячейке В2 введена формула =СУММ( $А$2:$А$11 ) , а в ячейке С2 формула =СУММ(А2:А11). Скопировав формулы вниз, например с помощью Маркера заполнения, во всех ячейках столбца В получим одну и ту же формулу =СУММ( $А$2:$А$11 ) , т.е. ссылка на диапазон ячеек при копировании не изменилась . А в столбце С получим другой результат: в ячейке С3 будет формула =СУММ(A3:A12) , в ячейке С4 будет формула =СУММ(A4:A13) и т.д. Т.е. при копировании ссылка была модифицирована .
Какая формула лучше? Все зависит от вашей задачи: иногда при копировании нужно фиксировать диапазон, в других случая это делать не нужно.
Другой пример.
Пусть в диапазоне А1:А5 имеются числа (например, зарплата сотрудников отдела), а в С1 – процент премии установленный для всего отдела. Для подсчета премии каждого сотрудника необходимо все зарплаты умножить на % премии. Рассчитанную премию поместим в диапазоне В1:В5 . Для этого введем в ячейку В1 формулу =А1*С1 . Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в В2:В5 нули (при условии, что в диапазоне С2:С5 нет никаких значений). В ячейке В5 будем иметь формулу =А5*С5 (EXCEL при копировании формулы модифицировал ссылки на ячейки, т.к. их адреса не были записаны в виде абсолютных ссылок).
Чтобы выйти из ситуации - откорректируем формулу в ячейке В1 .
Такм образом, введем в В1 формулу =А1*$С$1 . Это можно сделать и в ручную, введя знак $ перед буквой столбца и перед номером строки.
Нажмем ENTER и протянем ее вниз. Теперь в В5 будет правильная формула =А5*$С$1 . Всем сотрудникам теперь достанется премия :).
Относительная адресация (относительные ссылки)
Введем в ячейку B1 формулу =А1 , представляющую собой относительную ссылку на ячейку А1 . Что же произойдет с формулой при ее копировании в ячейки расположенные ниже В1 ? После протягивания ее вниз Маркером заполнения , в ячейке В5 будет стоять формула =А5 , т.е. EXCEL изменил первоначальную формулу =A1 . При копировании вправо в ячейку С1 формула будет преобразована в =В1.
Теперь примеры.
Пусть в столбце А введены числовые значения. В столбце B нужно ввести формулы для суммирования значений из 2-х ячеек столбца А : значения из той же строки и значения из строки выше.
Т.е. в B2 должна быть формула: =СУММ(A1:A2) , в B3 : =СУММ(A2:A3) и т.д.
Решить задачу просто: записав в B2 формулу =СУММ(A1:A2) , протянем ее с помощью Маркера заполнения в ячейку B3 и ниже.
Альтернативное решение
Другим вариантом решения этой задачи является использование Именованной формулы . Для этого:
- выделите ячейку B2 (это принципиально при использовании относительных ссылок в Именах ). Теперь B2 – активная ячейка;
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите, например Сумма2ячеек ;
- убедитесь, что в поле Диапазон введена формула =СУММ(A1:A2)
- Нажмите ОК.
Теперь в B2 введем формулу = Сумма2ячеек . Результат будет тот, который мы ожидали: будет выведена сумма 2-х ячеек из столбца слева (см. файл примера , лист пример1 ). Если формулу ввести в ячейку B5 , то она будет суммировать ячейки A4:A5 , если ввести в D10 , то – ячейки С9:С10 .
Другими словами, будут суммироваться 2 ячейки соседнего столбца слева, находящиеся на той же строке и строкой выше. Ссылка на диапазон суммирования будет меняться в зависимости от месторасположения формулы на листе, но «расстояние» между ячейкой с формулой и диапазоном суммирования всегда будет одинаковым (один столбец влево).
Относительная адресация при создании формул для Условного форматирования.
Пусть необходимо выделить в таблице, содержащей числа от 1 до 100, значения больше 50, причем, только в четных строках (см. файл примера , лист пример2 ). Построим такую таблицу:
Важно отметить, что, если бы, при создании правила, активной ячейкой была F11 , то формулу необходимо было переписать: =И(ОСТАТ($A11;2)=$I$1;F11>50) . Поменять необходимо только ссылки незафиксированные знаком $: B2 на F11 и $A2 на $A11 .
Внимание! При использовании относительной адресации в Именованных формулах , Именованных диапазонах , Условном форматировании , Проверке данных (примеры см. в соответствующих статьях) необходимо следить, какая ячейка является активной в момент создания формулы (активной может быть только одна ячейка на листе, не смотря на то, что выделено может быть несколько).
Смешанные ссылки
Смешанные ссылки имеют формат =$В3 или =B$3 . В первом случае при копировании формулы фиксируется ссылка на столбец B , а строка может изменяться в зависимости при копировании формулы.
Предположим, у нас есть столбец с ценами в диапазоне B 3: B 6 (см. файл примера , лист пример3 ). В столбцах С, D , Е содержатся прогнозы продаж в натуральном выражении по годам (в шт.). Задача: в столбцах F , G , H посчитать годовые продажи в рублях, т.е. перемножить столбцы С, D , Е на столбец B . Использование механизма относительной адресации позволяет нам ввести для решения задачи только одну формулу. В ячейку F вводим: =$В3*C3 . Потом протягиваем формулу маркером заполнения вниз до F 6 ,
а затем весь столбец таблицы протягиваем вправо на столбцы G и H .
Обратите внимание, что в формуле =$В3*C3 перед столбцом B стоит значок $. При копировании формулы =$В3*C3 в ячейки столбцов F, G и H , этот значок $ говорит EXCEL о том, что ссылку на столбец B модифицировать не нужно. А вот перед столбцом С такого значка нет и формула в ячейке H6 примет вид =$В6*E6 .
Вводим знак $ в адрес ячейки
Существует несколько возможностей при вводе формулы ввести знак $ в адрес ячейки или диапазона. Рассмотрим ввод на примере формулы =СУММ($А$2:$А$5)
1. Ввести знак $ можно вручную, последовательно вводя с клавиатуры все знаки =СУММ($А$2:$А$5)
2. С помощью клавиши F4 (для ввода абсолютной ссылки):
- Введите часть формулы без ввода $: =СУММ(А2:А5
- Затем сразу нажмите клавишу F4 , знаки $ будут вставлены автоматически: =СУММ( $А$2:$А$5
- Для окончания ввода формулы нажмите ENTER.
Если после ввода =СУММ(А2:А5 в формуле передвинуть курсор с помощью мыши в позицию левее,
а затем вернуть его в самую правую позицию (также мышкой),
то после нажатия клавиши F4 , знаки $ будут автоматически вставлены только во вторую часть ссылки! =СУММ( А2:$А$5
Чтобы вставить знаки $ во всю ссылку, выделите всю ссылку А2:$А$5 или ее часть по обе стороны двоеточия, например 2:$А , и нажмите клавишу F4. Знаки $ будут автоматически вставлены во всю ссылку $А$2:$А$5
3. С помощью клавиши F4 (для ввода относительной ссылки).
- Введите часть формулы без ввода $: =СУММ(А2:А5
- Затем сразу нажмите клавишу F4 , будут автоматически вставлены знаки $: =СУММ( $А$2:$А$5
- Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ( А$2:А$5 (фиксируются строки)
- Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ($ А2:$А5 (фиксируется столбец)
- Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ( А2:А5 (относительная ссылка). Последующие нажатия изменяют ссылку заново по кругу.
- Для окончания ввода нажмите ENTER.
Чтобы изменить только первую или втрорую часть ссылки - установите мышкой курсор в нужную часть ссылки и последовательно нажимайте клавушу F4.
"СуперАбсолютная" адресация
В заключении расширим тему абсолютной адресации. Предположим, что в ячейке B 2 находится число 25, с которым необходимо выполнить ряд вычислений, например, возвести в разные степени (см. файл примера , лист пример4 ). Для этого в столбце C напишем формулу возведения в степень (значения степени введем в столбец D ): =$B$2^$D2 .
Мы использовали абсолютную ссылку на ячейку B 2 . При любых изменениях положения формулы абсолютная ссылка всегда будет ссылаться на ячейку, содержащую наше значение 25 :
- при копировании формулы из С3 в Н3 – формула не изменится, и мы получим правильный результат 625 ;
- при вставке нового столбца между столбцами А и В – формула превратится в =$C$2^$E3 , но мы снова получим правильный результат 625 .
Все правильно, т.к. это и есть суть абсолютной адресации: ссылки автоматически модифицируются для сохранения адресации на нужные ячейки при любых модификациях строк и столбцах листа (ну, кроме удаления ячейки с формулой, конечно). Однако бывают ситуации, когда значения на лист попадают из внешних источников. Например, когда созданный пользователем макрос вставляет внешние данные в ячейку B 2 (т.е. всегда во второй столбец листа). Теперь, при вставке столбца между столбцами А и В – формула как и раньше превратится в =$C$2^$E3 , но т.к. исходное число (25) будет вставляться макросом не в С2 , а по прежнему в ячейку B 2 , и мы получим неправильный результат.
Вопрос: можно ли модифицировать исходную формулу из С2 ( =$B$2^$D2 ), так чтобы данные все время брались из второго столбца листа и независимо от вставки новых столбцов?
Решение заключается в использовании функции ДВССЫЛ() , которая формирует ссылку на ячейку из текстовой строки. Если ввести в ячейку формулу: =ДВССЫЛ("B2") , то она всегда будет указывать на ячейку с адресом B2 вне зависимости от любых дальнейших действий пользователя, вставки или удаления столбцов и т.д.
Небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ() выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО() :
При ссылке на ячейку В2 с другого листа =ДВССЫЛ("пример4!B2") может возникнуть и другая сложность: при изменении названия листа пример4 – формула перестает работать. Но это также можно обойти – см. пример из статьи Определяем имя листа .
Другим способом заставить формулу ссылаться на один и тот же столбец является использование функции СМЕЩ() – об этом читайте статью Как заставить формулу все время ссылаться на один и тот же столбец .
Функция ЕСЛИ() относится к наиболее часто используемым функциям. Поэтому рассмотрим ее подробно.
Синтаксис функции
ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])
Функция имеет 3 аргумента.
Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, выражение A1>100. Если в ячейке А1 содержится значение 102, то выражение A1>100 вернет ИСТИНА, т.к. 102>100. Если в ячейке А1 содержится значение 88, то выражение вернет ЛОЖЬ, т.к. неравенство 88>100 неверно. Полностью формула может выглядеть так =ЕСЛИ(A1>100;"Бюджет проекта превышен";"ОК!") Если в ячейке A1 содержится значение большее 100, то формула вернет текстовое значение Бюджет проекта превышен, а если меньше 100 или равное 100, то будет выведено ОК!
Второй аргумент - Значение_если_истина и третий аргумент значение_если_ложь
Как было показано выше, если логическое выражение (первый аргумент функции) принимает значение ИСТИНА, то функция возвращает значение из второго аргумента значение_если_истина. Соответственно, если логическое выражение принимает значение ЛОЖЬ, то функция возвращает значение из третьего аргумента значение_если_ложь .
Второй и третий аргумент могут быть любым значением (число или текст) или являться выражением (формулой с функциями). Например формула =ЕСЛИ(A1>100;0;А1*1%) позволяет вычислять премию для проектов, в которых не было превышения бюджета (>100). Если бюджет превышен (в А1 введено значение 200 или любое другое более 100), то формула вернет 0, если в А1 введено значение менее 100, то будет вычислено значение премии А1*1% (т.е. если в А1 число 90, то формула вернет 0,9).
Если в качестве второго и третьего аргументов введено выражение, то оно может содержать и функции, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше или равно 100, то по столбцу С . Не забывайте, что при значении А1 = 100 в данном случае будет срабатывать выражение из аргумента значение_если_ложь.
Аргумент значение_если_ложь можно опустить (не вводить), тогда при значении логического выражения =ЛОЖЬ будет выведено ЛОЖЬ. Например, если в А1 содержится 600, то формула =ЕСЛИ(A1>=1000;"Больше или равно 1000") вернет просто ЛОЖЬ. Понятно, что при значении логического выражения =ИСТИНА будет выведено "Больше или равно 1000", т.е. второй аргумент функции ЕСЛИ() - Значение_если_истина.
Вложенные ЕСЛИ (несколько условий)
Для построения более сложных проверок в одной формуле можно использовать несколько функций ЕСЛИ(). Для этого в качестве значений аргументов значение_если_истина и значение_если_ложь введите еще одно условие с функцией ЕСЛИ(). Например, для проверки в какой диапазон попадает значение из ячейки А8 запишите формулу
- Если значение из ячейки А8 менее 5, то будет выведено 1
- Если значение из ячейки А8 более 10, то будет выведено 3
- Если значение из ячейки А8 между 5 и 10 (включая 5 и 8), то будет выведено 2
Примечание : В версии EXCEL 2007 можно использовать до 64 вложенных друг в друга функций ЕСЛИ(). Но, пожалуйста, не делайте так. Такой подход чреват ошибками.
Примечание : Формулу =ЕСЛИ(A8 10;3;2)) можно переписать как =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) или ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1)
или более сложный вариант =ЕСЛИ(A8
Формула =ЕСЛИ(A8 10;3;2)) работает следующим образом: сначала вычисляется логическое выражение A8 =5, то EXCEL переходит к вычислению выражения ЕСЛИ(A8>10;3;2). Опять сначала вычисляется логическое выражение A8>10. Если оно истинно, то выводится 3, вычисления завершены. Если оно ложно, то выводится 2, вычисления завершены.
Чтобы грамотно написать формулу с вложенными ЕСЛИ() следуйте следующим простым правилам:
1) Определитесь с диапазонами:
1.1) они не должны пересекаться. В примере выше - 3 диапазона и значение из А8 гарантированно попадет только в один из них. Было бы ошибкой написать формулу типа =ЕСЛИ(A8 10;3;ЕСЛИ(A8 10;3;2)) на интересует вся числовая ось от минус бесконечности до +бесконечности. И все 3 диапазона полностью покрывают эту область.
1.3.) Не забудьте ставить знак равно, чтобы при значениях А8 совпадающих с границами интервалов не было неправильных вычислений. Например, если в формуле =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) забыть указать знак = в логическом выражении A8>=5, то при А8=5 формула =ЕСЛИ(A8>5;ЕСЛИ(A8>10;3;2);1) вернет 1, тогда как исходная формула вернет 2.
Примечание: В перечисленных выше случаях логические выражения вычисляются на основании одной переменной А8. Иногда нужно придумывать формулы с ЕСЛИ(), зависящие от 2-х и более ячеек: =ЕСЛИ(B8>=5;ЕСЛИ(A8>10;3;2);1). Это гораздо сложнее, но, конечно, возможно. Следуйте правилам 1.1)-1.3) и следите за диапазонами. Еще сложнее, если обе переменных являются зависимыми, т.е. например B8 вычисляется на основании А8. Написание таких формул требует предельной аккуратности и напрямую связано с корректностью формулирования самой задачи (возможно вы не продумали до конца как использовать такую сложную зависимость). Советую по возможности разбить вычисления на 2 или более формулы, чтобы проще контролировать результат вычисления.
2) Правильно записывайте логические выражения. Например, условие 5 10;3;2)) в строке формул можно записать как
Для этого поставьте курсор перед вторым ЕСЛИ(), нажмите и удерживайте клавишу ALT, нажмите ENTER.
4) для отладки вложенных ЕСЛИ вместо реальных вычислений (V8*10%-37500) используйте простые значения - последовательности 1; 2; 3 . Это покажет какой блок вложенных ЕСЛИ() срабатывает.
5) В ряде случаев от вложенных ЕСЛИ можно отказаться. Рассмотрим пример определения размера проекта от его бюджета
=ЕСЛИ(A1>=100;"Очень крупный проект";
ЕСЛИ(A1>=90;"Крупный проект";
ЕСЛИ(A1>=50;"Средний проект";"Малый проект ")))
Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы. Ниже приведено 2 эквивалентные формулы:
Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:
Формула =ПРОСМОТР(A1;;) выглядит необычно, но по сути второй аргумент , как и третий аргумент это запись содержимого 4-х ячеек. Вместо можно записать ссылку на диапазон ячеек, содержащих 4 значения 0;50;90;100. Круглые скобки нужны, чтобы сообщить, что в функцию передается не одно значение, а целый массив.
Еще пример. Пусть требуется вывести разный текст (например " 0") в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0. Это можно сделать без вложенных ЕСЛИ():
Примечание : -1E+307 - это самое маленькое отрицательное число в EXCEL, 1E-307 - это самое маленькое положительное число. Таким образом задается 3 диапазона: от минус бесконечности до 0 (0 не включая); 0 (диапазон состоит только из одного значения 0); от 0 до +бесконечности (0 не включая).
Второй массив можно заменить ссылкой на диапазон, например для того чтобы вывести результат вычисления формул:
Диапазон A27:A29 должен, понятно, содержать формулы (см. файл примера).
Опущен третий аргумент [значение_если_ложь]
Третий аргумент функции не обязателен. Если логическое условие не выполнено, т.е. ЛОЖЬ, а третий аргумент функции не указан, то функция вернет также значение ЛОЖЬ. Например:
Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.
Сложные логические выражения
В аргументе Лог_выражение можно использовать как простые сравнения типа F1>10, так и более сложные конструкции на основе функций И(), ИЛИ(), НЕ() и их комбинаций.
Основное правило - логические функции и их комбинации возвращают только одно из 2х логических значений: ЛОЖЬ или ИСТИНА.
Приведем несколько полезных примеров.
Формула =ЕСЛИ(ИЛИ(A1;A2);0;2) проверяет одновременность равенства значений А1 и А2 нулю. Если оба значения равны 0, то формула вернет 2. Во всех других случаях будет возвращен 0. А1 и А2 не должны быть пустыми ячейками.
Формула =ЕСЛИ(ИЛИ(A1=0;A2=0);0;2) проверяет равенство 0 хотя бы одного значения из А1 и А2.
Формула =ЕСЛИ(НЕ(A1>0);1;2) эквивалентна формуле =ЕСЛИ(A1 10") . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )
Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;">10") . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))
Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))
Если вы еще не Excel в Интернете, скоро вы увидите, что это не просто сетка для ввода чисел в столбцах или строках. Да, с помощью Excel в Интернете можно найти итоги для столбца или строки чисел, но вы также можете вычислять платежи по ипотеке, решать математические или инженерные задачи или находить лучшие сценарии в зависимости от переменных чисел, которые вы подключали.
Excel в Интернете делает это с помощью формул в ячейках. Формула выполняет вычисления или другие действия с данными на листе. Формула всегда начинается со знака равенства (=), за которым могут следовать числа, математические операторы (например, знак "плюс" или "минус") и функции, которые значительно расширяют возможности формулы.
Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.
Следующая формула использует функцию ПЛТ для вычисления платежа по ипотеке (1 073,64 долларов США) с 5% ставкой (5% разделить на 12 месяцев равняется ежемесячному проценту) на период в 30 лет (360 месяцев) с займом на сумму 200 000 долларов:
Ниже приведены примеры формул, которые можно использовать на листах.
=A1+A2+A3 Вычисляет сумму значений в ячейках A1, A2 и A3.
=КОРЕНЬ(A1) Использует функцию КОРЕНЬ для возврата значения квадратного корня числа в ячейке A1.
=СЕГОДНЯ() Возвращает текущую дату.
=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции ПРОПИСН.
=ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.
Элементы формулы
Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.
1. Функции. Функция ПИ() возвращает значение числа Пи: 3,142.
2. Ссылки. A2 возвращает значение ячейки A2.
3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
4. Операторы. Оператор ^ ("крышка") применяется для возведения числа в степень, а оператор * ("звездочка") — для умножения.
Использование констант в формулах
Использование операторов в формулах
Операторы определяют операции, которые необходимо выполнить над элементами формулы. Вычисления выполняются в стандартном порядке (соответствующем основным правилам арифметики), однако его можно изменить с помощью скобок.
Типы операторов
Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.
Арифметические операторы
Арифметические операторы служат для выполнения базовых арифметических операций, таких как сложение, вычитание, умножение, деление или объединение чисел. Результатом операций являются числа. Арифметические операторы приведены ниже.
Арифметический оператор
Возведение в степень
Операторы сравнения
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.
Оператор сравнения
>= (знак «больше или равно»)
Больше или равно
Меньше или равно
Текстовый оператор конкатенации
Амперсанд (&) используется для объединения (соединения) одной или нескольких текстовых строк в одну.
Текстовый оператор
Соединение или объединение последовательностей знаков в одну последовательность
Выражение «Северный»&«ветер» дает результат «Северный ветер».
Операторы ссылок
Для определения ссылок на диапазоны ячеек можно использовать операторы, указанные ниже.
Оператор ссылки
Оператор диапазона, который образует одну ссылку на все ячейки, находящиеся между первой и последней ячейками диапазона, включая эти ячейки.
; (точка с запятой)
Оператор объединения. Объединяет несколько ссылок в одну ссылку.
Оператор пересечения множеств, используется для ссылки на общие ячейки двух диапазонов.
Порядок выполнения Excel в Интернете формулах
В некоторых случаях порядок вычисления может повлиять на возвращаемое формулой значение, поэтому для получения нужных результатов важно понимать стандартный порядок вычислений и знать, как можно его изменить.
Порядок вычислений
Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равно(=).Excel в Интернете интерпретирует знаки после знака равно как формулу. После знака равно вычисляются элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычислений. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Приоритет операторов
Если в одной формуле несколько операторов, Excel в Интернете выполняет операции в том порядке, который показан в таблице ниже. Если формула содержит операторы с одинаковым приоритетом, например операторы деления и умножения, Excel в Интернете эти операторы оцениваются слева направо.
Возведение в степень
Умножение и деление
Сложение и вычитание
Объединение двух текстовых строк в одну
Использование круглых скобок
Чтобы изменить порядок вычисления формулы, заключите ее часть, которая должна быть выполнена первой, в скобки. Например, следующая формула дает результат 11, так как Excel в Интернете умножение выполняется перед с добавлением. В этой формуле число 2 умножается на 3, а затем к результату прибавляется число 5.
Если же изменить синтаксис с помощью скобок, Excel в Интернете сбавляет 5 и 2, а затем умножает результат на 3, чтобы получить 21.
В следующем примере скобки, в которые заключена первая часть формулы, принудительно Excel в Интернете сначала вычислить ячейки B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.
Использование функций и вложенных функций в формулах
Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.
Синтаксис функций
Приведенный ниже пример функции ОКРУГЛ, округляющей число в ячейке A10, демонстрирует синтаксис функции.
1. Структура. Структура функции начинается со знака равно (=), за которым следуют имя функции, открывая скобка, аргументы функции, разделенные запятой, и закрывая скобка.
2. Имя функции. Чтобы отобразить список доступных функций, щелкните любую ячейку и нажмите клавиши SHIFT+F3.
4. Всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.
Ввод функций
Диалоговое окно Вставить функцию упрощает ввод функций при создании формул, в которых они содержатся. При вводе функции в формулу в диалоговом окне Вставить функцию отображаются имя функции, все ее аргументы, описание функции и каждого из аргументов, текущий результат функции и всей формулы.
Чтобы упростить создание и редактирование формул и свести к минимуму количество опечаток и синтаксических ошибок, пользуйтесь автозавершением формул. После того как вы введите знак " ocpSection" role="region" aria-label="Вложенные функции">
Вложенные функции
В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в приведенной ниже формуле для сравнения результата со значением 50 используется вложенная функция СРЗНАЧ.
1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
Использование ссылок в формулах
Ссылка указывает на ячейку или диапазон ячеек на сайте и сообщает Excel в Интернете, где искать значения или данные, которые вы хотите использовать в формуле. С помощью ссылок в одной формуле можно использовать данные, которые находятся в разных частях листа, а также значение одной ячейки в нескольких формулах. Вы также можете задавать ссылки на ячейки разных листов одной книги либо на ячейки из других книг. Ссылки на ячейки других книг называются связями или внешними ссылками.
Стиль ссылок A1
Стиль ссылок по умолчанию По умолчанию в Excel в Интернете используется стиль ссылок A1, который ссылается на столбцы буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с числами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.
Ячейка или диапазон
Использование
Ячейка на пересечении столбца A и строки 10
Диапазон ячеек: столбец А, строки 10-20.
Диапазон ячеек: строка 15, столбцы B-E
Все ячейки в строке 5
Все ячейки в строках с 5 по 10
Все ячейки в столбце H
Все ячейки в столбцах с H по J
Диапазон ячеек: столбцы А-E, строки 10-20
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Различия между абсолютными, относительными и смешанными ссылками
Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.
Стиль трехмерных ссылок
Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.
При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.
Вставка или копирование Если вставить листы между листами 2 и 6, Excel в Интернете будет включать в расчет все значения из ячеек с A2 по A5 на добавленных листах.
Удалить Если удалить листы между листами 2 и 6, Excel в Интернете вы вычислите их значения.
Переместить Если переместить листы между листами 2 и 6 в место за пределами диапазона, на который имеется ссылка, Excel в Интернете удалит их значения из вычислений.
Перемещение конечного листа Если переместить лист 2 или 6 в другое место книги, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов.
Удаление конечного листа Если удалить лист 2 или 6, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов между ними.
Стиль ссылок R1C1
Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. В стиле R1C1 Excel в Интернете указывает на расположение ячейки с помощью R, за которым следует номер строки, и C, за которым следует номер столбца.
относительная ссылка на ячейку, расположенную на две строки выше в том же столбце
Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца
Относительная ссылка на строку, расположенную выше текущей ячейки
Абсолютная ссылка на текущую строку
При записи макроса Excel в Интернете некоторые команды с помощью стиля ссылок R1C1. Например, если записать команду (например, нажать кнопку "Автоумма"), чтобы вставить формулу, в которую добавляется диапазон ячеек, Excel в Интернете записи формулы со ссылками с помощью стиля R1C1, а не A1.
Использование имен в формулах
Можно создавать определенные имена для представления ячеек, диапазонов ячеек, формул, констант и Excel в Интернете таблиц. Имя — это значимое краткое обозначение, поясняющее предназначение ссылки на ячейку, константы, формулы или таблицы, так как понять их суть с первого взгляда бывает непросто. Ниже приведены примеры имен и показано, как их использование упрощает понимание формул.
Читайте также: