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

Как в эксель связать таблицы на разных листах


Связанные таблицы в Excel

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

Связанные таблицы очень удобно использовать для обработки большого объема информации. Располагать всю информацию в одной таблице, к тому же, если она не однородная, не очень удобно. С подобными объектами трудно работать и производить по ним поиск. Указанную проблему как раз призваны устранить связанные таблицы, информация между которыми распределена, но в то же время является взаимосвязанной. Связанные табличные диапазоны могут находиться не только в пределах одного листа или одной книги, но и располагаться в отдельных книгах (файлах). Последние два варианта на практике используют чаще всего, так как целью указанной технологии является как раз уйти от скопления данных, а нагромождение их на одной странице принципиально проблему не решает. Давайте узнаем, как создавать и как работать с таким видом управления данными.

Создание связанных таблиц

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

Способ 1: прямое связывание таблиц формулой

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

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

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

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

  1. На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.
  2. Происходит перемещения во вторую область документа. Щелкаем по первой ячейке в столбце «Ставка». Затем кликаем по кнопке Enter на клавиатуре, чтобы произвести ввод данных в ячейку, в которой ранее установили знак «равно».
  3. Затем происходит автоматический переход на первый лист. Как видим, в соответствующую ячейку подтягивается величина ставки первого сотрудника из второй таблицы. Установив курсор на ячейку, содержащую ставку, видим, что для вывода данных на экран применяется обычная формула. Но перед координатами ячейки, откуда выводятся данные, стоит выражение «Лист2!», которое указывает наименование области документа, где они расположены. Общая формула в нашем случае выглядит так:

    =Лист2!B2

  4. Теперь нужно перенести данные о ставках всех остальных работников предприятия. Конечно, это можно сделать тем же путем, которым мы выполнили поставленную задачу для первого работника, но учитывая, что оба списка сотрудников расположены в одинаковом порядке, задачу можно существенно упростить и ускорить её решение. Это можно сделать, просто скопировав формулу на диапазон ниже. Благодаря тому, что ссылки в Excel по умолчанию являются относительными, при их копировании происходит сдвиг значений, что нам и нужно. Саму процедуру копирования можно произвести с помощью маркера заполнения.

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

  5. Все данные из аналогичного столбца на Листе 2 были подтянуты в таблицу на Листе 1. При изменении данных на Листе 2 они автоматически будут изменяться и на первом.

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

Но что делать, если перечень сотрудников в табличных массивах расположен не в одинаковом порядке? В этом случае, как говорилось ранее, одним из вариантов является установка связи между каждой из тех ячеек, которые следует связать, вручную. Но это подойдет разве что для небольших таблиц. Для массивных диапазонов подобный вариант в лучшем случае отнимет очень много времени на реализацию, а в худшем – на практике вообще будет неосуществим. Но решить данную проблему можно при помощи связки операторов ИНДЕКСПОИСКПОЗ. Посмотрим, как это можно осуществить, связав данные в табличных диапазонах, о которых шел разговор в предыдущем способе.

  1. Выделяем первый элемент столбца «Ставка». Переходим в Мастер функций, кликнув по пиктограмме «Вставить функцию».
  2. В Мастере функций в группе «Ссылки и массивы» находим и выделяем наименование «ИНДЕКС».
  3. Данный оператор имеет две формы: форму для работы с массивами и ссылочную. В нашем случае требуется первый вариант, поэтому в следующем окошке выбора формы, которое откроется, выбираем именно его и жмем на кнопку «OK».
  4. Выполнен запуск окошка аргументов оператора ИНДЕКС. Задача указанной функции — вывод значения, находящегося в выбранном диапазоне в строке с указанным номером. Общая формула оператора ИНДЕКС такова:

    =ИНДЕКС(массив;номер_строки;[номер_столбца])

    «Массив» — аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию по номеру указанной строки.

    «Номер строки» — аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.

    «Номер столбца» — аргумент, носящий необязательный характер. Для решения конкретно нашей задачи мы его использовать не будем, а поэтому описывать его суть отдельно не нужно.

    Ставим курсор в поле «Массив». После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка».

  5. После того, как координаты отобразились в окошке оператора, ставим курсор в поле «Номер строки». Данный аргумент мы будем выводить с помощью оператора ПОИСКПОЗ. Поэтому кликаем по треугольнику, который расположен слева от строки функций. Открывается перечень недавно использованных операторов. Если вы среди них найдете наименование «ПОИСКПОЗ», то можете кликать по нему. В обратном случае кликайте по самому последнему пункту перечня – «Другие функции…».
  6. Запускается стандартное окно Мастера функций. Переходим в нем в ту же самую группу «Ссылки и массивы». На этот раз в перечне выбираем пункт «ПОИСКПОЗ». Выполняем щелчок по кнопке «OK».
  7. Производится активация окошка аргументов оператора ПОИСКПОЗ. Указанная функция предназначена для того, чтобы выводить номер значения в определенном массиве по его наименованию. Именно благодаря данной возможности мы вычислим номер строки определенного значения для функции ИНДЕКС. Синтаксис ПОИСКПОЗ представлен так:

    =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

    «Искомое значение» — аргумент, содержащий наименование или адрес ячейки стороннего диапазона, в которой оно находится. Именно позицию данного наименования в целевом диапазоне и следует вычислить. В нашем случае в роли первого аргумента будут выступать ссылки на ячейки на Листе 1, в которых расположены имена сотрудников.

    «Просматриваемый массив» — аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.

    «Тип сопоставления» — аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1; 0; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.

    Итак, приступим к заполнению полей окна аргументов. Ставим курсор в поле «Искомое значение», кликаем по первой ячейке столбца «Имя» на Листе 1.

  8. После того, как координаты отобразились, устанавливаем курсор в поле «Просматриваемый массив» и переходим по ярлыку «Лист 2», который размещен внизу окна Excel над строкой состояния. Зажимаем левую кнопку мыши и выделяем курсором все ячейки столбца «Имя».
  9. После того, как их координаты отобразились в поле «Просматриваемый массив», переходим к полю «Тип сопоставления» и с клавиатуры устанавливаем там число «0». После этого опять возвращаемся к полю «Просматриваемый массив». Дело в том, что мы будем выполнять копирование формулы, как мы это делали в предыдущем способе. Будет происходить смещение адресов, но вот координаты просматриваемого массива нам нужно закрепить. Он не должен смещаться. Выделяем координаты курсором и жмем на функциональную клавишу F4. Как видим, перед координатами появился знак доллара, что означает то, что ссылка из относительной превратилась в абсолютную. Затем жмем на кнопку «OK».
  10. Результат выведен на экран в первую ячейку столбца «Ставка». Но перед тем, как производить копирование, нам нужно закрепить ещё одну область, а именно первый аргумент функции ИНДЕКС. Для этого выделяем элемент колонки, который содержит формулу, и перемещаемся в строку формул. Выделяем первый аргумент оператора ИНДЕКС (B2:B7) и щелкаем по кнопке F4. Как видим, знак доллара появился около выбранных координат. Щелкаем по клавише Enter. В целом формула приняла следующий вид:

    =ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))

  11. Теперь можно произвести копирование с помощью маркера заполнения. Вызываем его тем же способом, о котором мы говорили ранее, и протягиваем до конца табличного диапазона.
  12. Как видим, несмотря на то, что порядок строк у двух связанных таблиц не совпадает, тем не менее, все значения подтягиваются соответственно фамилиям работников. Этого удалось достичь благодаря применению сочетания операторов ИНДЕКСПОИСКПОЗ.

Читайте также:
Функция ИНДЕКС в Экселе
Функция ПОИСКПОЗ в Экселе

Способ 3: выполнение математических операций со связанными данными

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

Посмотрим, как это осуществляется на практике. Сделаем так, что на Листе 3 будут выводиться общие данные заработной платы по предприятию без разбивки по сотрудникам. Для этого ставки сотрудников будут подтягиваться из Листа 2, суммироваться (при помощи функции СУММ) и умножаться на коэффициент с помощью формулы.

  1. Выделяем ячейку, где будет выводиться итог расчета заработной платы на Листе 3. Производим клик по кнопке «Вставить функцию».
  2. Следует запуск окна Мастера функций. Переходим в группу «Математические» и выбираем там наименование «СУММ». Далее жмем по кнопке «OK».
  3. Производится перемещение в окно аргументов функции СУММ, которая предназначена для расчета суммы выбранных чисел. Она имеет нижеуказанный синтаксис:

    =СУММ(число1;число2;…)

    Поля в окне соответствуют аргументам указанной функции. Хотя их число может достигать 255 штук, но для нашей цели достаточно будет всего одного. Ставим курсор в поле «Число1». Кликаем по ярлыку «Лист 2» над строкой состояния.

  4. После того, как мы переместились в нужный раздел книги, выделяем столбец, который следует просуммировать. Делаем это курсором, зажав левую кнопку мыши. Как видим, координаты выделенной области тут же отображаются в поле окна аргументов. Затем щелкаем по кнопке «OK».
  5. После этого мы автоматически перемещаемся на Лист 1. Как видим, общая сумма размера ставок работников уже отображается в соответствующем элементе.
  6. Но это ещё не все. Как мы помним, зарплата вычисляется путем умножения величины ставки на коэффициент. Поэтому снова выделяем ячейку, в которой находится суммированная величина. После этого переходим к строке формул. Дописываем к имеющейся в ней формуле знак умножения (*), а затем щелкаем по элементу, в котором располагается показатель коэффициента. Для выполнения вычисления щелкаем по клавише Enter на клавиатуре. Как видим, программа рассчитала общую заработную плату по предприятию.
  7. Возвращаемся на Лист 2 и изменяем размер ставки любого работника.
  8. После этого опять перемещаемся на страницу с общей суммой. Как видим, из-за изменений в связанной таблице результат общей заработной платы был автоматически пересчитан.

Способ 4: специальная вставка

Связать табличные массивы в Excel можно также при помощи специальной вставки.

  1. Выделяем значения, которые нужно будет «затянуть» в другую таблицу. В нашем случае это диапазон столбца «Ставка» на Листе 2. Кликаем по выделенному фрагменту правой кнопкой мыши. В открывшемся списке выбираем пункт «Копировать». Альтернативной комбинацией является сочетание клавиш Ctrl+C. После этого перемещаемся на Лист 1.
  2. Переместившись в нужную нам область книги, выделяем ячейки, в которые нужно будет подтягивать значения. В нашем случае это столбец «Ставка». Щелкаем по выделенному фрагменту правой кнопкой мыши. В контекстном меню в блоке инструментов «Параметры вставки» щелкаем по пиктограмме «Вставить связь».

    Существует также альтернативный вариант. Он, кстати, является единственным для более старых версий Excel. В контекстном меню наводим курсор на пункт «Специальная вставка». В открывшемся дополнительном меню выбираем позицию с одноименным названием.

  3. После этого открывается окно специальной вставки. Жмем на кнопку «Вставить связь» в нижнем левом углу ячейки.
  4. Какой бы вариант вы не выбрали, значения из одного табличного массива будут вставлены в другой. При изменении данных в исходнике они также автоматически будут изменяться и во вставленном диапазоне.

Урок: Специальная вставка в Экселе

Способ 5: связь между таблицами в нескольких книгах

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

  1. Выделяем диапазон данных, который нужно перенести в другую книгу. Щелкаем по нему правой кнопкой мыши и выбираем в открывшемся меню позицию «Копировать».
  2. Затем перемещаемся к той книге, в которую эти данные нужно будет вставить. Выделяем нужный диапазон. Кликаем правой кнопкой мыши. В контекстном меню в группе «Параметры вставки» выбираем пункт «Вставить связь».
  3. После этого значения будут вставлены. При изменении данных в исходной книге табличный массив из рабочей книги будет их подтягивать автоматически. Причем совсем не обязательно, чтобы для этого были открыты обе книги. Достаточно открыть одну только рабочую книгу, и она автоматически подтянет данные из закрытого связанного документа, если в нем ранее были проведены изменения.

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

Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.

Разрыв связи между таблицами

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

Способ 1: разрыв связи между книгами

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

  1. В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные». Щелкаем по значку «Изменить связи», который расположен на ленте в блоке инструментов «Подключения». Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
  2. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь».
  3. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи».
  4. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Способ 2: вставка значений

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

  1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать». Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C.
  2. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения», которая размещена в группе инструментов «Параметры вставки».
  3. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

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

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТ

Как вставить гиперссылку на другой лист в Excel 2016, 2013, 2010

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

Если вы настоящий интернет-серфер, вы не понаслышке знаете о ярких сторонах гиперссылок. Щелкая гиперссылки, вы мгновенно получаете доступ к другой информации, где бы она ни находилась.Но знаете ли вы о преимуществах гиперссылок электронных таблиц в книгах Excel? Пришло время открыть их и начать использовать эту замечательную функцию Excel.

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

Вставить гиперссылку в Excel

Если вам нужно добавить гиперссылку в Excel 2016 или 2013, вы можете выбрать один из следующих типов гиперссылки : ссылка на существующий или новый файл, на веб-страницу или адрес электронной почты.Поскольку предметом этой статьи является создание гиперссылки на другой лист в той же книге, ниже вы узнаете три способа сделать это.

Добавить гиперссылку из контекстного меню

Первый метод создания гиперссылки в одной книге - использовать команду Hyperlink .

  1. Выберите ячейку, в которую вы хотите вставить гиперссылку.
  2. Щелкните ячейку правой кнопкой мыши и выберите в контекстном меню опцию Гиперссылка .

    На экране появится диалоговое окно Вставить гиперссылку .

  3. Выберите Поместить в этот документ в разделе Ссылка на , если ваша задача - связать ячейку с определенным местом в той же книге.
  4. Выберите рабочий лист, на который вы хотите создать ссылку в Или выберите место в этом поле документа.
  5. Введите адрес ячейки в поле Введите ссылку на ячейку , если вы хотите создать ссылку на определенную ячейку другого листа.
  6. Введите значение или имя в поле Текст для отображения , чтобы представить гиперссылку в ячейке.
  7. Щелкните ОК .

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

Функция ГИПЕРССЫЛКА в Excel

В

Excel есть функция ГИПЕРССЫЛКА, которую также можно использовать для создания ссылок между электронными таблицами в книге. Если вы не умеете сразу вводить формулы Excel на панели формул, сделайте следующее:

  1. Выберите ячейку, в которую вы хотите добавить гиперссылку.
  2. Перейдите к библиотеке функций на вкладке FORMULAS .
  3. Откройте раскрывающийся список Lookup & Reference и выберите HYPERLINK .

    Теперь вы можете увидеть имя функции в строке формул. Просто введите следующие два аргумента функции ГИПЕРССЫЛКА в диалоговом окне: link_location и friendly_name .

    В нашем случае link_location относится к определенной ячейке в другом листе Excel, а friendly_name - это текст перехода для отображения в ячейке.

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

  4. Заполните текстовое поле Link_location .

    Наконечник. Если вы не знаете, какой адрес ввести, просто используйте значок Выбрать диапазон , чтобы выбрать ячейку назначения.

    Адрес отображается в текстовом поле Расположение ссылки .

  5. Добавьте цифровой знак (#) перед указанным местом.

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

    Когда вы перейдете к текстовому полю Friendly_name , вы увидите результат формулы в нижнем левом углу диалогового окна «Аргументы функции».

  6. Введите Friendly_name , которое вы хотите отобразить в ячейке.
  7. Щелкните ОК .

Вот, пожалуйста! Все как надо: формула в строке формул, ссылка в ячейке. Щелкните ссылку, чтобы проверить, где она следует.

Вставка ссылки путем перетаскивания ячейки

Самый быстрый способ создания гиперссылок в одной книге - это перетаскивание. Позвольте мне показать вам, как это работает.

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

Примечание. Убедитесь, что книга сохранена, потому что этот метод не работает в новых книгах.

  1. Выберите ячейку назначения гиперссылки на листе 2.
  2. Укажите на одну из границ ячейки и щелкните правой кнопкой мыши.
  3. Зажмите кнопку и спуститесь к вкладкам листа.
  4. Нажмите клавишу Alt и наведите указатель мыши на вкладку «Лист 1».

    Если нажать клавишу Alt, вы автоматически перейдете на другой лист. Как только Лист 1 активирован, вы можете перестать удерживать клавишу.

  5. Продолжайте перетаскивать в то место, куда вы хотите вставить гиперссылку.
  6. Отпустите правую кнопку мыши, чтобы появилось всплывающее меню.
  7. Выберите Создать гиперссылку здесь из меню.

После этого в ячейке появится гиперссылка. Щелкнув по ней, вы переключитесь на целевую ячейку на Листе 2.

Несомненно, перетаскивание - это самый быстрый способ вставить гиперссылку в рабочий лист Excel. Он объединяет несколько операций в одно действие. Это займет меньше времени, но немного больше концентрации внимания, чем два других метода. Так что вам решать, какой путь
выбрать.

Редактировать гиперссылку

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

Изменить назначение ссылки

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

  1. Щелкните правой кнопкой мыши гиперссылку, которую хотите отредактировать.
  2. Выберите Изменить гиперссылку во всплывающем меню.

    На экране появится диалоговое окно Редактировать гиперссылку . Вы видите, что он выглядит так же, как диалог Insert Hyperlink , и имеет те же поля и макет.

    Примечание. Есть еще как минимум два способа открыть диалоговое окно Edit Hyperlink . Вы можете нажать Ctrl + K или щелкнуть Hyperlink в группе Links на вкладке INSERT . Но не забудьте перед этим выделить нужную ячейку.

  3. Обновите информацию в соответствующих полях диалогового окна Изменить гиперссылку .
  4. Щелкните ОК и проверьте, куда теперь переходит гиперссылка. Примечание. Если вы использовали метод 2 для добавления гиперссылки в Excel, вам необходимо отредактировать формулу, чтобы изменить место назначения гиперссылки.Выберите ячейку, содержащую ссылку, а затем поместите курсор на панель формул, чтобы отредактировать ее.

Изменить формат гиперссылки

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

  1. Перейдите в группу Styles на вкладке HOME .
  2. Откройте список Стили ячеек .
  3. Щелкните правой кнопкой мыши Hyperlink , чтобы изменить внешний вид гиперссылки, которая не была нажата. Или щелкните правой кнопкой мыши Followed Hyperlink , если гиперссылка была активирована.
  4. Выберите опцию Изменить из контекстного меню.
  5. Щелкните Формат в диалоговом окне Стили .
  6. Внесите необходимые изменения в диалоговом окне Формат ячеек . Здесь вы можете изменить выравнивание гиперссылки и шрифт или добавить цвет заливки.
  7. Когда вы закончите, нажмите ОК.
  8. Убедитесь, что все изменения отмечены в разделе Стиль включает в диалоговом окне Стиль .
  9. Нажмите ОК.

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

Удалить гиперссылку

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

  1. Щелкните правой кнопкой мыши гиперссылку, которую вы хотите удалить.
  2. Выберите Удалить гиперссылку во всплывающем меню.

Текст остается в ячейке, но больше не является гиперссылкой.

Примечание. Если вы хотите удалить гиперссылку и текст, который ее представляет, щелкните правой кнопкой мыши ячейку, содержащую ссылку, и выберите в меню опцию Очистить содержимое .

Этот трюк поможет вам удалить одну гиперссылку.Если вы хотите узнать, как удалить несколько (все) гиперссылок с листов Excel за раз, перейдите по ссылке на нашу предыдущую запись в блоге.

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

Вас также может заинтересовать

.

Создать связь между таблицами в Excel

Вы когда-нибудь использовали ВПР для переноса столбца из одной таблицы в другую? Теперь, когда Excel имеет встроенную модель данных, функция VLOOKUP устарела. Вы можете создать связь между двумя таблицами данных на основе совпадающих данных в каждой таблице. Затем вы можете создавать листы Power View и строить сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы из разных источников. Например, если у вас есть данные о продажах клиентов, вы можете импортировать и связать данные анализа времени для анализа моделей продаж по годам и месяцам.

Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.

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

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

  2. Выполните одно из следующих действий: Отформатируйте данные как таблицу или Импортируйте внешние данные как таблицу на новом листе.

  3. Дайте каждой таблице осмысленное имя: В Инструменты таблиц щелкните Дизайн > Имя таблицы > введите имя.

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

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

  5. Щелкните Данные > Отношения .

Если Отношения неактивны, ваша книга содержит только одну таблицу.

  1. В поле Управление взаимосвязями щелкните Новый .

  2. В поле Create Relationship щелкните стрелку для Таблица и выберите таблицу из списка. В отношениях «один ко многим» эта таблица должна быть на стороне многих. Используя наш пример анализа данных о клиентах и ​​времени, вы должны сначала выбрать таблицу продаж по клиентам, потому что многие продажи, вероятно, произойдут в любой день.

  3. Для столбца (внешний) выберите столбец, содержащий данные, относящиеся к Связанный столбец (основной) . Например, если у вас есть столбец даты в обеих таблицах, вы бы выбрали этот столбец сейчас.

  4. Для Связанная таблица выберите таблицу, в которой есть хотя бы один столбец данных, связанный с таблицей, которую вы только что выбрали для Таблица .

  5. Для Связанный столбец (основной) выберите столбец с уникальными значениями, соответствующими значениям в столбце, выбранном для Столбец .

  6. Щелкните ОК .

Подробнее о связях между таблицами в Excel

Заметки об отношениях

Пример: связь данных времени с данными о рейсах авиакомпаний

«Могут потребоваться связи между таблицами»

Шаг 1. Определите, какие таблицы указать в отношении

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

Примечания к отношениям

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

  • Создание отношений аналогично использованию ВПР: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel мог связывать строки в одной таблице со строками другой таблицы. В примере с аналитикой времени в таблице «Клиент» должны быть значения даты, которые также существуют в таблице аналитики времени.

  • В модели данных отношения таблиц могут быть взаимно однозначными (каждый пассажир имеет один посадочный талон) или один ко многим (на каждом рейсе много пассажиров), но не многие ко многим.Отношения «многие ко многим» приводят к ошибкам циклической зависимости, таким как «Обнаружена циклическая зависимость». Эта ошибка возникает, если вы устанавливаете прямое соединение между двумя таблицами, которые являются многими-ко-многим, или косвенные соединения (цепочка отношений таблиц, которые являются взаимно-многими внутри каждого отношения, но многие-ко-многим при просмотре end в конец. Подробнее о взаимосвязях между таблицами в модели данных.

  • Типы данных в двух столбцах должны быть совместимы.Дополнительные сведения см. В разделе Типы данных в моделях данных Excel.

  • Другие способы создания отношений могут быть более интуитивными, особенно если вы не знаете, какие столбцы использовать. См. Раздел Создание отношения в представлении схемы в Power Pivot.

Пример: Связывание данных анализа времени с данными полета авиакомпании

Вы можете узнать как о связях таблиц, так и о логике времени, используя бесплатные данные на Microsoft Azure Marketplace.Некоторые из этих наборов данных очень большие и требуют быстрого подключения к Интернету для завершения загрузки данных за разумный период времени.

  1. Запустите Power Pivot в надстройке Microsoft Excel и откройте окно Power Pivot.

  2. Щелкните Получить внешние данные > из службы данных > из Microsoft Azure Marketplace . Домашняя страница Microsoft Azure Marketplace откроется в мастере импорта таблиц.

  3. Меньше Цена , нажмите Бесплатно .

  4. В разделе Категория щелкните Наука и статистика .

  5. Найдите DateStream и нажмите Подписаться .

  6. Войдите в свою учетную запись Microsoft и нажмите Войти .Предварительный просмотр данных должен появиться в окне.

  7. Прокрутите вниз и щелкните Выбрать запрос .

  8. Щелкните Далее .

  9. Выберите BasicCalendarUS и затем щелкните Finish , чтобы импортировать данные. При быстром подключении к Интернету импорт должен занять около минуты. По завершении вы должны увидеть отчет о 73 414 переданных строках.Щелкните Закрыть .

  10. Щелкните Получить внешние данные > из службы данных > из Microsoft Azure Marketplace , чтобы импортировать второй набор данных.

  11. В разделе Тип щелкните Данные .

  12. Меньше Цена , нажмите Бесплатно .

  13. Найдите US Air Carrier Flight Delays и щелкните Выберите .

  14. Прокрутите вниз и щелкните Выбрать запрос .

  15. Щелкните Далее .

  16. Нажмите Готово , чтобы импортировать данные. При быстром подключении к Интернету импорт может занять 15 минут. По завершении вы должны увидеть отчет о состоянии передачи 2 427 284 строк. Щелкните Закрыть .Теперь у вас должно быть две таблицы в модели данных. Чтобы связать их, нам понадобятся совместимые столбцы в каждой таблице.

  17. Обратите внимание, что DateKey в BasicCalendarUS имеет формат 01.01.2012 12:00:00 AM. Таблица On_Time_Performance также имеет столбец даты и времени FlightDate , значения которого указаны в том же формате: 01.01.2012 12:00:00 AM. Два столбца содержат совпадающие данные одного и того же типа данных, и по крайней мере один из столбцов ( DateKey ) содержит только уникальные значения.В следующих нескольких шагах вы будете использовать эти столбцы, чтобы связать таблицы.

  18. В окне Power Pivot щелкните PivotTable , чтобы создать сводную таблицу на новом или существующем листе.

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

  20. Разверните BasicCalendarUS и щелкните MonthInCalendar , чтобы добавить его в область строк.

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

  22. В списке полей в разделе «Могут потребоваться связи между таблицами» щелкните Создать .

  23. В связанной таблице выберите On_Time_Performance , а в связанном столбце (основной) выберите FlightDate .

  24. В таблице выберите BasicCalendarUS , а в столбце (Внешний) выберите DateKey . Щелкните ОК , чтобы создать отношение.

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

  26. В BasicCalendarUS и перетащите YearKey в область строк выше MonthInCalendar .

Теперь вы можете разделить задержки прибытия по году и месяцу или другим значениям в календаре.

Советы: По умолчанию месяцы перечислены в алфавитном порядке. Используя надстройку Power Pivot, вы можете изменить сортировку, чтобы месяцы отображались в хронологическом порядке.

  1. Убедитесь, что таблица BasicCalendarUS открыта в окне Power Pivot.

  2. В главной таблице щелкните Сортировать по столбцу .

  3. В разделе «Сортировка» выберите МесяцInCalendar

  4. По, выберите Месяц Года .

Сводная таблица теперь сортирует каждую комбинацию месяц-год (октябрь 2011 г., ноябрь 2011 г.) по номеру месяца в году (10, 11). Изменить порядок сортировки очень просто, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу интеллекта времени, ваш шаг будет другим.

«Могут потребоваться связи между таблицами»

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

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

Шаг 1. Определите, какие таблицы указать в отношении

Если ваша модель содержит всего несколько таблиц, может быть сразу очевидно, какие из них вам нужно использовать. Но для более крупных моделей вам, вероятно, понадобится помощь.Один из подходов - использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуальное представление всех таблиц в модели данных. Используя представление диаграммы, вы можете быстро определить, какие таблицы отделены от остальной модели.

Примечание. Возможно создание неоднозначных отношений, которые недопустимы при использовании в сводной таблице или отчете Power View. Предположим, все ваши таблицы каким-либо образом связаны с другими таблицами в модели, но когда вы пытаетесь объединить поля из разных таблиц, вы получаете сообщение «Могут потребоваться связи между таблицами».Наиболее вероятная причина в том, что у вас возникли отношения "многие ко многим". Если вы проследите цепочку отношений таблиц, которые соединяются с таблицами, которые вы хотите использовать, вы, вероятно, обнаружите, что у вас есть две или более отношений таблиц «один ко многим». Не существует простого обходного пути, который работал бы в любой ситуации, но вы можете попробовать создать вычисляемые столбцы, чтобы объединить столбцы, которые вы хотите использовать, в одну таблицу.

Шаг 2: Найдите столбцы, которые можно использовать для создания пути от одной таблицы к следующей

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

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

Помимо соответствия значений, есть несколько дополнительных требований для создания отношения:

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

  • Типы данных как исходного столбца, так и столбца подстановки должны быть совместимы. Дополнительные сведения о типах данных см. В разделе Типы данных в моделях данных.

Чтобы узнать больше о связях таблиц, см. Связи между таблицами в модели данных.

Верх страницы

.

Работа со ссылками в Excel

Для быстрого доступа к связанной информации в другом файле или на веб-странице вы можете вставить гиперссылку в ячейку листа. Вы также можете вставлять ссылки в определенные элементы диаграммы.

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

  1. На листе щелкните ячейку, в которой вы хотите создать ссылку.

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

    Вы также можете щелкнуть правой кнопкой мыши ячейку или рисунок и затем щелкнуть Ссылка в контекстном меню или нажать Ctrl + K.

  2. В разделе Ссылка на щелкните Создать новый документ .

  3. В поле Имя нового документа введите имя нового файла.

    Совет: Чтобы указать расположение, отличное от указанного в поле Полный путь , вы можете ввести новое расположение перед именем в поле Имя нового документа или щелкнуть Изменить , чтобы выбрать расположение, которое вы хотите, а затем нажмите ОК .

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

  5. В поле Текст для отображения введите текст, который вы хотите использовать для представления ссылки.

  6. Чтобы отобразить полезную информацию при наведении указателя на ссылку, щелкните ScreenTip , введите нужный текст в поле текста всплывающей подсказки , а затем щелкните OK .

  1. На листе щелкните ячейку, в которой вы хотите создать ссылку.

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

    Вы также можете щелкнуть правой кнопкой мыши ячейку или объект, а затем щелкнуть Ссылка в контекстном меню или нажать Ctrl + K.

  2. В разделе Ссылка на щелкните Существующий файл или веб-страницу .

  3. Выполните одно из следующих действий:

    • Чтобы выбрать файл, щелкните Текущая папка , а затем щелкните файл, на который нужно создать ссылку.

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

    • Чтобы выбрать веб-страницу, щелкните Просмотренные страницы , а затем щелкните веб-страницу, на которую нужно создать ссылку.

    • Чтобы выбрать файл, который вы недавно использовали, щелкните Последние файлы и

.

Как скопировать таблицу в Excel с сохранением формата ячеек

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

Как вставить формулу в таблицу Excel с сохранением формата? Решая эту задачу, можно сэкономить массу времени. Для этого воспользуемся функцией «Специальная вставка». Это простой инструмент, позволяющий быстро решать сложные задачи и осваивать основы его использования.Этот простой инструмент используется для быстрого применения одного формата ко всем таблицам и достижения необходимого результата.

Взаимодействие с другими людьми

Как скопировать таблицу с сохранением ширины столбца и высоты строки

Допустим, у нас есть таблица, формат которой нужно сохранить при копировании:

Когда вы копируете его в другую таблицу, используя обычный метод Ctrl + C - Ctrl + V, результат далек от желаемого:

Придется вручную регулировать ширину столбца, придавая ему удобную форму.Если стол большой, вам придется долго возиться. Однако есть способ значительно сократить потерю времени.

Метод 1. Использование специальной пасты

  1. Выберите исходную таблицу, которую необходимо скопировать, и нажмите Ctrl + C.
  2. Выберите новую (уже скопированную) таблицу, в которой нужно применить формат ширины столбца, и щелкните правой кнопкой мыши ячейку. Найдите в выпадающем меню строку «Специальная вставка» (CTRL + ALT + V).
  3. В открывшемся диалоговом окне выберите опцию «Ширина столбца» и нажмите «ОК».

В таблице добавлены исходные параметры, выглядит идеально.

Метод 2. Выбор столбцов перед копированием

У этого метода есть секрет: если вы выберете столбцы вместе с их заголовками перед копированием таблицы, ширина каждого столбца также будет скопирована при вставке.

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

Каждый из методов рационально использовать в зависимости от поставленной задачи.Однако второй способ позволяет как быстро перенести таблицу вместе с форматом, так и скопировать высоту строки. Обратите внимание, что в специальном меню вставки отсутствует параметр «Высота строки». Таким образом, для выполнения такой задачи вам потребуется предпринять следующие шаги:

  1. Выберите все строки и столбцы на листе (CTRL + A).
  2. Вставьте его копию ниже.

Полезный совет! Самый быстрый способ скопировать большую и сложную таблицу с сохранением ширины столбца и высоты строки - скопировать ее как целый лист.

Удерживая нажатой клавишу CTRL, перетащите вкладку листа.

Взаимодействие с другими людьми

Как вставить значения формулы, сохраняя формат таблицы

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

Шаг 1:

  1. Выберите исходную таблицу, содержащую формулы, и скопируйте ее.
  2. В том месте, куда нужно вставить диапазон значений (без формул), выберите опцию «Значения».Щелкните ОК.

Шаг 2:

Так как скопированный диапазон все еще находится в буфере обмена после того, как вы его скопировали, снова откройте специальный диалог вставки и выберите опцию «Форматы». Щелкните ОК.

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

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

.

Смотрите также

Scroll To Top