Содержание:
- Отразить данные с помощью SORT и вспомогательного столбца
- Переверните данные по вертикали (обратный порядок вверх ногами)
- Отразить данные по горизонтали
- Отразить данные с помощью формул
- Использование функции СОРТБИ (доступно в Microsoft 365)
- Использование функции ИНДЕКС
- Перевернуть данные с помощью VBA
Иногда вам может потребоваться перевернуть данные в Excel, то есть изменить порядок данных вверх ногами в вертикальном наборе данных и слева направо в горизонтальном наборе данных.
Если вы думаете, что для этого в Excel должна быть встроенная функция, боюсь, вы будете разочарованы.
Хотя есть несколько способов перевернуть данные в Excel, встроенной функции нет. Но вы можете легко сделать это, используя простой трюк сортировки, формулы или VBA.
В этом руководстве я покажу вам, как переворачивать данные в строках, столбцах и таблицах в Excel.
Итак, приступим!
Отразить данные с помощью SORT и вспомогательного столбца
Один из самых простых способов изменить порядок данных в Excel — использовать вспомогательный столбец, а затем использовать этот вспомогательный столбец для сортировки данных.
Переверните данные по вертикали (обратный порядок вверх ногами)
Предположим, у вас есть набор данных с именами в столбце, как показано ниже, и вы хотите перевернуть эти данные:
Ниже приведены шаги, чтобы перевернуть данные по вертикали:
- В соседнем столбце введите «Помощник» в качестве заголовка столбца.
- В столбце помощника введите ряд чисел (1, 2, 3 и т. Д.). Вы можете использовать показанные здесь методы, чтобы сделать это быстро.
- Выберите весь набор данных, включая вспомогательный столбец
- Перейдите на вкладку «Данные«.
- Щелкните значок сортировки
- В диалоговом окне «Сортировка» выберите «Помощник» в раскрывающемся списке «Сортировать по».
- В раскрывающемся списке «Порядок» выберите «От наибольшего к наименьшему».
- Нажмите ОК.
Вышеупомянутые шаги будут отсортировать данные на основе значений вспомогательных столбцов, что также приведет к изменению порядка имен в данных на обратный.
После этого можете удалить вспомогательный столбец.
В этом примере я показал вам, как переворачивать данные, когда у вас всего один столбец, но вы также можете использовать тот же метод, если у вас есть вся таблица. Просто убедитесь, что вы выбрали всю таблицу, а затем использовали вспомогательный столбец для сортировки данных в порядке убывания.
Отразить данные по горизонтали
Вы также можете использовать ту же методику, чтобы перевернуть данные в Excel по горизонтали.
В Excel есть возможность сортировать данные по горизонтали с помощью диалогового окна «Сортировка» (функция «Сортировать слева направо»).
Предположим, у вас есть таблица, показанная ниже, и вы хотите перевернуть эти данные по горизонтали.
Ниже приведены шаги для этого:
- В строке ниже введите «Помощник» в качестве заголовка строки.
- Во вспомогательной строке введите ряд чисел (1, 2, 3 и т. Д.).
- Выберите весь набор данных, включая вспомогательную строку
- Перейдите на вкладку «Данные«.
- Щелкните значок сортировки
- В диалоговом окне «Сортировка» нажмите кнопку «Параметры«.
- В открывшемся диалоговом окне нажмите «Сортировать слева направо».
- Нажмите ОК.
- В раскрывающемся списке «Сортировать по» выберите строку 3 (или любую другую строку, в которой есть вспомогательный столбец).
- В раскрывающемся списке «Порядок» выберите «От наибольшего к наименьшему».
- Нажмите ОК.
Вышеупомянутые шаги перевернут всю таблицу по горизонтали.
После этого вы можете удалить вспомогательную строку.
Отразить данные с помощью формул
В Microsoft 365 появилось несколько новых формул, которые позволяют очень легко изменить порядок столбца или таблицы в Excel.
В этом разделе я покажу вам, как это сделать, используя формулу СОРТБИ (если вы используете Microsoft 365) или формулу ИНДЕКС (если вы не используете Microsoft 365).
Использование функции СОРТБИ (доступно в Microsoft 365)
Предположим, у вас есть таблица, показанная ниже, и вы хотите перевернуть данные в этой таблице:
Для этого сначала скопируйте заголовки и поместите их там, где вы хотите, чтобы перевернутая таблица.
Теперь используйте следующую формулу под ячейкой в крайнем левом заголовке:= СОРТИРОВАТЬ ($ A $ 2: $ B $ 12; СТРОКА (A2: A12); - 1)
Приведенная выше формула сортирует данные и использует результат функции СТРОКА в качестве основы для их сортировки.
Функция ROW в этом случае вернет массив чисел, который представляет номера строк между указанным диапазоном (который в этом примере будет серией чисел, таких как 2, 3, 4 и т. Д.).
А поскольку третий аргумент этой формулы равен -1, это заставит формулу сортировать данные в порядке убывания.
Запись с наивысшим номером строки будет наверху, а запись с наименьшим номером правила — внизу, что существенно меняет порядок данных.
После этого вы можете преобразовать формулу в значения, чтобы получить статическую таблицу.
Использование функции ИНДЕКС
Если у вас нет доступа к функции СОРТИРОВКА, не беспокойтесь — вы можете использовать удивительную функцию ИНДЕКС.
Предположим, у вас есть набор данных с именами, как показано ниже, и вы хотите перевернуть эти данные.
Ниже приведена формула для этого:= ИНДЕКС ($ A $ 2: $ A $ 12, СТРОКИ (A2: $ A $ 12))
Как работает эта формула?
В приведенной выше формуле используется функция ИНДЕКС, которая возвращает значение из ячейки на основе числа, указанного во втором аргументе.
Настоящая магия происходит во втором аргументе, где я использовал функцию ROWS.
Поскольку я заблокировал вторую часть ссылки в функции ROWS, в первой ячейке она вернет количество строк между A2 и A12, которое будет равно 11.
Но когда он идет вниз по строкам, первая ссылка изменится на A3, а затем на A4 и так далее, в то время как вторая ссылка останется как есть, потому что я заблокировал ее и сделал ее абсолютной.
По мере того, как мы спускаемся по строкам, результат функции ROWS будет уменьшаться на 1, с 11 до 10, до 9 и так далее.
А поскольку функция ИНДЕКС возвращает нам значение на основе числа во втором аргументе, это в конечном итоге даст нам данные в обратном порядке.
Вы можете использовать ту же формулу, даже если у вас есть несколько столбцов в наборе данных. однако вам нужно будет указать второй аргумент, который будет указывать номер столбца, из которого должны быть извлечены данные.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите изменить порядок всей таблицы в обратном порядке:
Ниже приведена формула, которая сделает это за вас:= ИНДЕКС ($ A $ 2: $ B $ 12, СТРОКИ (A2: $ A $ 12), КОЛОННЫ ($ A $ 2: A2))
Это аналогичная формула, в которой я также добавил третий аргумент, определяющий номер столбца, из которого должно быть извлечено значение.
Чтобы сделать эту формулу динамической, я использовал функцию COLUMNS, которая продолжала бы изменять значение столбца с 1 на 2 до 3, когда вы копируете его вправо.
После этого вы можете преобразовать формулы в значения, чтобы получить статический результат.
Примечание. При использовании формулы для изменения порядка набора данных в Excel исходное форматирование не сохраняется. Если вам также нужно исходное форматирование для отсортированных данных, вы можете применить его вручную или скопировать и вставить форматирование из исходного набора данных в новый отсортированный набор данных.
Перевернуть данные с помощью VBA
Если перелистывание данных в Excel — это то, что вам нужно делать довольно часто, вы также можете попробовать метод VBA.
С помощью кода макроса VBA вы можете один раз скопировать и вставить его в книгу в редакторе VBA, а затем повторно использовать его снова и снова в той же книге.
Вы также можете сохранить код в личной книге макросов или как надстройку Excel и иметь возможность использовать его в любой книге в вашей системе.
Ниже приведен код VBA, который перевернет выбранные данные на листе по вертикали.Sub FlipVerically () 'Code by Sumit Bansal с TrumpExcel.com Dim TopRow As Variant Dim LastRow As Variant Dim StartNum As Integer Dim EndNum as Integer Application.ScreenUpdating = False StartNum = 1 EndNum = Selection.Rows.Count Do While StartNum <EndNum TopRow = Selection.Rows (StartNum) LastRow = Selection.Rows (EndNum) Selection.Rows (EndNum) = TopRow Selection.Rows (StartNum) = LastRow StartNum = StartNum + 1 EndNum = EndNum - 1 приложение цикла. ScreenUpdating = True End Sub
Чтобы использовать этот код, вам сначала нужно выбрать набор данных, который вы хотите отменить (исключая заголовки), а затем запустить этот код.
Как работает этот код?
Приведенный выше код сначала подсчитывает общее количество строк в наборе данных и присваивает его переменной EndNum.
Затем он использует цикл Do While, в котором происходит сортировка данных.
Он сортирует эти данные, беря первую и последнюю строки и меняя их местами. Затем он переходит во вторую строку во второй последней строке и меняет их местами. Затем он переходит в третий ряд в третьем последнем ряду и так далее.
Цикл заканчивается, когда вся сортировка завершена.
Он также использует свойство Application.ScreenUpdating и устанавливает для него значение FALSE во время выполнения кода, а затем возвращает его в значение TRUE, когда код завершает работу.
Это гарантирует, что вы не увидите изменений, происходящих в реальном времени на экране, а также ускоряет процесс.
Как пользоваться кодом?
Выполните следующие действия, чтобы скопировать и вставить этот код в редактор VB:
- Откройте файл Excel, в который вы хотите добавить код VBA.
- Удерживая клавишу ALT, нажмите клавишу F11 (вы также можете перейти на вкладку Разработчик и щелкнуть значок Visual Basic)
- В открывшемся редакторе Visual Basic в левой части редактора VBA будет Project Explorer. Если вы его не видите, перейдите на вкладку «Просмотр», а затем нажмите «Обозреватель проекта».
- Щелкните правой кнопкой мыши любой из объектов книги, в которую вы хотите добавить код.
- Перейдите к опции «Вставить» и нажмите «Модуль». Это добавит новый модуль в книгу
- Дважды щелкните значок модуля в проводнике проекта. Это откроет окно кода для этого модуля
- Скопируйте и вставьте приведенный выше код VBA в окно кода.
Чтобы запустить код макроса VBA, сначала выберите набор данных, который вы хотите перевернуть (за исключением заголовков).
Выбрав данные, перейдите в редактор VB и нажмите зеленую кнопку воспроизведения на панели инструментов или выберите любую строку в коде и затем нажмите клавишу F5.
Итак, это некоторые из методов, которые вы можете использовать для переворота данных в Excel (т. Е. Обратного порядка набора данных).
Все методы, которые я рассмотрел в этом руководстве (формулы, функция СОРТИРОВКИ и VBA), можно использовать для переворота данных по вертикали и горизонтали (вам нужно будет соответствующим образом настроить формулу и код VBA для горизонтального переворота данных).
Надеюсь, вы нашли этот урок полезным.
Часто во время форматирования готовых таблиц в Microsoft Excel пользователю приходится выполнять разворот. Такая функция выполняется в несколько несложных шагов. Обычно данные в электронной книге принято разворачивать на 90-180 градусов. Давайте разберемся, для чего нужно такое редактирование и как его сделать.
Что такое разворот таблицы и для чего он нужен?
Разворот данных в таблице представляет собой перемешивание внесенной информации и расположение ее в обратном порядке в отличие от начального результата создания. При поворотах на 180 градусов левая часть таблицы меняется с правой, а нижняя строчка занимает верхнюю. Таким образом производится небольшая пересортировка материалов.
Если предусматривается разворот в половину меньше (т.е. на 90 градусов), тогда происходят не столь обширные изменения, последняя строка займет первое место, а первая, наоборот.
Основная функция данного способа редактирования — выставить все данные в нужном порядке, а также выделить главные ячейки в верхней части таблицы, а второстепенные опустить ниже или сместить. Такое форматирование в Excel применяется на практике в больших фирмах, когда в первую очередь нужно учитывать главные сумы затрат и доходов.
Первый способ: разворот таблицы в Excel на 180 градусов
Чтобы все данные в таблице вместе с подписанными ячейками изменили свое положение и были развернуты на 180 градусов, необходимо выполнить несколько простых шагов, но для начала создать ту самую таблицу.
- Первое — установить порядковый номер к каждой строчке таблицы. Для этого справа от первой строки ставим цифру «1», а затем направляем курсор мыши на уголок ячейки, чтобы преобразовать его в маркер в виде «крестика». Зажимаем клавишу CRTL и протягиваем мышь до последней строки в таблице.
- Обратите внимание, что все числа выставились на листе по порядку. Теперь выделяем весь столбец, переходим во вкладку «Главная», находим инструмент «Сортировка», где в выпадающем списке отмечаем пункт «Настраиваемая сортировка».
- Перед вами открывается окошко мастера сортировки. Его нужно заполнить соответствующим образом. В поле «Сортировать по» выбираем заголовок того столбца, в котором мы проставили порядковую нумерацию. Поле сортировка остается без изменений (если в нем по умолчанию установлены «Значения»). Далее редактируем поле «Порядок», в котором указываем «По убыванию». Нажимаем копку «ОК» для сохранения.
- Сортировка произведена и все данные разместились в обратном порядке. При этом все заголовки остались на своем месте.
Если ваша таблица закреплена на листе Excel, ее необходимо открепить, прежде чем разворачивать. Иначе это действие обернется системной ошибкой в документе.
Второй способ: разворот таблицы на 90 градусов
Трансформация таблицы на 90 градусов тоже не подразумевает никаких сложностей, если в расчетах не допущены ошибки. Если таковые имеются, они впоследствии будут видны в ячейках. Исправьте их, и приступайте к развороту.
- Первым делом выделим диапазон всей таблицы. Которая подлежит преобразованию. После чего нажмем кнопку копировать в «Буфере обмена» или воспользуемся комбинацией клавиш CTRL+C.
- Под первым столбиком таблицы выбираем свободную ячейку с дополнительным незанятым пространством. Теперь кликаем по ней правой кнопкой мыши. Перед вами появляется список допустимых действий, где в инструментах вставки выбираем «Транспортировать».
- В случае, если соответствующий инструмент не найден, выполним другие действия. Вызываем снова меню и переходим в пункт «Специальная вставка». Здесь кликаем на стрелочку вправо, появляется следующий список, снова выбираем «Специальная вставка».
- На экране высветится дополнительное окошко, в котором необходимо установить флажок напротив слова «Транспортировать». Затем нажать кнопку «ОК».
Ваша таблица отобразится в развернутом виде, то есть строки переместятся на место столбцов, а столбцы, наоборот. Если в некоторых ячейках наблюдаются ошибки и некорректные числа, значит в оформлении участвовали формулы.
Если в электронной таблице были данные, вычисленные при помощи формул, то они могут отображаться некорректно. Чтобы этого избежать перед разворотом преобразуйте существующие данные в числовые значения.
Тест по теме «Разворот таблицы в Excel: способы, которые не займут много времени»
-
Как нарисовать таблицу
-
Как отфармотировать таблицу
-
Как продолжить таблицу
-
Как перевернуть таблицу
-
Как сортировать таблицу
-
Как напечатать таблицу
-
Что ещё?
6 Основных приёмов в работе с таблицами
Как строить таблицы в Excel
В этой статье мы затронем самые частые темы, которые встречаются при работе с таблицами excel. Мы рассмотрим, как нарисовать таблицу в excel, как транспонировать (перевернуть) таблицу в excel, покажем, как сортировать таблицу в excel, а также как продлить, отформатировать и напечатать.
Работа в Microsoft Excel во многом состоит из построения, оформления и вычисление таблиц.
Практически каждый, кто открывает Excel сталкивается с задачами создания, оформления, преобразования, сортировки и печати таблиц.
В этой статье мы затронем самые частые темы, которые встречаются при работе с таблицами excel. Мы рассмотрим, как нарисовать таблицу в excel, как транспонировать (перевернуть) таблицу в excel, покажем, как сортировать таблицу в excel, а также как продлить, отформатировать и напечатать.
Как нарисовать таблицу в эксель
Нарисовать таблицу в экселе можно многими способами, мы рассмотрим самые простые:
Воспользоваться сеткой.
Т.е. на вкладке «Главная», открыть «Границы» и выбрать пункт «Нарисовать сетку». После этого курсор примет изображение карандаша. Этим карандашом Вы можете вести по диагонали превращая обычные ячейки в таблицу.
как начертить таблицу в excel
Применить все границы.
Если в Вашей таблице уже заполнены данные, то можно сделать просто обрамление таблицы excel границами.
Для этого выделите диапазон Вашей таблицы, потом зайдите на вкладке «Главная» в «Границы» и там выберете «Все границы»:
как строить таблицы в экселе
Как отформатировать таблицу.
Пожалуй, самый эффектный способ оформить таблицу в excel – это сделать форматирование таблицы.
Для этого нужно поместить курсор в любое место Вашей таблицы и на вкладке «Главная» выбрать «Форматировать как таблицу», далее применить любую из готовых цветовых схем:
Как отформатировать таблицу в excel
Таким образом меняется не только форматирование, создается так называемая «умная таблица». Такую таблицу очень удобно продолжить вниз или растянуть.
Как продолжить таблицу в эксель вниз.
Проще всего в экселе продолжить таблицу вниз, если включен режим таблицы. Часто этот режим называют «умной таблицей». Этот режим включается горячими клавишами Ctrl+T или нажатием значка «Таблица» на вкладке «вставка». Ещё один способ описан в предыдущем пункте «Отформатировать таблицу». После включения умной таблицы продлить таблицу в excel можно просто начав заполнять первую строку под ней. Заполнение любой ячейки позволит в экселе продлить таблицу вниз. Теперь Вы знаете, как продолжить таблицу в excel самым простым способом.
Как в excel продлить таблицу вниз
Как растянуть таблицу в excel.
Аналогично в excel можно растянуть таблицу вправо. Для этого нужно заполнить заголовок первого столбца справа от таблицы.
Как растянуть таблицу в эксель
Как отменить умную таблицу в excel.
Все возможности умной таблицы мы обязательно разберем отдельной статьей, здесь мы расскажем, как отменить умную таблицу в excel.
Для этого на любом месте сводной таблицы нужно будет нажать правой клавишей мыши, зайти в раздет «Таблица», а там выбрать «Преобразовать в диапазон».
Как перевернуть таблицу в excel.
В этом разделе мы выясним можно ли в экселе перевернуть таблицу.
Чтобы в экселе перевернуть таблицу горизонтально, нужно сначала выделить и скопировать ту таблицу, которую нужно перевернуть, а затем воспользоваться функцией транспонирование таблицы.
Для этого нужно выделить ячейку в которую нужно перевернуть таблицу горизонтально, нажать на «Правую клавишу мыши», выбрать «Специальная вставка», в правом нижнем углу диалогового окна специальной вставки отметить флажок «Транспонировать», нажать «Ok».
Таким образом в пару кликов можно развернуть таблицу в excel на 90 градусов.
Как в экселе транспонировать таблицу.
Как сортировать таблицу в excel.
Чтобы отсортировать таблицу в excel по возрастанию достаточно поместить курсор внутрь столбца по которому требуется упорядочить таблицу в excel, и на вкладке «Данные» в разделе «Сортировка и фильтры» выбрать порядок сортировки:
- отсортировать таблицу в excel по возрастанию
- отсортировать таблицу в excel по убыванию
Для сортировки таблицы по нескольким уровням в этом же разделе нужно открыть окно «Сортировка» и там настроить приоритеты сортировки.
Как отсортировать таблицу в экселе.
Как напечатать таблицу в excel.
Печать таблицы эксель заслуживает отдельного внимания, т.к. правильные настройки позволят Вам:
- настроить одинаковые заголовки для каждой страницы печати
- вписать всю таблицу на один лист
- вписать все столбцы таблицы на лист
Если у вас длинная таблица и необходимо, чтобы на каждом листе были одинаковые заголовки, то это проще всего сделать специальной настройкой. Для этого в excel нужно зайти на вкладке «Разметка страницы» в окно «Печатать заголовки», затем в строке «Выводить на печать диапазон» указать расположение Вашей таблицы, в поле «Сквозные строки» выберите строки с заголовками.
Чтобы предварительно посмотреть результат до печати таблицы excel, нажмите «Просмотр»:
Как напечатать таблицу в экселе.
В режиме просмотра, Вы можете сжать таблицу в excel для печати, так чтобы она помещалась на одном листе или, например вмещалась на листе по ширине. Для этого в режиме просмотра выберете в разделе «Масштабирование» вписать всю таблицу на один лист или вписать все столбцы таблицы на лист.
Как сжать таблицу в excel для печати.
В этой статье мы рассмотрели основные моменты в таблицах excel.
У нас есть online курс Умные и сводные таблицы, пройдя который вы получите практические навыки в обработке больших массивов данных в том числе с помощью сводных.
Содержание
- Процедура транспонирования
- Способ 1: специальная вставка
- Способ 2: применение функции
- Вопросы и ответы
Иногда встречаются ситуации, когда требуется перевернуть таблицу, то есть, поменять строки и столбцы местами. Конечно, можно полностью перебить все данные, как вам нужно, но это может занять значительное количество времени. Далеко не всем пользователям Excel известно о том, что в данном табличном процессоре имеется функция, которая поможет автоматизировать эту процедуру. Давайте изучим подробно, как строки сделать столбцами в Excel.
Процедура транспонирования
Смена местами столбцов и строчек в Экселе называется транспонированием. Выполнить данную процедуру можно двумя способами: через специальную вставку и с применением функции.
Способ 1: специальная вставка
Выясним, как транспонировать таблицу в Excel. Транспонирование с помощью специальной вставки является самым простым и популярным видом переворота табличного массива у пользователей.
- Выделяем всю таблицу курсором мышки. Кликаем по ней правой кнопкой. В появившемся меню выбираем пункт «Копировать» или просто жмем на клавиатуре сочетание Ctrl+C.
- Становимся на том же или на другом листе на пустую ячейку, которая должна будет стать верхней левой ячейкой новой копируемой таблицы. Кликаем по ней правой кнопкой мыши. В контекстном меню переходим по пункту «Специальная вставка…». В появившемся дополнительном меню выбираем пункт с таким же наименованием.
- Открывается окно настройки специальной вставки. Устанавливаем галочку напротив значения «Транспонировать». Жмем на кнопку «OK».
Как видим, после этих действий исходная таблица была скопирована в новое место, но уже с перевернутыми ячейками.
Затем, можно будет удалить первоначальную таблицу, выделив её, кликнув курсором, и в появившемся меню выбрав пункт «Удалить…». Но можно этого и не делать, если она не будет вам мешать на листе.
Способ 2: применение функции
Второй способ переворачивания в Экселе предполагает применения специализированной функции ТРАНСП.
- Выделяем область на листе, равную по вертикальному и горизонтальному диапазону ячеек исходной таблице. Кликаем по значку «Вставить функцию», размещенному слева от строки формул.
- Открывается Мастер функций. В списке представленных инструментов ищем наименование «ТРАНСП». После того, как нашли, выделяем и жмем на кнопку «OK».
- Открывается окно аргументов. У данной функции только один аргумент – «Массив». Ставим курсор в его поле. Вслед за этим, выделяем всю таблицу, которую хотим транспонировать. После того, как адрес выделенного диапазона записан в поле, жмем на кнопку «OK».
- Ставим курсор в конец строки формул. На клавиатуре набираем сочетание клавиш Ctrl+Shift+Enter. Это действие нужно для того, чтобы данные корректно были преобразованы, так как мы имеем дело не с одной ячейкой, а с целым массивом.
- После этого программа выполняет процедуру транспонирования, то есть меняет в таблице столбцы и строки местами. Но перенос был сделан без учета форматирования.
- Форматируем таблицу так, чтобы она имела приемлемый вид.
Особенностью данного способа транспонирования, в отличие от предыдущего, является то, что изначальные данные удалять нельзя, так как это приведет к удалению транспонированного диапазона. Более того, любые изменения первичных данных приведут к такому же изменению их в новой таблице. Поэтому, этот способ особенно хорош для работы со связанными таблицами. В то же время, он существенно сложнее первого варианта. Кроме того, при использовании данного способа необходимо сохранять исходник, что не всегда является оптимальным решением.
Мы выяснили, как поменять местами столбцы и строки в Excel. Существует два основных способа перевернуть таблицу. Каким из них воспользоваться зависит от того, планируете ли вы применение связанных данных или нет. Если таких планов не имеется, то рекомендуется использовать первый вариант решения поставленной задачи, как более простой.
Еще статьи по данной теме:
Помогла ли Вам статья?
На чтение 5 мин Опубликовано 26.01.2021
Транспонирование таблиц – функция, о которой знают не все пользователи Microsoft Excel. Она предназначена для случаев, когда необходимо изменить ориентацию диапазона ячеек с вертикальной на горизонтальную или наоборот. Программа также позволяет повернуть один столбец так, чтобы данные шли в обратном порядке – это пригодится, если информация занесена неправильно. Выясним, как поменять расположение данных на листе без необходимости переписывать все целиком.
Содержание
- Транспонирование таблицы Excel специальной вставкой
- Как перевернуть таблицу с помощью функции
- Транспонирование с использованием сводной таблицы
- Как перевернуть один столбец функцией
- Заключение
Транспонирование таблицы Excel специальной вставкой
Бывают ситуации, когда в таблицу уже занесены все данные, но нужно было оформить содержимое шапки по вертикали. Для этого можно воспользоваться инструментом «Специальная вставка» – в его меню содержится несколько настроек, позволяющих регулировать вставку таблицы на новое место. Возьмем любую таблицу, где есть шапка, и испробуем на ней специальную вставку.
- Выделим диапазон ячеек целиком и копируем его с помощью комбинации клавиш «Ctrl+C». Можно также кликнуть правой кнопкой мыши и выбрать в контекстном меню пункт «Копировать».
- На новом листе или в другой части того же листа находим свободное место, куда нужно вставить таблицу. Нажимаем правую кнопку мыши, поместив курсор на одну из ячеек, и выбираем пункт «Специальная вставка». В некоторых версиях Microsoft Excel при наведении на этот элемент меню сразу открываются опции, но нужно найти в нижней части дополнительного меню пункт «Специальная вставка» и кликнуть по нему.
- Диалоговое окно содержит несколько опций, позволяющих вставить отдельные данные из таблицы на новое место. Поставим галочку в графе «Транспонировать» и нажмем «ОК».
- Теперь шапка таблицы находится в первом столбце, а данные оттуда переместились в верхнюю строку. На такой таблице также хорошо заметна разница между объемами продаж.
Как перевернуть таблицу с помощью функции
Мастер функций содержит формулу, с помощью которой можно поменять строки и столбцы в таблице местами. Придется снова скопировать данные и вставить их в другой диапазон ячеек, чтобы увидеть изменения.
- Переходим в «Параметры» и в разделе «Работа с формулами/Формулы» ставим галочку в графе «Стиль ссылок R1C1». Это понадобится в будущем для корректной работы с формулой. Если этот шаг не будет пройден, не получится добавить массив данных в функцию.
- Определяем место, где будет находиться измененная таблица и добавляем функцию ТРАНСП в первую ячейку. Формулу можно найти в Мастере функций или записать вручную – потребуется только буквенное обозначение и круглые скобки.
Обратите внимание! Если используете меню Мастер функций для поиска формул, следует открыть категорию «Ссылки и массивы». Записывая формулу вручную, обязательно ставьте в начале знак равенства.
- Переходим на лист с начальной таблицей и выделяем ее, чтобы диапазон ячеек попал в формулу. После нажимаем «ОК». Таблица пока не перенесена полностью, но это нормально – требуется еще два шага.
- На листе с функцией выделяем ячейки в таком соотношении, в каком они окажутся после переворота. Например, в таблице-примере 7 строки и 4 столбца – это значит, что требуется выделить 4 строки и 7 столбцов.
- Воспользуемся комбинацией клавиш, чтобы формула подействовала. Сначала нажмите F2 – если на эту кнопку установлены функции, найдите клавишу, останавливающую их срабатывание, и зажмите ее. Формула в ячейке развернется. Далее быстро нажмите три клавиши: Ctrl, Shift, Enter. В выделенном диапазоне появится перевернутая таблица.
Транспонирование с использованием сводной таблицы
Составление сводных таблиц помогает во многих случаях при использовании Excel. Эта опция пригодится и при транспонировании. С помощью сводной таблицы можно только переместить столбцы на место строк, но не наоборот. У такого метода есть и преимущества – пользователи могут отфильтровать данные в таблице.
- Выделяем курсором исходный диапазон ячеек.
- Переходим на вкладку «Вставка» на панели инструментов и находим кнопку «Сводная таблица».
- Настройте формирование сводной таблицы в диалоговом окне. Там указывается диапазон ячеек – он уже выбран – и место вставки. Выберите новый лист, чтобы без затруднений управлять новым диапазоном данных.
- Распределяем поля в списке по категориям. Содержимое столбцов должно относиться к строкам, и наоборот.
- Открываем вкладку «Конструктор» и отключаем общие и промежуточные итоги для строк и столбцов.
- В результате получается таблица с теми же значениями, но строки и столбцы изменили свое положение.
Как перевернуть один столбец функцией
Если в таблице Excel есть столбец, в котором значения располагаются в обратном порядке, его можно перевернуть с помощью Мастера функций. Существует две формулы, помогающие развернуть столбец «вверх ногами».
- Вставляем в ячейку, с которой начнется перевернутый столбец, функцию СМЕЩ или ИНДЕКС. Можете воспользоваться этими шаблонами, но не забудьте вставить значения, соответствующие расположению ячеек в вашей таблице: =СМЕЩ($A$8;(СТРОКА($A$2)-СТРОКА());) или =ИНДЕКС($A$2:$A$8;ЧСТРОК(A2:$A$8)).
- Для примера воспользуемся функцией СМЕЩ – добавим ее в ячейку и нажмем Enter.
- Зажимаем маркер заполнения – черный квадрат в правом нижнем углу ячейки – и вытягиваем выделение до конца столбца. Все ячейки будут заполнены в соответствии с планом, то есть в обратном порядке.
- Результат заполнения столбца с помощью функции ИНДЕКС выглядит так:
Важно! Не обязательно, чтобы столбцы находились рядом, ссылки в формулах все равно ведут к нужным ячейкам. Это знание пригодится, чтобы составить перевернутый столбец вне цельной таблицы.
Заключение
Большинство методов переворота таблиц, или транспонирования, работают в обе стороны. Пользователь может сделать вертикально ориентированный диапазон ячеек горизонтальным или сделать шапкой названия строк. Для двухстороннего транспонирования не подходят только сводные таблицы. Наиболее простой способ повернуть таблицу – применение специальной вставки.
Оцените качество статьи. Нам важно ваше мнение:
Всем здравствуйте. В сегодняшнем коротком уроке я расскажу вам как перевернуть таблицу в Excel. Мы рассмотрим как вариант транспонирования на 90 градусов, так и полный переворот на 180 градусов. Уважаемые читатели и гости нашего курса, помните – вы всегда можете задать свой вопрос в комментариях. Кто-нибудь из преподавателей вам обязательно поможет.
Содержание
- Способ 1: На 90 градусов
- Способ 2: На 180 градусов
- Видео
- Задать вопрос автору статьи
Способ 1: На 90 градусов
В первом варианте я расскажу вам, как повернуть таблицу в Эксель на 90 градусов. Для этого мы воспользуемся копированием и специальной функцией вставки.
- Полностью выделите таблицу с помощью зажатой левой кнопки мыши.
- Далее скопируйте её в буфер обмена. Это можно сделать с помощью горячих клавиш:
Ctrl + C
- Или использовать контекстное меню – нажимаем правой кнопкой мыши по выделению и выбираем команду «Копировать».
- Выберите любую пустую ячейку под уже существующей табличкой, там где мы попробуем её перевернуть. Нажимаем правой кнопкой и из дополнительного меню выбираем команду «Специальная вставка» – далее находим функцию «Транспонировать».
- После этого вы увидите перевернутую табличку, где данные столбцов и строк поменяются местами. Плюс данного варианта еще и в том, что в таблице полностью сохраняется форматирование.
- Если вам нужно удалить старую табличку, выделите её вместе с пустыми ячейками сверху второй перевернутой таблицы.
- Теперь на вкладке «Главная» найдите функцию «Удалить», откройте дополнительное меню и выберите функцию «Удалить строки с листа».
- Вот такой красивый результат у нас получился.
В Эксель есть еще один вариант горизонтальную таблицу сделать вертикальной (и наоборот) – это использовать специальную формулу. Так как этот вариант более сложный и им редко кто пользуется, я решил его упомянуть вскользь. Сама функция будет иметь вид:
=ТРАНСП(Массив)
В качестве массива можно указать диапазон ячеек, как по вертикали, так и по горизонтали. Например, в моем случае вид будет таким:
=ТРАНСП(A1:D7)
В качестве домашнего задания можете попрактиковаться с этой формулой и попробовать её применять для разных табличек. Мы же на ней не будет заострять свое внимание. Просто помните, что и такой вариант тоже есть.
Давайте посмотрим, как нам в Excel перевернуть столбец (или целую таблицу) снизу вверх. Для этого мы воспользуемся небольшой хитростью – мы будем использовать дополнительный столбец, цифры и сортировку.
- Сбоку в первой пустой ячейке поставьте цифру 1 (один).
- Зажмите клавишу:
Ctrl
- Наведите на маркер автозаполнения и растяните его до конца этой таблицы.
Читаем – что такое маркер автозаполнения и как им пользоваться.
- Все ячейки равномерно должны пронумероваться от одного до номера последней строки. Проверьте, чтобы эти ячейки были выделены.
- На «Главной» кликаем по функции «Сортировка и фильтр» – «Настраиваемая сортировка».
- Убедитесь, что выделена конфигурация «Автоматически расширить выделенный диапазон.
- Сначала уберите галочку «Мои данные содержат заголовки». Далее в свойстве «Столбец» – укажите ту букву столбца, в которой находятся цифры. В «Сортировке» указываем «Значения ячеек». И в конце указываем «Порядок» – «По убыванию».
- Как только вы нажмете «ОК» табличка перевернется с ног на голову. Вам останется удалить столбец с числами, которые мы ранее добавили.
Вот и все. Как видите, перевернуть таблицу не так сложно, как кажется на первый взгляд. До новых встреч на портале WiFiGiD.RU.
Видео
Просмотров: 432
Источник проблемы.
В ходе работы довольно часто встречаются ситуации, когда необходимо выполнить разворот таблиц определенным образом, в том числе разворот в обратном порядке столбцов.. Такой разворот таблиц может, к примеру, понадобиться, если требуется выполнить сортировку ячеек, расположенных по горизонтали.
Приведем пример. Мы имеем таблицу следующего вида:
Цена, а значит и сумма заказа по каждой позиции зависит от объема, то есть от количества заказанных единиц товара. В этом случае можно было бы применить функцию ГПР, но тогда цены должны быть указаны в порядке их повышения. Здесь же ситуация состоит с точностью до наоборот.
Транспонирование.
Наверное, это самый простой вариант решения проблемы. Необходимо скопировать нужную таблицу. После этого выбрать ячейку рядом и в специальной вставке выбрать вариант «транспонирование». В последних версиях Excel этот пункт вынесен в виде пиктограммы непосредственно в контекстное меню.
В результате мы переворачиваем таблицу на 90 градусов, и ее столбцы и строки поменяются местами.
Как это использовать для переворачивания исходных столбцов? Очень просто! После транспонирования надо пронумеровать строчки с нужными данными. В нашем случае это выглядит так.
После этого выделяем нужные строки вместе с добавленной нами нумерацией, и запускаем с вкладки «Данные» настраиваемую сортировку.
В появившемся окне убираем, если стоит, галочку «мои данные содержат заголовки», в качестве первого уровня показываем столбец с нашей нумерацией и выбираем вариант сортировки по убыванию.
Осталось скопировать полученный результат, но без добавленного столбца с нумерацией и с помощью транспонирования вставить его на исходное место. После этого промежуточную таблицу можно удалить.
Теперь наша таблица уже выглядит следующим образом.
Остается заметить, что, если надо поменять в обратном порядке непосредственно строки, то выполнять транспонирование не требуется.
Преимуществом данного способа является прежде всего простота и доступность, а также отсутствие формул и функций. Однако метод имеет и недостатки. Прежде всего это трудоемкость. Кроме этого, имеется ограничение на количество столбцов. На листе Excel имеется только 16384 строки. Поэтому если у вас в таблице строк больше, а я сталкивался с таблицами и в 20000 строк, и в 200000 строк, и более чем в миллион строк, так вот в таком случае способ бесполезен и все-таки придется использовать формулы и функции
Используем для переворачивания функцию ИНДЕКС.
Наверное, это самый простой вариант для разворота таблицы в обратную сторону средствами MS Excel без предварительного транспонирования, который я смог найти на просторах интернета. Суть его состоит в том, что в качестве диапазона указываем строку, в которой надо поменять столбцы местами. Номер строки задаем равной 1, а в качестве номера столбца используем дополнительную функцию ЧИСЛСТОЛБ, в которой указываем заново этот же диапазон для разворачивания, но в адресе последней ячейки в ней закрепляем только столбец, используя смешанную адресацию
Данный способ, точнее данная составная функция, проще, чем следующие варианты из сети Интернет:
=ИНДЕКС (A$1:A$6;ЧСТРОК (A$1:A$6)+СТРОКА (A$1)-СТРОКА ()) =СМЕЩ ($A$6;(СТРОКА ()-СТРОКА ($A$1))*-1;0)
=ИНДЕКС ($A$1:$A$6;СЧЁТЗ ($A$1:$A$6)-СТРОКА ()+1)
=ИНДЕКС (A:A;СЧЁТЗ (A:A)-СТРОКА ()+1)
=ИНДЕКС (A$1:A$6;7-СТРОКА ()) {=ИНДЕКС ($A$1:$A$6;НАИБОЛЬШИЙ (СТРОКА ($A$1:$A$6)*ЕЧИСЛО ($A$1:$A$6);СТРОКА (1:1)))} это формула массива завершается ввод нажатием Ctrl+Shift+Enter.
В отличии от всего этого мы использовали только две функции. Функция ИНДЕКС извлекает значение из диапазона на пересечении указанных по номеру в таблице строки и столбца. Строка была только одна, поэтому мы и написали единицу. Номер же столбца мы нашли с помощью функции ЧИСЛСТОЛБ. Вначале она показала номер последнего столбца, а затем из=-за примененной адресации этот номер стал уменьшаться и в итоге столбцы стали разворачиваться.
Учтите, что если надо было бы поменять местами строки, то надо уже указать диапазон из одного столбца. В формуле вместо единицы на месте номера строки надо было бы указать функцию ЧСТРОК и, выделив диапазон, в адресе последней ячейки уже закрепить только строку, оставив знак доллара только перед ее номером.
Применяем функцию СМЕЩ.
В данном случае необходимо добавить сверху таблицы новую строку и пронумеровать в ней нужные столбцы в обратном порядке, начиная с нуля. Удобно это сделать с помощью простой формулы, как на рисунке. Первый нуль мы указываем, конечно, сами с клавиатуры.
Формула же для разворачивания столбцов в обратном порядке будет уже выглядеть следующей. Мы будем использовать функцию СМЕЩ. В качестве исходного диапазона зададим последнюю ячейку в текущей обрабатываемой строке с закреплением только столбца, перемещение по строкам зададим равным 0, а смещение по столбцам сделаем равным первой ячейке в добавленной нами сверху строке, то есть нулю, и закрепим в адресе этой ячейке номер строки.
Результат перед вами. И обратите внимание на адресацию!
Действия после разворачивания таблицы и итоги работы.
Теперь, когда таблица развернута в противоположном по столбцам направлении, уже можно применить в частности функцию ГПР.
В данном случае она будет выглядеть так.
Более подробно о функциях ВПР и ГПР мы поговорим на следующих уроках.
Наше же текущее занятие подошло к концу. Желаю всем безошибочной работы и профессионализма на практике. Помните, что многие действия могут быть выполнены гораздо проще, чем кажется вначале. Не зря еще дедушка Крылов обронил в свое время фразу: «На мудреца довольно простоты … А ларчик просто открывался…
Всем успехов и уверенности в своих силах. Экспериментируйте, будьте аккуратными и внимательным и все будет получаться и становиться простым и легким!.
Переворачиваем с помощью сортировки
Для применения данного метода не требуется формул. Более того, сами перемещаемые пр развороте столбцы остаются в пределах своего диапазона, что так же является значитпельным плюсом. К тому же данный спгособ позволяет не только развернуть таблицу слева гнаправа, но и вывести колонки в любом порядке. Все очень просто. ПРедположим, нам надо развернуть стобцыв в таблице, рассмотренной выше. Для этого:
добавляем строку с нужным порядком столбцов. Ее можно добавить как выше заголовка таблицы, так и под ней. Выберем первый вариант.
Выделяем нужные столбцы вместе с добавленной номинацией и запускаем настраиваемую сортировку. В частности, это можно сделать с вкладки «Данные». Заходим в параметры и а выбираем вариант «Сортировать по столбцам диапазона»
Возвращаемся в настройку сортировки. В пункте «Сортировать по» указываем строку с заданным на первом этапе порядком столбцов и нажимаем ОК
Готово! Согласитесь, простой способ?
