Как связать ячейки в excel на одном листе для сортировки

Обновлено: 07.07.2024

Информация в этой статье относится к версиям Excel 2019, 2016, 2013, 2010 и Excel для Mac.

Вставить таблицу

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

Чтобы создать таблицу в Excel:

Выберите одну ячейку внутри блока данных.

Выберите Вставить .

Выберите Таблица . Excel выбирает весь блок непрерывных данных и открывает диалоговое окно Создать таблицу .

Если в Excel возникают проблемы с правильным форматированием таблицы, выделите данные, прежде чем выбрать параметр Вставить Таблица .

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

Выберите ОК , чтобы создать таблицу.

Особенности стола

Наиболее заметные функции, которые Excel добавляет в блок данных:

• Раскрывающиеся меню в заголовках столбцов, которые содержат параметры сортировки, фильтрации и поиска.
• Альтернативные заштрихованные строки, которые облегчают чтение данных.
• Калибровочные ручки расположены по углам стола.
• Значок быстрого анализа (в Excel 2013 и новее), который появляется в правом нижнем углу активной ячейки, когда выбраны две или более ячейки в таблице. Это облегчает анализ данных с использованием диаграмм, сводных таблиц, промежуточных итогов и условного форматирования.

Управление данными таблицы

Параметры сортировки и фильтрации

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

• Показывать только те данные, которые соответствуют указанным критериям.
• Фильтруйте по шрифту или по цвету фона ячейки.
• Поиск конкретных записей путем сопоставления отдельных полей данных.

Добавить и удалить поля и записи

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

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

Расчетные столбцы

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

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

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

Кроме того, другие вычисления Excel, такие как SUM, AVERAGE, MAX и MIN, могут быть добавлены с помощью раскрывающегося меню параметров. Эти дополнительные вычисления также используют функцию SUBTOTAL.

Выберите ячейку в таблице.

Перейдите на вкладку Дизайн инструментов для таблиц .

В группе Параметры стиля таблицы установите флажок Общая строка .

Чтобы добавить другие вычисления в итоговую строку:

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

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

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

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

Удалить таблицу, но сохранить данные

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

Выберите ячейку в таблице.

Перейдите на вкладку Дизайн инструментов для таблиц .

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

Выберите Да для подтверждения.

Функции таблицы, такие как раскрывающиеся меню и указатель размера, удаляются, но данные, затенение строк и другие функции форматирования сохраняются.

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

Сортировка связанных данных и сохранение формул с помощью Kutools for Excel

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

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

Выберите ячейку формулы, выберите формулу в строке формул и нажмите F4 Клавиша изменения ссылки на абсолютную ссылку.

Чаевые: Если в одной ячейке несколько ссылок, вам нужно изменить ссылки одну за другой, выбрав и нажав F4 в строке формул.

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

Теперь формулы сохраняются при сортировке.

Сортировка связанных данных и сохранение формул с помощью Kutools for Excel

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

После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Выберите ячейки, ссылку на которые вы хотите изменить, нажмите Kutools > Подробнее (в группе Формула) > Преобразовать ссылки.

2. в Преобразование ссылок на формулы диалог, проверьте К абсолютному вариант. Смотрите скриншот:

3. Нажмите Ok. Затем весь выбор был преобразован в абсолютные ссылки.

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

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

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

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

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

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

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

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

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

На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.

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

Выделяем первый элемент столбца «Ставка». Переходим в Мастер функций, кликнув по пиктограмме «Вставить функцию».

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

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

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

Выделяем ячейку, где будет выводиться итог расчета заработной платы на Листе 3. Производим клик по кнопке «Вставить функцию».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

Зачем связывать данные ячейки в Excel

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

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

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

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

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

Как связать две отдельные клетки

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

• В Sheet2 введите символ равенства (=) в клетку.
• Перейти на другую вкладку (Лист1) и щелкните ячейку, на которую вы хотите сослаться.
• Нажмите Войти завершить формулу.

Теперь, если вы нажмете на ячейку в Sheet2вы увидите, что Excel записывает для вас путь в строке формул.

Например, = Лист1! C3, где Лист1 это имя листа, C3 является ли ячейка, на которую вы ссылаетесь, и восклицательный знак (!) используется как разделитель между ними.

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

Примечание. Если имя листа содержит пробелы (например, Лист 1), то при вводе ссылки в ячейку необходимо указать имя в одинарных кавычках. подобно = ’Лист 1 ′! C3, Поэтому иногда проще и надежнее позволить Excel написать для вас справочную формулу.

Как связать диапазон ячеек

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

• В исходной вкладке с данными (Лист1), выделите ячейки, на которые вы хотите сослаться.
• Скопируйте ячейки (Ctrl/команда + С, или щелкните правой кнопкой мыши и выберите копия).
• Перейти на другую вкладку (Sheet2) и нажмите на ячейку (или ячейки), где вы хотите разместить ссылки.

• Щелкните правой кнопкой мыши по ячейке (ям) и выберите Специальная паста…

• В левом нижнем углу меню выберите Вставить ссылку,

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

Как связать ячейку с функцией

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

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

У вас будет = SUM (Лист1 C3: C7)где функция SUM суммирует содержимое ячеек C3: C7 в Sheet1. Нажмите Войти завершить формулу.

Как связать ячейки из разных файлов Excel

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

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

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

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

Станьте профессиональным пользователем Microsoft Excel

Какие еще изящные лайфхаки Excel вы знаете и используете? Знаете ли вы какие-либо другие творческие способы связать ячейки в Excel? Поделитесь ими с нами в разделе комментариев ниже.

Читайте также:

  
• Компьютерная программа может управлять работой компьютера если она находится
  
• Как исправить транспортный налог за прошлый год в 1с
  
• Программы предъявляющие разнообразные задачи и побуждающие решать их путем проб и ошибок
  
• Как прописать alt к картинке в фотошопе
  
• 1с обработкавыбора очистить выбранное значение