Квадратные скобки в формуле Excel, что они означают
Обновлено: 20.11.2024
Символы Excel – знаете ли вы, что они означают? Взгляните на список ниже!
Блог этой недели посвящен символам Excel. Они могут быть довольно простыми, но если вы не знаете, что они означают, это может быть пугающим или даже разочаровывающим! Некоторые из них более очевидны, чем другие.
Excel часто используется многими людьми для ввода данных, поэтому людям часто не нужно использовать или понимать, что означают эти символы, поскольку они уже встроены в используемые ими электронные таблицы.
Но сталкивались ли вы когда-нибудь с ситуацией, когда вы нажимали на ячейку, открывали формулу и паниковали от увиденного? Все эти символы смешались вместе и не знают, что они означают?
Не волнуйтесь, это случается со всеми нами! Поэтому мы решили включить в наш базовый курс таблицу в качестве своего рода «глоссария», чтобы помочь вам, когда вы начинаете изучать Excel! Вы также можете узнать больше о сообщениях об ошибках в Excel, взглянув на список ниже!
Список символов Excel
Некоторые из вас были на нашем базовом курсе Excel и видели этот список в своих заметках по курсу, которые вы взяли с собой, однако некоторые из вас его не видели.
Поэтому мы подумали, что поделимся с вами этим списком всего, что мы собрали из некоторых часто используемых основных символов в Excel.
= Это знак равенства, который используется в начале формулы
+ Это знак сложения, который используется в суммах и формулах
– это знак вычитания, который используется в суммах и формулах
/ Это знак деления, который используется в суммах и формулах
* Это знак умножения, который используется в суммах и формулах
( ) Это круглые скобки, которые используются для группировки меньших сумм в более сложных формулах
: точка с запятой, используемая в формуле для создания диапазона ячеек (например, A2:B4)
. Это запятая, которая используется для разделения ссылок на ячейки в формулах (часто для несмежных ячеек)
$ Это знак доллара, который используется при создании абсолютных ссылок
% Это знак процента, который используется при работе с цифрами в процентах
[ ] Это квадратные скобки, которые используются для обозначения книги, которая связана с формулой
<р>! Это восклицательный знак, который используется для обозначения рабочего листа, связанного с формулойМы надеемся, что этот список будет вам полезен! Дайте нам знать, что вы думаете!
Все они используются в вычислениях и формулах в Excel, и они более подробно рассматриваются в наших однодневных курсах для начального и среднего уровня Excel.
Надоело видеть сообщения об ошибках в электронных таблицах и не знать, что они означают?
Нажмите здесь, чтобы узнать больше о том, что означают различные сообщения об ошибках в Excel.
Если вы хотите узнать, что еще рассматривается в наших курсах Excel, вы можете ознакомиться с нашими программами на нашем веб-сайте здесь, также загляните в раздел комментариев наших клиентов и узнайте, что думают другие, когда они пришли на один из наших курсов!
Вы увидите это (и подобные ему), если откроете встроенный шаблон с именем ExpenseReport в Excel 2007.
Предположительно, Meals — это название диапазона, но оно не появляется в списке имен диапазонов для книги.
Что означают скобки? Книга формул Excel (автор Джон Уокенбах) — довольно хороший источник всего, что связано с формулами, но он не говорит об этом ни слова.
Скобки в формулах
Я часто использую таблицы в Excel. Если выбран правильный параметр, Excel автоматически называет диапазоны в формулах, если я выбираю диапазоны ячеек для вставки в функции. При работе со всем столбцом таблицы результирующая формула, вставляемая Excel, всегда использует скобки. Например:
=СУММЕСЛИ('Факт с начала года'!$B$2:$AF$9999,'ACTPROJ Annual'!AC$5&AC$6,Table_Query_from_MS_Access_Database[NetRevenue])+СУММЕСЛИ('YEnd Proj'!$B$2:$AG$9999, 'ACTPROJ Annual'!AC$5&AC$6,Table_Query_from_MS_Access_Database4[NetRevenue])
Однако после беглого взгляда я не вижу формул, в которых это имеет место без имени соседней таблицы, как в вашем примере.
Надеюсь, это поможет.
Я знаю еще один
Ссылка на значение в таблице выглядит следующим образом:
Когда вы берете значение из таблицы, оно выглядит так:
[] скобки
Сирилл – Я думаю, ты что-то понял.
[] определенно что-то делает с таблицами данных
Я посмотрел, но не смог воспроизвести пример.
Вот вы здесь
Я уже писал об этом
шаблон
<р>1. При ссылке на другую книгу в формуле: <р>2. В VBA вы можете использовать [MyRange] вместо Range("MyRange"). но я не рекомендую этого делать, так как это на самом деле говорит VBA оценить, что находится в скобках.ПРОМЕЖУТОЧНЫЙ ИТОГ(109,ДИАПАЗОН) суммирует видимые ячейки в этом диапазоне, но если я ввожу ПРОМЕЖУТОЧНЫЙ ИТОГ(109,[приемы пищи]), я получаю сообщение об ошибке, даже если приемы пищи определены как диапазон. локальный или глобальный.
Эта формула действительно работает в шаблоне?
Что происходит, когда вы открываете окно формулы?
Это общая формула строки в таблице данных
Третий случай: имя столбца в квадратных скобках можно увидеть, когда вы выбираете «Показать строку итогов» с таблицей данных. Когда вы щелкаете правой кнопкой мыши по таблице данных и выбираете «Показать итоговую строку» и выбираете «СУММ», это то, что вы получаете в ячейке «Итого». «Питание» в скобках относится к столбцу «Питание» в таблице данных, а не к именованному диапазону. Таким образом, «ПРОМЕЖУТОЧНЫЙ ИТОГ (109, [Питание])» означает СУММУ столбца «Питание», где «Питание» — это столбец в таблице данных.
Ячейка суммы, вероятно, была скопирована из таблицы данных, и поэтому выглядела странно.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Mobile Еще. Меньше
При создании таблицы Excel Excel присваивает имя таблице и каждому заголовку столбца в таблице. Когда вы добавляете формулы в таблицу Excel, эти имена могут появляться автоматически, когда вы вводите формулу и выбираете ссылки на ячейки в таблице, а не вводите их вручную. Вот пример того, что делает Excel:
Вместо использования явных ссылок на ячейки
Excel использует имена таблиц и столбцов
Такая комбинация имен таблиц и столбцов называется структурированной ссылкой. Имена в структурированных ссылках изменяются всякий раз, когда вы добавляете или удаляете данные из таблицы.
Структурированные ссылки также появляются при создании формулы вне таблицы Excel, которая ссылается на табличные данные. Ссылки могут упростить поиск таблиц в большой книге.
Чтобы включить в формулу структурированные ссылки, щелкните ячейки таблицы, на которые вы хотите сослаться, вместо того, чтобы вводить ссылку на эту ячейку в формулу. Давайте воспользуемся приведенным ниже примером данных, чтобы ввести формулу, которая автоматически использует структурированные ссылки для расчета суммы комиссии за продажу.
Продавец
Сумма продажи
% комиссии
Сумма комиссии
Скопируйте пример данных из приведенной выше таблицы, включая заголовки столбцов, и вставьте их в ячейку A1 нового листа Excel.
Чтобы создать таблицу, выберите любую ячейку в диапазоне данных и нажмите Ctrl+T.
Убедитесь, что флажок "Моя таблица имеет заголовки" установлен, и нажмите "ОК".
В ячейке E2 введите знак равенства (=) и щелкните ячейку C2.
В строке формул после знака равенства появляется структурированная ссылка [@[Сумма продаж]].
Введите звездочку (*) сразу после закрывающей скобки и щелкните ячейку D2.
В строке формул после звездочки появляется структурированная ссылка [@[% комиссии]].
Нажмите Enter.
Excel автоматически создает вычисляемый столбец и копирует формулу вниз по всему столбцу, корректируя ее для каждой строки.
Что происходит, когда я использую явные ссылки на ячейки?
Если вы вводите явные ссылки на ячейки в вычисляемый столбец, может быть сложнее увидеть, что вычисляет формула.
На образце рабочего листа щелкните ячейку E2
В строке формул введите =C2*D2 и нажмите Enter.
Обратите внимание, что Excel копирует вашу формулу вниз по столбцу, но не использует структурированные ссылки. Если, например, вы добавите столбец между существующими столбцами C и D, вам придется пересмотреть формулу.
Как изменить имя таблицы?
Когда вы создаете таблицу Excel, Excel создает имя таблицы по умолчанию (Таблица1, Таблица2 и т. д.), но вы можете изменить имя таблицы, чтобы сделать его более осмысленным.
Выберите любую ячейку в таблице, чтобы отобразить вкладку Работа с таблицами > Дизайн на ленте.
Введите нужное имя в поле "Имя таблицы" и нажмите Enter.
В нашем примере данных мы использовали название DeptSales.
Используйте следующие правила для имен таблиц:
Используйте допустимые символы Всегда начинайте имя с буквы, символа подчеркивания (_) или обратной косой черты (\). Используйте буквы, цифры, точки и символы подчеркивания для остальной части имени. Вы не можете использовать «C», «c», «R» или «r» для имени, потому что они уже обозначены как ярлык для выбора столбца или строки для активной ячейки, когда вы вводите их в поле. Поле Имя или Перейти.
Не используйте ссылки на ячейки. Имена не могут совпадать со ссылкой на ячейку, например Z$100 или R1C1.
Не используйте пробел для разделения слов Пробелы нельзя использовать в имени. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (.). Например, DeptSales, Sales_Tax или First.Quarter.
Используйте не более 255 символов Имя таблицы может содержать до 255 символов.
Используйте уникальные имена таблиц. Повторяющиеся имена не допускаются. Excel не различает символы верхнего и нижнего регистра в именах, поэтому, если вы вводите «Продажи», но уже имеете другое имя с названием «ПРОДАЖИ» в той же книге, вам будет предложено выбрать уникальное имя.
Используйте идентификатор объекта. Если вы планируете использовать сочетание таблиц, сводных таблиц и диаграмм, рекомендуется добавлять к именам префикс типа объекта. Например: tbl_Sales для таблицы продаж, pt_Sales для сводной таблицы продаж и chrt_Sales для диаграммы продаж или ptchrt_Sales для сводной диаграммы продаж. При этом все ваши имена будут храниться в упорядоченном списке в диспетчере имен.
Правила синтаксиса структурированных ссылок
Вы также можете вручную вводить или изменять структурированные ссылки в формуле, но для этого нужно понимать синтаксис структурированных ссылок. Давайте рассмотрим следующий пример формулы:
Эта формула содержит следующие компоненты структурированных ссылок:
Имя таблицы: DeptSales — это пользовательское имя таблицы. Он ссылается на данные таблицы без каких-либо заголовков или итоговых строк. Вы можете использовать имя таблицы по умолчанию, например Table1, или изменить его, чтобы использовать собственное имя.
Спецификатор столбца: [Сумма продаж] и [Сумма комиссии] — это спецификаторы столбцов, в которых используются имена столбцов, которые они представляют. Они ссылаются на данные столбца без заголовка столбца или итоговой строки. Всегда заключайте спецификаторы в квадратные скобки, как показано.
Для создания или редактирования структурированных ссылок вручную используйте следующие правила синтаксиса:
Используйте скобки вокруг описателей Все описатели таблиц, столбцов и специальных элементов должны быть заключены в соответствующие квадратные скобки ([ ]). Спецификатор, который содержит другие спецификаторы, требует, чтобы внешние совпадающие скобки заключали внутренние совпадающие скобки других спецификаторов. Например: =DeptSales[[Продавец]:[Регион]]
Все заголовки столбцов представляют собой текстовые строки. Но они не требуют кавычек, когда используются в структурированной ссылке. Числа или даты, например 2014 или 01.01.2014, также считаются текстовыми строками. Вы не можете использовать выражения с заголовками столбцов. Например, выражение DeptSalesFYSummary[[2014]:[2012]] не будет работать.
Используйте скобки вокруг заголовков столбцов со специальными символами. Если есть специальные символы, весь заголовок столбца должен быть заключен в квадратные скобки, что означает, что в спецификаторе столбца требуются двойные скобки. Например: =DeptSalesFYSummary[[Общая сумма в долларах США]]
Вот список специальных символов, для которых в формуле нужны дополнительные скобки:
Одинарная кавычка (')
Двойная кавычка ("")
Больше символа (>)
Вот список специальных символов, для которых требуется escape-символ (‘) в формуле:
Одинарная кавычка (')
Рекомендуется использовать один пробел:
После первой левой скобки ([)
Перед последней правой скобкой (]).
Ссылочные операторы
Для большей гибкости при указании диапазонов ячеек вы можете использовать следующие операторы ссылок для объединения спецификаторов столбцов.
Эта структурированная ссылка:
С помощью:
Что такое диапазон ячеек:
Все ячейки в двух или более соседних столбцах
: (двоеточие) оператор диапазона
=DeptSales[Сумма продаж],DeptSales[Сумма комиссии]
Комбинация двух или более столбцов
, (запятая) оператор объединения
=Отдел продаж[[Продавец]:[Сумма продажи]] Отдел продаж[[Регион]:[% комиссии]]
Пересечение двух или более столбцов
(пробел) оператор пересечения
Особые спецификаторы элементов
Чтобы сослаться на определенные части таблицы, например только на строку итогов, вы можете использовать любой из следующих специальных спецификаторов элементов в своих структурированных ссылках.
Этот специальный спецификатор элемента:
Вся таблица, включая заголовки столбцов, данные и итоги (если есть).
Только строки данных.
Только строка заголовка.
Только общая строка. Если ничего не существует, возвращается значение null.
Только ячейки в той же строке, что и формула. Эти спецификаторы нельзя комбинировать ни с какими другими специальными спецификаторами элементов. Используйте их, чтобы задать поведение неявного пересечения для ссылки или переопределить поведение неявного пересечения и ссылаться на отдельные значения из столбца.
Квалификация структурированных ссылок в вычисляемых столбцах
При создании вычисляемого столбца вы часто используете структурированную ссылку для создания формулы. Эта структурированная ссылка может быть неквалифицированной или полной. Например, чтобы создать вычисляемый столбец «Сумма комиссии», в котором рассчитывается сумма комиссии в долларах, можно использовать следующие формулы:
Тип структурированной ссылки
=[Сумма продажи]*[% комиссии]
Умножает соответствующие значения из текущей строки.
=DeptSales[Сумма продаж]*DeptSales[% комиссии]
Умножает соответствующие значения для каждой строки для обоих столбцов.
Общее правило заключается в следующем: если вы используете структурированные ссылки в таблице, например, при создании вычисляемого столбца, вы можете использовать неполную структурированную ссылку, но если вы используете структурированную ссылку вне таблицы , необходимо использовать полную структурированную ссылку.
Примеры использования структурированных ссылок
Вот несколько способов использования структурированных ссылок.
Эта структурированная ссылка:
Что такое диапазон ячеек:
Все ячейки в столбце "Сумма продаж".
Заголовок столбца "% комиссии".
Итого столбца "Регион". Если строки "Итоги" нет, возвращается значение null.
Все ячейки в поле "Сумма продаж" и "% комиссии".
Только данные из столбцов % комиссии и суммы комиссии.
Только заголовки столбцов между «Регион» и «Сумма комиссии».
Итого в столбцах «Сумма продаж» по «Сумме комиссии». Если строки "Итоги" нет, возвращается значение null.
Только заголовок и данные % комиссии.
E5 (если текущая строка равна 5)
Стратегии работы со структурированными ссылками
При работе со структурированными ссылками учитывайте следующее.
Использование автозаполнения формул Вы можете обнаружить, что использование автозаполнения формул очень полезно при вводе структурированных ссылок и для обеспечения использования правильного синтаксиса. Дополнительные сведения см. в разделе Использование автозаполнения формул.
Решите, создавать ли структурированные ссылки для таблиц в полувыборах. По умолчанию при создании формулы щелчок по диапазону ячеек в таблице частично выделяет ячейки и автоматически вводит структурированную ссылку вместо диапазона ячеек в формуле. . Такое полувыборочное поведение значительно упрощает ввод структурированной ссылки. Вы можете включить или отключить это поведение, установив или сняв флажок «Использовать имена таблиц в формулах» в диалоговом окне «Файл» > «Параметры» > «Формулы» > «Работа с формулами».
Преобразование диапазона в таблицу и таблицы в диапазон При преобразовании таблицы в диапазон все ссылки на ячейки изменяются на эквивалентные им абсолютные ссылки в стиле A1. При преобразовании диапазона в таблицу Excel не заменяет автоматически ссылки на ячейки этого диапазона эквивалентными структурированными ссылками.
Добавление или удаление столбцов и строк в таблице Поскольку диапазоны данных в таблице часто меняются, ссылки на ячейки для структурированных ссылок корректируются автоматически. Например, если вы используете имя таблицы в формуле для подсчета всех ячеек данных в таблице, а затем добавляете строку данных, ссылка на ячейку изменяется автоматически.
Переименовать таблицу или столбец. Если вы переименуете столбец или таблицу, Excel автоматически изменит использование этой таблицы и заголовка столбца во всех структурированных ссылках, используемых в книге.
Перемещение, копирование и заполнение структурированных ссылок Все структурированные ссылки остаются неизменными при копировании или перемещении формулы, в которой используется структурированная ссылка.
Примечание. Копирование структурированной ссылки и заполнение структурированной ссылки — это не одно и то же. При копировании все структурированные ссылки остаются прежними, а при заполнении формулы полные структурированные ссылки изменяют спецификаторы столбцов, как ряды, как показано в следующей таблице.
Если вы новичок в Excel для Интернета, вы скоро обнаружите, что это больше, чем просто таблица, в которой вы вводите числа в столбцах или строках. Да, вы можете использовать Excel в Интернете, чтобы найти итоги для столбца или строки чисел, но вы также можете рассчитать платеж по ипотеке, решить математические или инженерные задачи или найти лучший сценарий на основе переменных чисел, которые вы подключаете.
Excel для Интернета делает это с помощью формул в ячейках. Формула выполняет вычисления или другие действия с данными на листе. Формула всегда начинается со знака равенства (=), за которым могут следовать числа, математические операторы (например, знак плюс или минус) и функции, которые действительно расширяют возможности формулы.
Например, следующая формула умножает 2 на 3, а затем добавляет к этому результату 5, чтобы получить ответ 11.
В следующей формуле используется функция ПЛТ для расчета платежа по ипотеке (1073,64 долл. США), основанного на процентной ставке 5 % (5 %, разделенные на 12 месяцев, равняется месячной процентной ставке) за 30-летний период (360 месяцев). ) для кредита в размере 200 000 долларов США:
Вот несколько дополнительных примеров формул, которые можно ввести на листе.
=A1+A2+A3 Складывает значения в ячейках A1, A2 и A3.
=SQRT(A1) Использует функцию SQRT для возврата квадратного корня из значения в A1.
=TODAY() Возвращает текущую дату.
=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции листа ПРОПИСН.
=IF(A1>0) Проверяет ячейку A1, чтобы определить, содержит ли она значение больше 0.
Части формулы
Формула также может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.
<р>1. Функции: функция PI() возвращает значение числа пи: 3,142. <р>2. Ссылки: A2 возвращает значение в ячейке A2. <р>3. Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2. <р>4. Операторы: оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает числа.Использование констант в формулах
Использование операторов вычисления в формулах
Операторы определяют тип вычисления, которое необходимо выполнить для элементов формулы. Существует порядок, в котором выполняются вычисления по умолчанию (это соответствует общим математическим правилам), но вы можете изменить этот порядок, используя круглые скобки.
Типы операторов
Существует четыре различных типа операторов вычисления: арифметические операции, сравнение, конкатенация текста и ссылка.
Арифметические операторы
Для выполнения основных математических операций, таких как сложение, вычитание, умножение или деление; комбинировать числа; и выводить числовые результаты, используйте следующие арифметические операторы.
Арифметический оператор
Операторы сравнения
Вы можете сравнить два значения с помощью следующих операторов. Когда два значения сравниваются с помощью этих операторов, результатом является логическое значение — либо ИСТИНА, либо ЛОЖЬ.
Оператор сравнения
> (знак больше)
= (знак больше или равно)
Больше или равно
(не равно знаку)
Оператор объединения текста
Используйте амперсанд (&), чтобы соединить (объединить) одну или несколько текстовых строк, чтобы получить единый фрагмент текста.
Текстовый оператор
Соединяет или объединяет два значения для создания одного непрерывного текстового значения
"Север"&"ветер" приводит к "Борей"
Ссылочные операторы
Объедините диапазоны ячеек для вычислений с помощью следующих операторов.
Оператор ссылки
Оператор диапазона, который создает одну ссылку на все ячейки между двумя ссылками, включая две ссылки.
Оператор объединения, который объединяет несколько ссылок в одну ссылку
Оператор пересечения, создающий одну ссылку на ячейки, общие для двух ссылок
Порядок, в котором Excel для Интернета выполняет операции в формулах
В некоторых случаях порядок, в котором выполняются вычисления, может повлиять на возвращаемое значение формулы, поэтому важно понимать, как определяется порядок и как можно изменить порядок, чтобы получить нужные результаты.
Порядок расчета
Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равенства (=). Excel в Интернете интерпретирует символы, следующие за знаком равенства, как формулу. После знака равенства следуют вычисляемые элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычисления. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Приоритет оператора
Если вы объединяете несколько операторов в одной формуле, Excel в Интернете выполняет операции в порядке, указанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом (например, если формула содержит оператор умножения и деления), Excel в Интернете оценивает операторы слева направо.
Описание
Отрицание (как в –1)
Умножение и деление
Сложение и вычитание
Соединяет две строки текста (объединение)
Использование скобок
Чтобы изменить порядок вычисления, заключите в круглые скобки ту часть формулы, которая будет вычисляться первой. Например, следующая формула дает 11, так как Excel в Интернете выполняет умножение перед сложением. Формула умножает 2 на 3, а затем добавляет к результату 5.
Наоборот, если вы используете круглые скобки для изменения синтаксиса, Excel для Интернета суммирует 5 и 2, а затем умножает результат на 3, чтобы получить 21.
В следующем примере круглые скобки, заключающие первую часть формулы, заставляют Excel для Интернета сначала вычислить B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.< /p>
Использование функций и вложенных функций в формулах
Функции – это предопределенные формулы, которые выполняют вычисления с использованием определенных значений, называемых аргументами, в определенном порядке или структуре. Функции можно использовать для выполнения простых или сложных вычислений.
Синтаксис функций
Следующий пример функции ОКРУГЛ, округляющей число в ячейке A10, иллюстрирует синтаксис функции.
<р>1. Структура.Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающая скобка, аргументы функции, разделенные запятыми, и закрывающая скобка. <р>2. Имя функции. Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите SHIFT+F3. <р>4. Подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, введите =ROUND( и появится всплывающая подсказка. Подсказки появляются только для встроенных функций.Ввод функций
При создании формулы, содержащей функцию, вы можете использовать диалоговое окно "Вставить функцию", чтобы упростить ввод функций рабочего листа. Когда вы вводите функцию в формулу, диалоговое окно «Вставить функцию» отображает имя функции, каждый из ее аргументов, описание функции и каждого аргумента, текущий результат функции и текущий результат всей формулы. .
Чтобы упростить создание и редактирование формул и свести к минимуму опечатки и синтаксические ошибки, используйте автозаполнение формул. После того как вы введете = (знак равенства) и начальные буквы или триггер отображения, Excel в Интернете отобразит под ячейкой динамический раскрывающийся список допустимых функций, аргументов и имен, которые соответствуют буквам или триггеру. Затем вы можете вставить элемент из раскрывающегося списка в формулу.
Вложенные функции
В некоторых случаях вам может понадобиться использовать функцию в качестве одного из аргументов другой функции. Например, следующая формула использует вложенную функцию СРЗНАЧ и сравнивает результат со значением 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
Диапазон ячеек в столбцах от A до E и строках с 10 по 20
Создание ссылки на другой лист В следующем примере функция рабочего листа AVERAGE вычисляет среднее значение для диапазона B1:B10 на листе Marketing в той же книге.
<р>1. Относится к рабочему листу под названием "Маркетинг" <р>2. Относится к диапазону ячеек от B1 до B10 включительно <р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеекРазница между абсолютными, относительными и смешанными ссылками
Относительные ссылки Относительная ссылка на ячейку в формуле, например A1, основана на относительном положении ячейки, содержащей формулу, и ячейки, на которую ссылается ссылка. Если положение ячейки, содержащей формулу, изменяется, ссылка изменяется. Если вы скопируете или заполните формулу между строками или столбцами, ссылка будет автоматически скорректирована. По умолчанию в новых формулах используются относительные ссылки. Например, если вы скопируете или заполните относительную ссылку из ячейки B2 в ячейку B3, она автоматически изменится с =A1 на =A2.
Абсолютные ссылки Абсолютная ссылка на ячейку в формуле, например $A$1, всегда указывает на ячейку в определенном месте. Если положение ячейки, содержащей формулу, изменяется, абсолютная ссылка остается прежней. Если вы скопируете или заполните формулу между строками или столбцами, абсолютная ссылка не изменится. По умолчанию в новых формулах используются относительные ссылки, поэтому вам может потребоваться переключить их на абсолютные ссылки.Например, если вы скопируете или заполните абсолютную ссылку из ячейки B2 в ячейку B3, она останется одинаковой в обеих ячейках: =$A$1.
Смешанные ссылки Смешанная ссылка имеет либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку принимает форму A$1, B$1 и т. д. Если положение ячейки, содержащей формулу, изменяется, относительная ссылка изменяется, а абсолютная ссылка не изменяется. Если вы копируете или заполняете формулу по строкам или столбцам, относительная ссылка корректируется автоматически, а абсолютная ссылка не корректируется. Например, если вы скопируете или заполните смешанную ссылку из ячейки A2 в ячейку B3, она изменится с =A$1 на =B$1.
Трехмерный эталонный стиль
Удобные ссылки на несколько листов Если вы хотите анализировать данные в одной и той же ячейке или диапазоне ячеек на нескольких листах в книге, используйте трехмерную ссылку. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, которому предшествует диапазон имен рабочих листов. Excel в Интернете использует все листы, хранящиеся между начальным и конечным именами ссылки. Например, =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5, на всех листах между листами 2 и 13 включительно.
Трехмерные ссылки можно использовать для ссылки на ячейки на других листах, для определения имен и создания формул с помощью следующих функций: СУММ, СРЗНАЧ, СРЗНАЧ, СЧЕТ, СЧЕТ, МАКС, МАКС, МИН, МИН, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA и VARPA.
Объемные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать с оператором пересечения (один пробел) или в формулах, использующих неявное пересечение.
Что происходит при перемещении, копировании, вставке или удалении листов В следующих примерах показано, что происходит при перемещении, копировании, вставке или удалении листов, включенных в трехмерную ссылку. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для добавления ячеек с A2 по A5 на листах со 2 по 6.
Вставка или копирование Если вы вставляете или копируете листы между Листами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 для Интернета. Имя — это значимое сокращение, упрощающее понимание назначения ссылки на ячейку, константы, формулы или таблицы, каждое из которых может быть трудно понять с первого взгляда. Следующая информация показывает распространенные примеры имен и то, как их использование в формулах может улучшить ясность и сделать формулы более понятными.
Читайте также: