Отладка формул в excel
В каждой ячейке, помимо значений, могут содержаться формулы. Запись формулы начинается со знака равно (=), а далее следует арифметическое выражение или название функции. Параметры функции записываются после имени функции в скобках через точку с запятой (;).
Некоторые функции не имеют параметров. В этом случае записываются пустые скобки. Например, =ПИ(), =СЕГОДНЯ().
Формулы могут содержать следующие арифметические действия:
^ возведение в степень.
В табличном процессоре Excel определены различные математические функции, некоторые из них представлены в табл. 10.2.
Таблица 10.2. Математические функции
табличного процессора Excel
| Функция | Описание |
| КОРЕНЬ | Квадратный корень |
| ФАКТР | Факториал |
| СУММ | Сумма аргументов |
| МАКС | Максимальное значение аргументов |
| МИН | Минимальное значение аргументов |
| СРЗНАЧ | Среднее арифметическое аргументов |
Эти и другие функции, их параметры и справку по ним можно найти с помощью Мастера функций (меню Вставка | Функция).
Пусть дано выражение
и значение х содержится в ячейке А1, а y – в A2, тогда соответствующая запись в ячейке A2 будет иметь вид:
Рассмотрим вычисление сложных формул. Пусть дано выражение
Воспользуемся функцией ЕСЛИ:
ЕСЛИ(Условие; Значение 1; Значение 2),
где Условие – логическое выражение; Значение 1 – значение, возвращаемое в случае истинности условия; Значение 2 – значение, возвращаемое в случае ложности условия.
Значение 1 и Значение 2 могут быть формулами. Если условие истинно или ложно, а соответствующее значение отсутствует, то возвращается значение 0.
Значение х содержится в ячейке А1, а y – в A2, тогда соответствующая запись в ячейке A2 будет иметь вид:
Чтобы изменить тип ссылки в формуле, необходимо выполнить следующие действия:
1) выбрать ссылку в формуле;
2) нажимать клавишу F4 до тех пор, пока ссылка не будет иметь нужный тип.
Формулы пересчитываются при вводе или изменении значений в ячейках, которые участвуют в вычислениях.
Чтобы принудительно пересчитать формулы во всех открытых книгах, нажмите клавишу F9.
Чтобы установить автоматический пересчет формул необходимо выполнить следующие действия:
1) выбрать пункт меню Сервис | Параметры;
2) выбрать вкладку Вычисления;
3) в группе Вычисления выбрать пункт Автоматически.
Чтобы просмотреть, какие формулы записаны в ячейках, необходимо перейти в режим проверки формул с помощью пункта меню Сервис | Зависимости формул | Режим проверки формул или сочетания клавиш CTRL + Ё.
Чтобы выявить ошибку, возникшую при вычислении формулы, необходимо выполнить следующие действия:
1) выбрать ячейку, где содержится формула с ошибкой;
2) выбрать пункт меню Сервис | Зависимости формул | Вычислить формулу;
3) в окне Вычисление формулы нажать кнопку Вычислить для каждой операции формулы; вычисляемая в данный момент операция подчеркивается, а последний полученный результат выделяется курсивом;
Чтобы выявить ячейки, влияющие (зависимые) на значение данной ячейки необходимо выполнить следующие действия:
1) выбрать ячейку, для которой необходимо выявить влияющие (зависимые) ячейки;
2) выбрать пункт меню Сервис | Зависимости формул | Влияющие ячейки (Зависимые ячейки).
В результате появятся стрелки, показывающие направления зависимостей значений ячеек. Если значение ячейки влияет (зависит) на значение ячейки, находящейся на другом листе, то рядом со стрелкой появится условное изображение листа книги табличного процессора Microsoft Excel.
Чтобы убрать стрелки зависимостей значений необходимо выбрать пункт меню Сервис | Зависимости формул | Убрать все стрелки.
Существует экспоненциальный вид записи числа, используемый в Excel. Любое число представляется в следующем виде:
где М – мантисса, находящаяся в пределах [0; 10); P – десятичный порядок.
Например, число 25 представляется, как 2,5E+01, что означает 2,5·10 1 .
Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов (88‰).
Механическое удерживание земляных масс: Механическое удерживание земляных масс на склоне обеспечивают контрфорсными сооружениями различных конструкций.
Опора деревянной одностоечной и способы укрепление угловых опор: Опоры ВЛ - конструкции, предназначенные для поддерживания проводов на необходимой высоте над землей, водой.
Общие условия выбора системы дренажа: Система дренажа выбирается в зависимости от характера защищаемого.
Все мы при работе с excel рано или поздно сталкиваемся с формулами. В начале нашего пути мы начинаем использовать простейшие формулы с одной функцией (например, СУММ, ОКРУГЛВВЕРХ, МАКС и многие другие).
Однако по мере увеличения мастерства и усложнения проводимых расчетов, нам приходится создавать сложные формулы, в которых используется 2,3,4, а то и больше функций. В таких формулах достаточно сложно разобраться, особенно если видишь их в первый раз. Также в них легко ошибиться.
Я бы хотела привести несколько способов, которые позволят упростить работу с громоздкими формулами:
1. Чтобы не ошибиться при вводе формулы с большим количеством операторов, помните порядок их вычисления . В excel существует порядка 9 операторов, которые мы используем в формулах. Все они имеют свой приоритет при расчете формулы:
- ^ (возведение в степень) - первый приоритет;
- * (умножение) и / (деление) - второй приоритет;
- + (сложение) и - (вычитание) - третий приоритет;
- & (конкатенация) - четвертый приоритет;
- = (равно), < (меньше) и > (больше) - пятый приоритет.
При этом расчеты, заключенные в скобки, выполняются раньше, чем все остальные. Если используются вложенные скобки (скобки в скобках), то сначала вычисляется значение, находящееся во внутренних скобках, затем во внешних.
2. Если вы не уверены, что сможете прописать корректно аргументы функции, пользуйтесь диалоговым окном "Вставка функции" , которое находится на ленте во вкладке Формулы (слева). При открытии этого окна открывается поисковик функций, в котором можно найти необходимую (в примере ниже я искала ВПР). После того, как вы нашли нужную функцию открывается окно "Аргументы функции", в котром содержатся подсказки:
3. Для того, чтобы разобраться в сложной формуле используйте прием "вычисления фрагмента формулы" . Для этого в сложной формуле выделите ее фрагмент и нажмите клавишу F9 и тогда этот фрагмент формулы рассчитается и примет свое итоговое значение прямо в формуле. После этого обязательно нажмите Ctrl+Z, для того, чтобы отменить этот расчет.
Приведу пример на формуле из другой своей статьи. У нас есть таблица с объемом продаж менеджеров, в которой с помощью функций ИНДЕКС и ПОИСКПОЗ мы ищем объем продаж определенного менеджера. На картинке я выделила фрагмент формулы с использованием функции ПОИСКПОЗ (она ищет положение строки c продавцом Авоян):
Привет. Бывали ли у вас такие ситуации, когда формулу ввели, а результат не соответствует ожиданиям? Можно внимательно прочитать формулу, еще раз попробовать ее написать. Но если это что-то длинное, с кучей ЕСЛИ, ссылками на другие ячейки или даже листы - так просто ее не отладишь!
Расскажу о нескольких инструментах Эксель для отладки формул!
1. Мастер функций
Мастер функций вызывается нажатиме на значек fx слева от строки ввода формулы.
Его помощь заключается в следующем:
- подсказка по текущей функции (что делает, какие аргументы)
- разбивка ввода по аргументам
- отображение значения для каждого аргумента
- отображение результата формулы
- возможность вызова справки по этой формуле.
2. Где формулы, а где значения?
Чтобы отобразить формулы в ячейках, нажмите сочетание клавиш CTRL+` (маленький значок — это значок тупого ударения). В таком виде лист можно распечатать, и при печати будут видны формулы. Чтобы вернуться к отображению результатов в ячейках, снова нажмите CTRL+`.
Команду можно также вызвать из ленты Формулы -> Отобразить формулы (Formulas -> Show Formulas)
3. Влияющие и зависимые ячейки
Влияющие и зависимые ячейки. Листайте вправо для скриншота русского варианта --> Влияющие и зависимые ячейки. Листайте вправо для скриншота русского варианта -->При однократном нажатии на кнопку Формулы -> Влияющие ячейки (Formulas -> Trace Precedents) стрелкой будут обозначены ячейки, от которых зависит формула. При повторном - аналогично будут обозначены ячейки от которых зависят те, от которых зависит наша формула. И так далее: вот дом, который построил Джек!
Аналогично работает и функция Зависимые ячейки (Trace Dependends).
4. Пошаговое вычисление формулы
Один из самых эффективных способов - пошаговое вычисление формулы. Этот способ позволяет пошагово "разобрать" длинную формулу на кусочки, и понять где происходит ошибка. Вызвать эту функцию можно в Формулы -> Вычислить формулу (Formulas -> Evaluate Formula)
В этой статье обсуждается отладка только для настраиваемой функции, которая не использует области задач или другие элементы пользовательского интерфейса (пользовательские функции без пользовательского интерфейса).
Обратите внимание, что настраиваемые функции доступны в Excel на следующих платформах.
- Office для Windows
- Office для Mac
- Office в Интернете
Сейчас настраиваемые функции Excel не поддерживаются на iPad или в версиях Office 2019 (или более ранних), предлагаемых в виде единовременных покупок.
Если у вас нет конкретной причины для использования функции, не используемой для пользовательского интерфейса, рекомендуется использовать настраиваемые функции с общим временем работы. Обратите внимание, что использование общего времени работы означает, что ваша надстройка будет использовать Microsoft Edge с WebView2 (Chromium основе), если условия выполнены, а в противном случае надстройка будет использовать Internet Explorer 11 независимо от Windows или Microsoft 365 версии. Описание условий WebView2 см. в браузерах, используемых Office надстройки. Дополнительные сведения о runtimes см. в дополнительных сведениях о runtimes.
Для простоты в этой статье показана отладка в контексте использования Visual Studio Code для редактирования, выполнения задач, а в некоторых случаях — представления отладки. При использовании другого средства редактора или командной строки см. инструкции по командной строке в конце этой статьи.
Требования
Этот процесс отладки работает только для пользовательских функций без пользовательского интерфейса, которые не используют области задач или другие элементы пользовательского интерфейса. Настраиваемая функция без пользовательского интерфейса может быть создана, следуя шагам в руководстве Create custom functions in Excel, а затем удалив все элементы области задач и пользовательского интерфейса, установленные генератором Yeomanдля надстроек Office.
Обратите внимание, что этот процесс отладки не совместим с пользовательскими проектами функций с помощью общего времени запуска.
Использование отладки VS Code для Excel Desktop
Вы можете использовать VS Code для отлаговки пользовательских функций без пользовательского интерфейса в Office Excel на рабочем столе.
Отладка рабочего стола для Mac недоступна, но может быть достигнута с помощью средств браузера и командной строки для отладки Excel в Интернете).
Запустите надстройку из VS Code
- Откройте настраиваемую папку корневого проекта функций в VS Code.
- Выберите терминал > выполнить задачу и введите или выберите Часы. Это позволит отслеживать и восстанавливать любые изменения файлов.
- Выберите терминальный > выполнить задачу и введите или выберите Сервер разработчиков.
Запуск отладки VS Code
- Выберите просмотр > выполнить или ввести Ctrl+Shift+D, чтобы перейти на отлагивание представления.
- Из выпадаемого меню Run выберите Excel Desktop (Настраиваемые функции).
- Чтобы начать отладку, выберите F5(или > запустить отладку из меню). Новая книга Excel откроется с помощью вашей надстройки, уже загруженной и готовой к использованию.
Начало отладки
- В VS Code откройте исходный файл скрипта кода (functions.jsили functions.ts). пользовательском коде источника функции.
- В Excel введите формулу, использующую настраиваемую функцию.
На этом этапе выполнение остановится на строке кода, где заданной точкой разрыва. Теперь вы можете ступить через код, установить часы и использовать все необходимые VS Code отладки.
Используйте отлад VS Code для Excel в Microsoft Edge
Вы можете использовать VS Code для отлаговки пользовательских функций в Excel браузере Microsoft Edge пользовательского интерфейса. Чтобы использовать VS Code с Microsoft Edge, необходимо установить debugger для Microsoft Edge расширения.
Запустите надстройку из VS Code
- Откройте настраиваемую папку корневого проекта функций в VS Code.
- Выберите терминал > выполнить задачу и введите или выберите Часы. Это позволит отслеживать и восстанавливать любые изменения файлов.
- Выберите терминальный > выполнить задачу и введите или выберите Сервер разработчиков.
Запуск отладки VS Code
- Выберите просмотр > выполнить или ввести Ctrl+Shift+D, чтобы перейти на отлагивание представления.
- Из параметров отлагивание выберите Office Online (Edge Chromium).
- Откройте Excel в Microsoft Edge браузере и создайте новую книгу.
- Выберите Share в ленте и скопируйте ссылку на URL-адрес этой новой книги.
- Чтобы начать отладку, выберите F5 (> запустить отладку из меню). Появится запрос, в котором будет указан URL-адрес документа.
- Введите URL-адрес книги и нажмите кнопку Ввод.
Загрузка неопубликованной надстройки
Выберите вкладку Insert на ленте и в разделе Надстройки, выберите Office надстройки.
В диалоговом Office надстройки выберите вкладку MY ADD-INS, выберите Управление надстройками, а затем Upload надстройки.
Просмотрите файл манифеста надстройки и выберите Upload.
Настройка точек разрыва
- В VS Code откройте исходный файл скрипта кода (functions.jsили functions.ts). пользовательском коде источника функции.
- В Excel введите формулу, использующую настраиваемую функцию.
Используйте средства разработчика браузера для отлаговки настраиваемой функции в Excel в Интернете
Вы можете использовать средства разработчика браузера для отлаговки пользовательских функций без пользовательского интерфейса в Excel в Интернете. Следующие действия работают как для Windows, так и для macOS.
Запустите надстройку из Visual Studio Code
- Откройте настраиваемую папку корневого проекта функций в Visual Studio Code (VS Code).
- Выберите терминал > выполнить задачу и введите или выберите Часы. Это позволит отслеживать и восстанавливать любые изменения файлов.
- Выберите терминальный > выполнить задачу и введите или выберите Сервер разработчиков.
Загрузка неопубликованной надстройки
Откройте новую книгу Excel.
Откройте вкладку Insert на ленте и в разделе Надстройки выберите Office надстройки.
В диалоговом Office надстройки выберите вкладку MY ADD-INS, выберите Управление надстройками, а затем Upload надстройки.
После загрузки в документ он будет оставаться в стороне при каждом открываемом документе.
Начало отладки
- Откройте средства разработчика в браузере. Для Chrome и большинства браузеров F12 откроет средства разработчика.
- В средствах разработчика откройте исходный файл скрипта кода с помощью Cmd+P или Ctrl+P(functions.jsили functions.ts). пользовательском коде источника функции.
Если вам нужно изменить код, вы можете внести изменения в VS Code и сохранить изменения. Обновите браузер, чтобы увидеть загруженные изменения.
Отламывка с помощью средств командной строки
Если вы не используете VS Code, для запуска надстройки можно использовать командную строку (например, bash или PowerShell). Для отлаговки кода в Excel в Интернете необходимо использовать средства разработчика браузера. Отламывка настольной версии Excel с помощью командной строки.
Из командной строки запустите, чтобы следить за изменениями кода и восстанавливать npm run watch их.
Откройте второе окно командной строки (первое будет заблокировано во время запуска часов.)
Если вы хотите запустить надстройку в настольной версии Excel, запустите следующую команду.
npm run start:desktop
Или если вы предпочитаете запустить надстройку в Excel в Интернете выполнить следующую команду.
npm run start:web
Для Excel в Интернете необходимо также побокзагрузить надстройку. Выполните действия в Sideload надстройки, чтобы побокзагрузить надстройку. Затем продолжайте отладку в следующем разделе.
Откройте средства разработчика в браузере. Для Chrome и большинства браузеров F12 откроет средства разработчика.
В средствах разработчика откройте исходный файл скрипта кода (functions.js или functions.ts). Пользовательский код функций может быть расположен в конце файла.
В пользовательском коде источника функции нанесите точку разрыва, выбрав строку кода.
Если вам нужно изменить код, вы можете внести изменения в Visual Studio и сохранить изменения. Обновите браузер, чтобы увидеть загруженные изменения.
Команды для создания и запуска надстройки
Существует несколько задач сборки.
- npm run watch : сборки для разработки и автоматическое восстановление при сэкономлении исходных файлов
- npm run build-dev : сборки для разработки один раз
- npm run build : сборки для производства
- npm run dev-server : запускает веб-сервер, используемый для разработки
Вы можете использовать следующие задачи для начала отладки на рабочем столе или в Интернете.
Если вы еще не 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. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
<c0>Предельное количество уровней вложенности функций</c0>. В формулах можно использовать до семи уровней вложенных функций. Если функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, в приведенном выше примере функции СРЗНАЧ и СУММ являются функциями второго уровня, поскольку обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня, и т. д.
Использование ссылок в формулах
Ссылка указывает на ячейку или диапазон ячеек на сайте и сообщает 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
<c0>Ссылка на другой лист</c0>. В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения диапазона B1:B10 на листе «Маркетинг» той же книги.
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Различия между абсолютными, относительными и смешанными ссылками
<c0>Относительные ссылки</c0>. Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2.
<c0>Абсолютные ссылки</c0>. Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.
Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.
Стиль трехмерных ссылок
Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.
При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.
<c0>Что происходит при перемещении, копировании, вставке или удалении листов</c0>. Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.
Вставка или копирование Если вставить листы между листами 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 в Интернете таблиц. Имя — это значимое краткое обозначение, поясняющее предназначение ссылки на ячейку, константы, формулы или таблицы, так как понять их суть с первого взгляда бывает непросто. Ниже приведены примеры имен и показано, как их использование упрощает понимание формул.
Читайте также: