Разновидности поиска
Поиск совпадений
Иногда бывает необходимо обнаружить в таблице повторяющиеся значения. Чтобы произвести поиск совпадений, сначала нужно выделить диапазон поиска. Затем, на той же вкладке «Главная» в группе «Стили», открыть инструмент «Условное форматирование». Далее последовательно выбрать пункты «Правила выделения ячеек» и «Повторяющиеся значения».
Результат представлен на скриншоте ниже.
При необходимости пользователь может поменять цвет визуального отображения совпавших ячеек.
Фильтрация
Другая разновидность поиска – фильтрация. Предположим, что пользователь хочет в столбце B найти числовые значения в диапазоне от 3000 до 4000.
- Выделить первый столбец с заголовком.
- На той же вкладке «Главная» в разделе «Редактирование» открыть инструмент «Сортировка и фильтр», и щёлкнуть пункт «Фильтр».
- В верхней строчке столбца B появляется треугольник – условный знак списка. После его открытия в списке «Числовые фильтры» щёлкнуть пункт «между».
- В окне «Пользовательский автофильтр» следует ввести начальное и конечное значение плюс OK.
Как видно, отображаться стали только строки, удовлетворяющие введённому условию. Все остальные оказались временно скрытыми. Для возврата к начальному состоянию следует повторить шаг 2.
Различные варианты поиска были рассмотрены на примере Excel 2010. Как сделать поиск в эксель других версий? Разница в переходе к фильтрации есть в версии 2003. В меню «Данные» следует последовательно выбрать команды «Фильтр», «Автофильтр», «Условие» и «Пользовательский автофильтр».
Внешние ссылки в Excel
Думаю, каждый из Вас знает, что внешняя ссылка в Эксель – это ссылка на ячейку, находящуюся в другой книге. И самый распространенный способ собрать данные из разных источников в одну – как раз внешние ссылки.
Если итоговая таблица достаточно простая, этот способ можно использовать, иначе – легко запутаться, да и утомительно вручную внести несколько сотен ссылок на ячейки, высока вероятность ошибки. Тем не менее, для маленьких таблиц это действительно решение.
Существует много способов сослаться на ячейку с другого листа, или книги. Я расскажу Вам самый простой:
- Откройте все файлы – свод и исходники
- Кликните по ячейке, в которую нужно вставить ссылку, и введите знак равно (как вы это делаете, когда создаете формулы).
- Выберите файл с исходным значением и кликните на этом значении. В строке формул появится ссылка на это значение.
- Если к этому значению нужно прибавить другие из остальных источников – допишите «+» и повторяйте п.3-4 до полного заполнения.
- Когда все ссылки на ячейки сделаны, нажмите Enter , будет просчитан сводный результат.
Внешняя ссылка имеет специфический внешний вид: имя файла в квадратных скобках, имя листа, восклицательный знак, координаты ячейки:
Когда Вы закроете исходные файлы, к ссылке слева добавится еще и адрес файла-исходника:
По умолчанию, все внешние ссылки абсолютные, т.е. при копировании не изменяются. Если нужно, сделайте их относительными, нажимая F4 до получения необходимого типа ссылки.
Не перемещайте файлы-исходники, т.к. это разорвет связи, и внешние ссылки работать больше не будут.
Если на одном компьютере открыт и сводный документ и исходник, изменения в исходной таблице сразу же «подтянутся» и в свод. Если хотя бы одна из этих книг закрыта – понадобится принудительное обновление. Так же, при повторном открытии сводного файла, Эксель предложит обновить данные. Сделайте это, если нужно актуализировать информацию.
Управление внешними ссылками
Чтобы управлять ссылками на источники в других книгах, нажмите на ленте Данные – Подключения – Изменить связи . В открывшемся окне отобразится весь список подключенных файлов и кнопки-команды:
- Обновить – принудительное обновление всех ссылок
- Изменить – указать новую книгу Excel, в которой располагаются подключенные данные
- Открыть – открывает документ-источник
- Разорвать связь – удаляет внешние ссылки из этой связи, заменяет их на значение
- Состояние – обновляет статус доступности файлов с данными
- Запрос на обновление связей – укажите программе, нужно ли обновлять данные при открытии файла
Если у Вас большой файл с несколькими листами, может понадобиться много времени на поиски внешних ссылок. Так как быстро найти внешние ссылки в документе Excel? Предложу рецепт. Все подобные ссылки имеют общий элемент – открывающая и закрывающая квадратные скобки, в которые записано имя файла-исходника. В других случаях такие скобки используются редко. Нажмите Ctrl+F для открытия окна поиска, в поле «Найти» введите квадратную скобку и нажмите «Найти все». В результатах поиска точно будут все внешние ссылки.
Применение внешних ссылок – самый простой и очевидный способ собрать данные из нескольких таблиц в одну. Но если данных достаточно много, этот метод неприменим. Тогда пользуемся инструментом «Консолидация».
4 Функция СОВПАД
Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:
- В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
- Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
- Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.
Поиск связей (внешних ссылок) в книге
каждом копировании данныхПоскольку книгу надо ячейками, листами: У меня в поэтому и не строке “Доход: маржа”значение 8, чтобыи затем вернуть текст том случае, если и не запускается случае используйте информацию окно. создавать ссылки на
Поиск ссылок, используемых в формулах
-
( т е папка из другой книги всё-таки посмотреть, можноЧасть имён может Вашей книге после
-
разместил. (ячейка, в которой поиск не выполнялся
-
ПОИСКБ с помощью функций связь является действительной. для вычисляемых полей, из этой статьи
-
ПереходНажмите кнопку другие книги. Однако в ячейке неЗаходим в эту
-
немедленно проверять появление сделать так: быть скрыта посредством удаления имён связиЯ думал что
-
выполняется поиск — A3). в той частине учитывают регистр.
-
ПСТРЕсли алгоритм автоматического обнаружения которые используются в для устранения ошибок, нажмите кнопкуНайти все иногда вы можете формула или значение папку не раззиповывая связей (в этот1) Ищете связи VBA
-
уходят все знаю где8 текста, которая является Если требуется учитывать
и предлагает связь, которая метках строк и автоматического обнаружения связей. ЧтобыВыделить
Поиск ссылок, используемых в определенных именах
-
. не найти ссылки а ссылка C: и ищем везде момент от них по ячейкам самостоятельноВозможны также скрытые
-
Возможно, Вы сейчас в нем и=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;”объем”) серийным номером (в регистр, используйте функцииПСТРБ не решает бизнес-задачи, столбцов сводной таблицы.
лучше понять требования, установите переключатель
-
В появившемся поле со в книге, хотя и тд ) папку с названием
-
избавиться очень просто)2) Для всех или сильно скрытые пишете о другой что находится, ноЗаменяет слово “маржа” словом данном случае —
-
Поиск ссылок, используемых в объектах, таких как текстовые поля или фигуры
-
НАЙТИили заменить его то необходимо удалить Поэтому перед началом и механизмы обнаруженияобъекты списком найдите в Excel сообщает, чтоКак это автоматически/быстро “externalLinks”, затем удаляемserjo1 листов Вы очищаете листы (все они книге?
-
видимо ошибался. Файл “объем”, определяя позицию “МДС0093”). Функцияи с помощью функций ее и создать построения сводной таблицы связей, см. раздели нажмите кнопку
Поиск ссылок, используемых в заголовках диаграмм
-
столбце они имеются. Автоматический сделать?
-
её и снова: Доброго всем время формулы и значения видны в окне
Поиск ссылок, используемых в рядах данных диаграммы
-
Тогда её надо создан полгода назад
-
слова “маржа” вПОИСКНАЙТИБЗАМЕНИТЬ вручную с использованием несвязанные таблицы можно Связи между таблицамиОКФормула поиск всех внешних
-
Спасибо! меняем расширение на суток! всех ячеек проекта VBA)
support.office.com>
Как найти внешние ссылки и ссылки в Excel
Вот несколько приемов, которые вы можете использовать для быстрого поиска внешних ссылок в Excel:
- Использование поиска и замены.
- Использование опции редактирования ссылок.
Давайте посмотрим, как работает каждый из этих методов.
Найдите внешние ссылки с помощью функции «Найти и заменить»
Ячейки с внешними ссылками содержат имя книги, на которую они ссылаются. Это будет означать, что ссылка будет иметь имя файла с расширением .xlsx / .xls / .xlsm / .xlb.
Мы можем использовать это, чтобы найти все внешние ссылки.
Вот шаги, чтобы найти внешние ссылки в Excel с помощью функции «Найти и заменить»:
- Выделите все ячейки.
- Перейдите на вкладку «Главная» -> «Редактирование» -> «Найти и выделить» -> «Найти».
- В диалоговом окне «Найти и заменить» введите * .xl * в поле «Найти».
- Нажмите «Найти все».
Это найдет и покажет все ячейки, в которых есть внешние ссылки.
Теперь вы можете выделить все эти ячейки (выбрать первую запись, удерживать клавишу Shift, а затем выбрать последнюю запись) и преобразовать формулы в значения.
Найдите внешние ссылки с помощью опции редактирования ссылок
В Excel есть встроенный инструмент, который найдет все внешние ссылки.
Вот шаги, чтобы найти внешние ссылки с помощью опции редактирования ссылок:
- Перейдите на вкладку «Данные».
- В группе Connections нажмите Edit Links. Откроется диалоговое окно «Редактировать ссылки», в котором будут перечислены все книги, на которые есть ссылки.
- Нажмите «Разорвать связи», чтобы преобразовать все связанные ячейки в значения.
Имейте в виду, что как только вы разорвете ссылки, вы можете отменить это. Перед этим рекомендуется создать резервную копию.
По-прежнему появляется запрос о внешних ссылках?
Иногда вы можете найти и удалить все внешние ссылки, но все равно получите подсказку, как показано ниже:
Не сходите с ума и не начинайте ругать Excel.
Поэтому, если вы получаете запрос на ссылку для обновления, также проверьте следующие внешние ссылки:
- Именованные диапазоны
- Условное форматирование
- Проверка достоверности данных
- Формы
- Заголовки диаграмм
Использование «Найти и заменить» или «Изменить ссылки», как показано выше, не приведет к идентификации внешних ссылок в этих вышеупомянутых функциях.
Вот шаги, чтобы найти внешние ссылки в этих местах:
- Именованные диапазоны: Перейдите на вкладку «Формула» и нажмите «Диспетчер имен». Он покажет вам все названные диапазоны в книге. Вы можете проверить столбец «Ссылается на», чтобы найти внешние ссылки.
- Условное форматирование: Единственный способ получить внешнюю ссылку в условном форматировании — это использовать настраиваемую формулу. Перейдите на вкладку «Главная» -> «Условное форматирование» -> «Управление правилами». В диспетчере правил условного форматирования проверьте формулы на наличие внешних ссылок.
- Проверка достоверности данных: Возможно, что раскрывающийся список проверки данных относится к именованному диапазону, который, в свою очередь, имеет внешние ссылки. Проверка именованных диапазонов также должна решить эту проблему.
-
Формы: Если вы используете фигуры, связанные с ячейками, проверьте их на наличие внешних ссылок. Вот быстрый способ пройтись по всем фигурам:
- Нажмите клавишу F5. Откроется диалоговое окно «Перейти к».
- Щелкните Special.
- В диалоговом окне «Перейти к специальному» выберите «Объекты».
- Щелкните ОК. Это выделит все формы. Теперь вы можете использовать клавишу Tab для их просмотра.
- Заголовки диаграмм: Выберите заголовок диаграммы и проверьте в строке формул, ссылается ли он на внешнюю ссылку.
Вы можете узнать больше о внешних ссылках из этих руководств:
- Поиск внешних ссылок в Excel — блог Contextures.
- Поиск внешних ссылок — Поддержка Microsoft Excel.
Также доступна надстройка для поиска внешних ссылок в Excel. Щелкните здесь, чтобы узнать больше и загрузить надстройку.
Поиск ссылок (внешних ссылок) в книге
Связывание с другими книгами — это очень распространенная задача в Excel, но иногда вы можете найти книгу, которая ссылок не может найти, хотя Excel сообщает о том, что они существуют. Нет способа автоматического поиска всех внешних ссылок, используемых в книге, однако существует несколько способов, используемых вручную. Вам нужно искать формулы, определенные имена, объекты (например, текстовые поля или фигуры), названия диаграмм и ряды данных диаграммы.
Поиск ссылок, используемых в формулах
Нажмите CTRL+F, чтобы запустить диалоговое окно Найти и заменить.
Выберите пункт Параметры.
В поле Найти введите .xl.
В поле В пределах выберите книга.
В поле Искать в выберите формулы.
Нажмите кнопку Найти все.
В отображемом списке наймем в столбце Формула формул, содержащих XL. В этом случае Excel обнаружил несколько экземпляров функции бюджетного Master.xlsx.
Чтобы выбрать ячейку с внешней ссылкой, щелкните ссылку на эту строку в списке.
Совет: Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
Проверьте каждую запись в списке и проверьте, нет ли в столбце Ссылка внешних ссылок. Внешние ссылки содержат ссылку на другую книгу, например .
Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
Если вы хотите удалить сразу несколько элементов, можно сгруппнуть несколько элементов, нажав клавишу SHIFT или CTRL и щелкнув левой кнопкой мыши.
Нажмите клавиши CTRL+G, нажмите клавиши CTRL+G, чтобы перейти в диалоговое окно Перейти, а затем выберите специальные > объекты > ОК. При этом будут выбраны все объекты на активном сайте.
«Специальная»» />
Нажимая клавишу TAB, переходить между выбранными объектами, а затем искать в строка формул ссылку на другую книгу, например .
Щелкните название диаграммы, которую вы хотите проверить.
В строка формул наймем ссылку на другую книгу, например .
Выберите диаграмму, которую нужно проверить.
На вкладке Макет в группе Текущий выделение щелкните стрелку рядом с полем Элементы диаграммы и выберите ряд данных, которые нужно проверить.
формат > текущий выделение» />
На строка формул , наймем ссылку на другую книгу, например в функции РЯД.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Найти внешние ссылки
В Excel можно использовать данные из другой книги (файла). В этом случае Excel создает внешнюю ссылку на эту книгу. Рассмотрим набор данных на рисунке ниже, который содержит внешние ссылки на другую книгу.
Как вы можете видеть выше, значение в B2 связано с рабочим листом с именем Внешний файл .xlsx (Лист1, ячейка B2). Ячейки B5, B7 и B8 также содержат похожие ссылки. Теперь посмотрите на этот файл и значение в ячейке B2.
Выше видно, что значение ячейки B2 в файле Внешний файл .xlsx 55, и это значение связано с исходным файлом. Когда вы связываете ячейку с другой книгой, значения обновляются в обеих книгах при каждом изменении связанной ячейки. Таким образом, вы можете столкнуться с проблемой, если файл, на который вы ссылаетесь, будет удален.
При открытии исходный файл предупреждает вас о том, что некоторые ячейки в этой книге связаны с несуществующим файлом. Чтобы решить эту проблему, вы можете найти все ячейки с внешними ссылками и заменить их каким-либо другим значением (или просто удалить их).
Однако часто бывают случаи, когда у вас много связанных ячеек, и на просмотр всего файла, чтобы выбрать их одну за другой, потребуется много времени. Вы можете найти все внешние ссылки в файле сразу, используя Найти и заменить или Редактировать ссылки Особенности.
Как сравнить два столбца в Excel по строкам
Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции ЕСЛИ. Рассмотрим как это работает на примерах ниже.
Пример 1. Как сравнить два столбца на совпадения и различия в одной строке
Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу ЕСЛИ. Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.
Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:
Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:
Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:
или
Пример результата вычислений может выглядеть так:
Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:
Консолидация данных в одну таблицу
Этот инструмент упрощает сведение информации в одну таблицу данных, это удачный способ автоматизации процесса.
Рассмотрим его работу на примере. У нас есть 3 файла с данными о продажах трех подразделений компании. Нужно собрать общие показатели по организации, и сделать это быстро, ведь информация нужна «на вчера».
Давайте выполнять. Открываем все файлы-исходники и свод. Активируем «Свод». Ищем на ленте Данные – Работа с данными – Консолидация . Откроется окно подключения внешних данных.
Теперь пошагово выполняем:
- В поле «Функция» выбираем «Сумма» (или что-то другое, если нужно)
- В поле «Ссылка» указываем ссылку на данные в первом файле-источнике (вместе с шапкой). Лучше всего, указать столбцы полностью, например A:D . Тогда не нужно будет переопределять диапазон при добавлении новых данных.
- Нажимаем «Добавить», ссылка появляется в поле «Список диапазонов»
- Повторяйте п. 2-3, пока не добавите ссылки на все файлы-исходники
- Установите галочки «Подписи верхней строки» и «Значения левого столбца», чтобы Эксель сам подобрал заголовки шапки и категории в строках
- Установите галку «Создавать связи с исходными данными», тогда консолидация будет сделана с помощью ссылок на ячейки. Иначе – будут просто вставлены итоговые значения.
- Нажмите Ок для завершения настройки консолидации.
В итоге, в файле «Свод» появится сведенный список из таблиц всех подключенных файлов. Если установили галочку «Создавать связи с исходными данными», итоговая таблица будет структурирована, можно развернуть каждый пункт и увидеть детали по каждому подразделению отдельно
Если исходные таблицы изменились, можно переформировать свод. Для этого повторно откройте окно «Консолидация» и нажмите Ок.
Такой вот простой инструмент, решающий одну из самых противных задач в Эксель. Я предпочитаю применять именно его, ручная работа по внесению ссылок – не для меня. Рекомендую и Вам пользоваться. Хотя, более удачным решением будет применение сводной таблицы (когда это возможно), или создание собственной VBA-программы с детальными настройками сведения.
На этом оставлю Вас экспериментировать с Консолидацией, и приступаю к написанию поста о защите данных в Эксель. Читайте, эта информация сейчас, как никогда актуальна. До встречи!
Создание гиперссылок
В отличие от ссылок, которые мы описали выше, гиперссылки нужны не только для того, чтобы “вытягивать” данные из других ячеек. Помимо этого, они также позволяют осуществлять переход к тому элементу, на который ссылаются.
- Чтобы создать гиперссылку, нужно перейти к специальному окну, позволяющему его создать. Сделать это можно по-разному:
- отмечаем нужную ячейку, переключаемся во вкладку “Вставка”, где нажимаем на кнопку “Ссылка”.
- либо для вставки гиперссылки можно использовать сочетание клавиш CTRL+K.
- Появится окно, позволяющее настроить гиперссылку. На выбор предлагаются следующие объекты для связки:
- новый документ;
- место в документе;
- электронная почта;
- Давайте попробуем связать ячейку с документом. Для этого в основной части окна открываем папку с требуемым файлом и отмечаем его. В качестве документа может служить файлы как с расширением “xls” (“xlsx”), так и других форматов. После выбора нужного документа щелкаем OK.
- В ситуациях, когда нужно создать ссылку на страницу в Интернете, выбирав тот же самый пункт, пишем в поле “Адрес” ссылку на веб-страницу, после чего жмем OK.
- Когда нужно создать гиперссылку на конкретное место в текущем документе, выбираем пункт “Место в документе”. В основной области окна отмечаем нужный лист и координаты именно той ячейки, связь с которой нужно создать. Как обычно, по завершении щелкаем OK.
- В тех случаях, когда нам нужно создать связь с новым документом Excel, выбираем соответствующий пункт. Затем придумываем имя новой книги, выбираем место для сохранения и щелкаем OK.
- В определенных ситуациях возникает потребность связать какой-то элемент книги с e-mail. Помочь в этом может пункт “Электронная почта”. Здесь в поле “Адрес эл. почты” пишем, соответственно, требуемый e-mail, после чего щелкаем OK.
- Как только мы выбрали тип гиперссылки, заполнили нужные поля и щелкнули OK, в выбранной ячейке появится активная ссылка синего цвета, нажатие на которую осуществит переход к связанному с ней объекту.
Применение функции “ГИПЕРССЫЛКА”
Также, в программе Эксель предусмотрена возможность создания гиперссылки с помощью функции, которая так и называется – “ГИПЕРССЫЛКА”. Формула оператора выглядит следующим образом:
=ГИПЕРССЫЛКА(Адрес;Имя), где:
- “Адрес” – это, собственно говоря, конкретный адрес страницы в Интернете или путь к файлу, с которым нужно связать выбранный элемент.
- “Имя” – текстовое значение, отображаемое в выбранном элементе, при нажатии на которое активируется гиперссылка.
Как пользоваться оператором “ГИПЕРССЫЛКА”:
- Выбираем нужную ячейку и нажимаем кнопку “Вставить функцию”.
- В категории “Ссылки и массивы” выбираем оператор “ГИПЕРССЫЛКА” и щелкаем OK.
- Заполняем аргументы функции и жмем OK.
- в поле “Имя” прописываем текстовое значение, которое будет показываться в выбранной ячейке.
- Получаем в выбранной ячейке активную ссылку, ведущую на веб-страницу, адрес которой мы указали.
Создание связи между таблицами Excel
Связь между таблицами Excel – это формула, которая возвращает данные с ячейки другой рабочей книги. Когда вы открываете книгу, содержащую связи, Excel считывает последнюю информацию с книги-источника (обновление связей)
Межтабличные связи в Excel используются для получения данных как с других листов рабочей книги, так и с других рабочих книг Excel. К примеру, у вас имеется таблица с расчетом итоговой суммы продаж. В расчете используются цены на продукт и объем продаж. В таком случае имеет смысл создать отдельную таблицу с данными по ценам, которые будут подтягиваться с помощью связей первой таблицы.
Когда вы создаете связь между таблицами, Excel создает формулу, которая включает в себя имя исходной книги, заключенную в скобки [], имя листа с восклицательным знаком на конце и ссылку на ячейку.
Создание связей между рабочими книгами
- Открываем обе рабочие книги в Excel
- В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
- Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
- В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.
Есть еще один, более простой, вариант создания связи между таблицами. В ячейку, куда мы хотим вставить связь, ставим знак равенства (так же как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, щелкаем Enter.
Вы можете использовать инструменты копирования и автозаполнения для формул связи так же, как и для обычных формул.
Прежде чем создавать связи между таблицами
Прежде чем вы начнете распространять знания на свои грандиозные идеи, прочитайте несколько советов по работе со связями в Excel:
Делайте легко отслеживаемые связи. Не существует автоматического поиска всех ячеек, содержащих ссылки. Поэтому, используйте определенный формат, чтобы быстро идентифицировать связи с другими таблицами, в противном случае, документ, содержащий ссылки, может разрастись до такой степени, что его будет трудно поддерживать.
Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.
Избегайте циклические ссылки. Циклические связи – когда две рабочие книги содержат ссылки друг на друга – могут быть причиной медленного открытия и работы файла.
Обновление связей
Для ручного обновления связи между таблицами, перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи.
В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Обновить.
Разорвать связи в книгах Excel
Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.
Перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.
Структура и ссылки на Таблицу Excel
Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.
Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.
А также при наборе формулы вручную.
Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.
=Отчет – на всю Таблицу=Отчет – только на данные (без строки заголовка)=Отчет – только на первую строку заголовков=Отчет – на итоги=Отчет – на всю текущую строку (где вводится формула)=Отчет – на весь столбец «Продажи»=Отчет – на ячейку из текущей строки столбца «Продажи»
Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).
Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.
Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»
=СУММ(D2:D8)
то она автоматически переделается в
=Отчет
Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.
Это значит, что диаграмма или сводная таблица, где в качестве источника указана Таблица Excel, автоматически будет подтягивать новые записи.
А теперь о том, как Таблицы облегчают жизнь и работу.
Как найти ссылки на внешние источники excel
Связывание с другими книгами — очень распространенная задача в Excel, но иногда вы можете найти книгу, которая ссылок не может найти, хотя Excel сообщает о том, что она существует. Нет способа автоматического поиска всех внешних ссылок, используемых в книге, однако существует несколько способов, используемых вручную. Вам нужно искать формулы, определенные имена, объекты (например, текстовые поля или фигуры), названия диаграмм и ряды данных диаграммы.
Имя любой Excel книги, с помощью ссылки на которую вы ссылались, будет связана с расширением XL* (например, .xls, .xlsx, XLSM), поэтому рекомендуемый способ — найти все ссылки на частичное расширение XL. Если вы ссылались на другой источник, необходимо определить оптимальный поисковый запрос.
Поиск ссылок, используемых в формулах
Нажмите CTRL+F, чтобы запустить диалоговое окно Найти и заменить.
Нажмите кнопку Параметры.
В поле Найти введите.xl.
В поле В пределах выберите книга.
В поле Искать в выберите формулы.
Нажмите кнопку Найти все.
В отображемом списке наймем в столбце Формула формул, содержащих XL. В этом случае Excel найдено несколько экземпляров функции бюджетного Master.xlsx.
Чтобы выбрать ячейку с внешней ссылкой, щелкните ссылку на эту строку в списке.
Совет: Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
Проверьте каждую запись в списке и проверьте, нет ли в столбце Ссылка внешних ссылок. Внешние ссылки содержат ссылку на другую книгу, например .
Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
Если вы хотите удалить сразу несколько элементов, можно сгруппнуть несколько элементов, нажав клавишу SHIFT или CTRL и щелкнув левой кнопкой мыши.
Нажмите клавиши CTRL+G, нажмите клавиши CTRL+G, чтобы перейти в диалоговое окно Перейти, а затем выберите специальные > объекты > ОК. При этом будут выбраны все объекты на активном сайте.
Нажимая клавишу TAB, переходить между выбранными объектами, а затем искать в строка формул ссылку на другую книгу, например .
Щелкните название диаграммы, которую вы хотите проверить.
В строка формул наймем ссылку на другую книгу, например .
Выберите диаграмму, которую нужно проверить.
На вкладке Макет в группе Текущий выделение щелкните стрелку рядом с полем Элементы диаграммы и выберите ряд данных, которые нужно проверить.
На строка формул , наймем ссылку на другую книгу, например в функции РЯД.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Основные требования
Вам понадобятся Power Pivot и Power Query (получение и преобразование) для выполнения нескольких задач при создании модели данных Excel . Вот как вы можете получить эти функции в своей книге Excel:
Как получить Power Pivot
1. Excel 2010: вам необходимо загрузить надстройку Power Pivot от Microsoft, а затем установить ее для своей программы Excel на свой компьютер.
2. Excel 2013: выпуск Excel 2013 Office профессиональный плюс включает Power Pivot. Но вам необходимо активировать его перед первым использованием. Вот как:
- Щелкните Файл на ленте книги Excel.
- Затем щелкните Параметры, чтобы открыть Параметры Excel .
- Теперь нажмите « Надстройки» .
- Выберите надстройки COM , щелкнув раскрывающееся меню в поле « Управление» .
- Нажмите « Перейти» и установите флажок для Microsoft Power Pivot для Excel .
3. Excel 2016 и более поздние версии: на ленте вы найдете меню Power Pivot.
Как получить Power Query (получить и преобразовать)
1. Excel 2010: вы можете загрузить надстройку Power Query от Microsoft . После установки Power Query появится на ленте .
2. Excel 2013: вам необходимо активировать Power Query, выполнив те же действия, которые вы только что сделали, чтобы сделать Power Pivot работоспособным в Excel 2013.
3. Excel 2016 и более поздние версии вы можете найти Power Query (получение и преобразование), перейдя на вкладку « Данные » на ленте Excel.