как узнать есть ли ссылка на ячейку в excel

Внешние ссылки Excel | Как найти и удалить внешние ссылки в Excel?

Что такое внешние ссылки в Excel?

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

Если наша формула выглядит так, как показано ниже, то это внешняя ссылка.

‘C: \ Users \ Admin_2.Dell-PC \ Desktop \: это путь к этому листу на компьютере.

[Внешний лист.xlsx]: это имя книги в этом пути.

Лист Vlookup: это имя рабочего листа в этой книге.

Типы внешних ссылок в Excel

# 1- Ссылки на одном листе

Эти типы ссылок находятся на одном листе. В рабочей тетради много листов. Этот тип ссылок указывает только имя ячейки.

Например: если вы находитесь в ячейке B2 и если в строке формул отображается A1, это означает, что все, что происходит в ячейке A1, будет отражено в ячейке B2.

Хорошо, это всего лишь простая ссылка на том же листе.

Эти типы ссылок находятся в одной книге, но с разных листов.

Например, в книге есть два листа, и прямо сейчас я нахожусь на листе 1 и даю ссылку с листа 2.

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

Например, если я даю ссылку из другой книги под названием «Книга1», то сначала будет показано имя книги, имя листа, а затем имя ячейки.

Как найти, отредактировать и удалить внешние ссылки в Excel?

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

Хорошо, позвольте мне объяснить методы поиска внешних ссылок в Excel.

Метод №1: Использование метода поиска и замены с символом оператора

Шаг 1: Выберите лист, нажмите Ctrl + F (ярлык для поиска внешних ссылок).

Шаг 2: Введите символ [ и нажмите «Найти все».

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

Примечание. Если ваши данные содержат символ [, то он также будет преобразован в значения.

Метод № 2: Использование метода поиска и замены с расширением файла

Ячейка с внешними ссылками включает имя книги, т.е. имя книги и тип книги.

Шаг 1: Выберите лист, нажмите Ctrl + F (ярлык для поиска внешних ссылок).

Это покажет все ячейки внешних ссылок.

Метод № 3: Использование опции редактирования ссылки в Excel

Это самый прямой вариант, который есть в Excel. Он будет выделять только внешнюю ссылку, в отличие от методов 1 и 2. В этом методе мы можем редактировать ссылку в Excel, разрывать или удалять и удалять внешние ссылки.

Шаг 1. Выберите ячейки, которые вы хотите изменить, разорвать или удалить ячейки ссылки.

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

Источник

Поиск данных в таблице или диапазоне ячеек с помощью встроенных функций Excel

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

Описание

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

Создание образца листа

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

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

Определения терминов

В этой статье для описания встроенных функций Excel используются указанные ниже условия.

Вся таблица подстановки

Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

Просматриваемый_массив
-или-
Лукуп_вектор

Диапазон ячеек, которые содержат возможные значения подстановки.

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

3 (третий столбец в инфо_таблица)

Ресулт_аррай
-или-
Ресулт_вектор

Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.

Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.

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

Функции

LOOKUP ()

Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.

Ниже приведен пример синтаксиса формулы подСТАНОВКи.

= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

Следующая формула находит возраст Марии на листе «образец».

= ПРОСМОТР (E2; A2: A5; C2: C5)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

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

Ниже приведен пример синтаксиса формулы ВПР :

= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)

Следующая формула находит возраст Марии на листе «образец».

= ВПР (E2; A2: C5; 3; ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

INDEX () и MATCH ()

Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.

Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:

= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

Следующая формула находит возраст Марии на листе «образец».

= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

СМЕЩ () и MATCH ()

Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.

= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

Эта формула находит возраст Марии на листе «образец».

= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

Источник

Поиск ссылок (внешних ссылок) в книге

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

Поиск ссылок, используемых в формулах

Нажмите CTRL+F, чтобы запустить диалоговое окно Найти и заменить.

Нажмите кнопку Параметры.

В поле В пределах выберите книга.

В поле Искать в выберите формулы.

Нажмите кнопку Найти все.

В отображемом списке наймем в столбце Формула формул, содержащих XL. В этом случае Excel найдено несколько экземпляров функции бюджетного Master.xlsx.

Чтобы выбрать ячейку с внешней ссылкой, щелкните ссылку на эту строку в списке.

Совет: Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.

На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.

Проверьте каждую запись в списке и проверьте, нет ли в столбце Ссылка внешних ссылок. Внешние ссылки содержат ссылку на другую книгу, например [Budget.xlsx].

Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.

Если вы хотите удалить сразу несколько элементов, можно сгруппнуть несколько элементов, нажав клавишу SHIFT или CTRL и щелкнув левой кнопкой мыши.

Нажмите клавиши CTRL+G, нажмите клавиши CTRL+G, чтобы перейти в диалоговое окно Перейти, а затем выберите специальные > объекты > ОК. При этом будут выбраны все объекты на активном сайте.

«Специальная»» loading=»lazy»>

Нажимая клавишу TAB, переходить между выбранными объектами, а затем искать в строка формул ссылку на другую книгу, например [Budget.xlsx].

Щелкните название диаграммы, которую вы хотите проверить.

В строка формул наймем ссылку на другую книгу, например [Budget.xls].

Выберите диаграмму, которую нужно проверить.

На вкладке Макет в группе Текущий выделение щелкните стрелку рядом с полем Элементы диаграммы и выберите ряд данных, которые нужно проверить.

формат > текущий выделение» loading=»lazy»>

На строка формул , наймем ссылку на другую книгу, например [Budget.xls] в функции РЯД.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Как узнать есть ли ссылка на ячейку в excel

Как узнать, есть ли ссылка на ячейку в другой ячейке Excel?

Как мы можем узнать, есть ли ссылка на ячейку в каких-либо формулах или нет? Фактически, в Excel есть встроенная функция, называемая «Отслеживание зависимостей», которая может помочь вам легко узнать, есть ли ссылка на ячейку в каких-либо формулах.

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

1. Выделите ячейку, в которой вы хотите узнать, ссылаются ли на нее какие-либо формулы.

2. Затем нажмите Формулы > Следить за зависимыми. Смотрите скриншот:

3. После активации Следить за зависимыми функции, вы можете видеть, что на листе отображаются стрелки.

Как показано на снимке экрана ниже, эти две стрелки указывают на то, что на активную ячейку A2 есть ссылки в двух формулах, которые находятся в ячейке B2 и ячейке B5.

Заметки:

1. Если формулы, относящиеся к активной ячейке, отсутствуют, появится диалоговое окно с запросом, как показано ниже.

2. Вы можете удалить стрелки, нажав Формулы > Удалить стрелки > Удалить зависимые стрелки.

Источник

Относительные и абсолютные ссылки – как создать и изменить

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

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

Что такое ссылка на ячейку?

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

Ссылка на ячейку представляет собой комбинацию из буквы столбца и номера строки, который идентифицирует её на листе. Проще говоря, это ее адрес. Он сообщает программе, где искать значение, которое вы хотите использовать в расчётах.

Например, A1 относится к адресу на пересечении столбца A и строки 1; B2 относится ко второй ячейке в столбце B и так далее.

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

Например, если вы введете простейшее выражение =A1 в клетку C1, Эксель продублирует данные из A1 в C1:

Чтобы сложить числа в ячейках A1 и A2, используйте: =A1 + A2

Что такое ссылка на диапазон?

В Microsoft Excel диапазон – это блок из двух или более ячеек. Ссылка на диапазонпредставлена адресами верхней левой и нижней правой его ячеек, разделенных двоеточием.

Например, диапазон A1:C2 включает 6 ячеек от A1 до C2.

Как создать ссылку?

Чтобы записать ссылку на ячейку на том же листе, вам нужно сделать следующее:

Например, чтобы сложить значения в A1 и A2, введите знак равенства, щелкните A1, введите знак плюса, щелкните A2 и нажмите Enter:

Чтобы создать ссылку на диапазон, выберите область на рабочем листе.

Например, чтобы сложить значения в A1, A2 и A3, введите знак равенства, затем имя функции СУММ и открывающую скобку, выберите ячейки от A1 до A3, введите закрывающую скобку и нажмите Enter:

Чтобы обратиться ко всей строке или целому столбцу, щелкните номер строки или букву столбца соответственно.

Например, чтобы сложить все ячейки в строке 1, начните вводить функцию СУММ, а затем кликните заголовок первой строки, чтобы включить ссылку на строку в ваш расчёт:

Как изменить ссылку?

Чтобы изменить адрес ячейки в существующей формуле Excel, выполните следующие действия:

Как сделать перекрестную ссылку?

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

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

Например, вот как вы можете создать ссылку на адрес A1 на листе Лист2 в той же книге Excel:

Если имя рабочего листа содержит пробелы или неалфавитные символы, вы должны заключить его в одинарные кавычки, например:

Чтобы предотвратить возможные опечатки и ошибки, вы можете заставить Excel автоматически создавать для вас внешнюю ссылку. Вот как:

Как сослаться на другую книгу?

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

Если имя файла или листа содержит небуквенные символы, не забудьте заключить путь в одинарные кавычки, например

Как и в случае ссылки на другой лист, вам не обязательно вводить всё это вручную. Более быстрый способ – начать писать формулу, затем переключиться на другую книгу и выбрать в ней ячейку или диапазон. Нажать Enter.

Итак, мы научились создавать простейшие ссылки. Теперь рассмотрим, какими они бывают.

В Экселе есть три типа ссылок на ячейки: относительные, абсолютные и смешанные. В ваших расчётах вы можете использовать любой из них. Но если вы собираетесь скопировать записанное выражение на другое место в вашем рабочем листе, то здесь нужно быть внимательным. Важно использовать правильный тип адреса, поскольку относительные и абсолютные ссылки ведут себя по-разному при переносе и копировании.

Относительная ссылка на ячейку.

Относительная ссылка является самой простой и включает координаты строки и столбца, например А1 или А1:D10. По умолчанию все адреса ячеек в Экселе являются относительными.

Это простейшее выражение сообщает программе, что нужно показать значение, которое записано в первой колонке (A) и второй строке (2). Используя скриншот чуть ниже, если бы эта формула была помещена в ячейку D1, она отобразила бы число «8», поскольку это значение находится по адресу A2.

При перемещении или копировании относительные ссылки изменяются в зависимости от относительного положения строк и столбцов. Иначе говоря, насколько новое местоположение изменилось относительно первоначального.

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

Например, чтобы сложить числа в A2 и B2, вы вводите это в C2: =A2+B2. При копировании из строки 2 в строку 3 выражение изменится на = A3+B3.

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

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

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

Вместо того, чтобы вводить формулу для всех ячеек одну за другой, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3: D8). Когда вы это сделаете, вы заметите, что адрес автоматически настраивается, чтобы ссылаться на соответствующую строку. Например, формула в ячейке D3 становится B3*C3, а в D4 теперь записано: B4*C4.

Абсолютная ссылка на ячейку.

Символ доллара, добавленный перед любой из координат, делает адрес абсолютным (т. е. предотвращает изменение номера строки и столбца).

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

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

Например, чтобы умножить числа в столбце B на величину скидки из F2, вы вводите следующую формулу в строке 2, а затем копируете её вниз, перетаскивая маркер заполнения:

Относительная ссылка (B2) будет изменяться в зависимости от относительного положения строки, в которую она копируется, в то время как абсолютная ($F$2) всегда будет зафиксирована на одном и том же адресе:

Конечно, можно в ваше выражение жёстко вбить 10% скидки, и этим решить проблему при копировании. Но если впоследствии вам понадобится изменить процент скидки, то придется искать и корректировать все формулы. И обязательно какую-то случайно пропустите. Поэтому принято подобные константы записывать отдельно и использовать абсолютные ссылки на них.

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

Абсолютные ссылки всегда указывают на конкретный адрес, независимо от того, где они находятся.

Смешанная ссылка.

Смешанные ссылки немного сложнее, чем абсолютные и относительные.

Может быть два типа смешанных ссылок:

Как вы помните, абсолютная ссылка содержит 2 знака доллара ($), которые фиксируют как столбец, так и строку. В смешанной только одна координата является фиксированной (абсолютной), а другая (относительная) будет изменяться в зависимости от нового расположения:

Может быть много ситуаций, когда нужно фиксировать только одну координату: либо столбец, либо строку.

Например, чтобы умножить колонку с ценами (столбец В) на 3 разных значения наценки (C2, D2 и E2), вы поместите следующую формулу в C3, а затем скопируете ее вправо и затем вниз:

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

А вот во втором множителе знак доллара мы поставили перед номером строки. Поэтому при копировании формулы в D3 координаты столбца изменятся и вместо C$2 мы получим D$2. В результате в D3 получим:

Самый приятный момент заключается в том, что формулу мы записываем только один раз, а потом просто копируем ее на всю таблицу. Экономим очень много времени.

И если ваши наценки вдруг изменятся, просто поменяйте числа в C2:E2, и проблема будет решена почти мгновенно.

Как изменить ссылку с относительной на абсолютную (или смешанную)?

Примечание. Если вы нажмете F4, не выбрав ничего конкретного, ячейка слева от указателя мыши будет выбрана автоматически и там будет изменён тип ссылки.

Имя как разновидность абсолютной ссылки.

Отдельную ячейку или диапазон также можно определить по имени. Для этого вы просто выбираете ячейку, вводите имя в поле Имя и нажимаете клавишу Enter.

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

Его вы можете использовать в вычислениях вашей рабочей книги.

Естественно, это своего рода абсолютная ссылка, поскольку за каждым именем жёстко закрепляются координаты определенной ячейки или диапазона.

Формула же при этом становится более понятной и читаемой.

Ссылка на столбец.

Как и на отдельные ячейки, ссылка на весь столбец может быть абсолютной и относительной, например:

Когда вы используете знак доллара ($) в абсолютной ссылке на столбец, его адрес не изменится при копировании в другое расположение.

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

А теперь давайте посмотрим это на примере.

Предположим, у вас есть некоторые числа в колонке B, и вы хотите узнать их общее и среднее значение. Проблема в том, что новые данные добавляются в таблицу каждую неделю, поэтому писать обычную формулу СУММ() или СРЗНАЧ() для фиксированного диапазона ячеек – не лучший вариант. Вместо этого вы можете ссылаться на весь столбец B:

=СУММ($D:$D)— используйте знак доллара ($), чтобы создать абсолютную ссылку на весь столбец, которая привязывает формулу к столбцу B.

Примечание. При использовании ссылки на весь столбец никогда не вводите формулу в том же столбце, на который ссылаетесь. Например, может показаться хорошей идеей ввести =СУММ(D:D) в одну из самых нижних пустых ячеек в этом же столбце D, чтобы получить итоговый результат в конце таблицы. Не делайте этого! Это создаст так называемую циклическую ссылку, и вы получите результат 0.

Ссылка на строку.

Чтобы обратиться сразу ко всей строке, вы используете тот же подход, что и со столбцами, за исключением того, что вы вводите номера строчек вместо букв столбиков:

Пример 2. Ссылка на всю строку (абсолютная и относительная)

Если данные в вашем листе расположены горизонтально, а не по вертикали, вы можете ссылаться на всю строку. Например, вот как мы можем рассчитать среднюю цену в строке 2:

=СРЗНАЧ($3:$3) – абсолютная ссылка на всю строку зафиксирована с помощью знака доллара ($).

=СРЗНАЧ(3:3) – относительная ссылка на строку изменится при копировании вниз.

В этом примере нам нужна относительная ссылка. Ведь у нас есть 6 строчек с данными, и мы хотим вычислить среднее значение для каждого товара отдельно. Записываем в B12 расчет средней цены для яблок и копируем его вниз:

Для бананов (B13) расчет уже будет такой: СРЗНАЧ(4:4). Как видите, номер строки автоматически изменился.

Ссылка на столбец, исключая первые несколько строк.

Это очень актуальная проблема, потому что довольно часто первые несколько строк на листе содержат некоторые вводные предложения, шапку даблицы или пояснительную информацию, и вы не хотите включать их в свои вычисления. К сожалению, Excel не допускает ссылок типа D3:D, которые включали бы все данные в столбце D, только начиная со строки 3. Если вы попытаетесь добавить такую ​​конструкцию, ваша формула, скорее всего, вернет ошибку #ИМЯ?.

Вместо этого вы можете указать максимальную строку, чтобы ваша ссылка включала все возможные адреса в данном столбце. В Excel с 2019 по 2007 максимум составляет 1 048 576 строк и 16 384 столбца. Более ранние версии программы имеют максимум 65 536 строк и 256 столбцов.

Итак, чтобы найти сумму продаж в приведенной ниже таблице (колонка «Стоимость»), можно использовать выражение:

Как вариант, можно вычесть из общей суммы те данные, которые хотите исключить:

Но первый вариант предпочтительнее, так как СУММ(D:D) выполняется дольше и требует больше вычислительных ресурсов, чем СУММ(D3:D1048576).

Смешанная ссылка на весь столбец.

Как я упоминал ранее, вы также можете создать смешанную ссылку на весь столбец или целую строку:

В результате Эксель сложит все числа в столбцах B и C. Ну и, двигаясь далее вправо, далее можно найти сумму уже трёх колонок.

Предупреждение! Не используйте на листе слишком много ссылок на целые столбцы или строки, поскольку так вы можете существенно замедлить работу Excel.

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге!

Источник