Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как переместить или преобразовать заголовки столбцов в заголовки строк в Excel?

Повышение или понижение уровня заголовков столбцов

Когда вы создаете новый запрос из неструктурированных источников данных, таких как текстовые файлы, Power Query анализирует содержимое файла. Если Power Query определяет другой шаблон для первой строки, он пытается переместить данные из первой строки в заголовки столбцов таблицы. Однако Power Query может ошибочно определить шаблон в 100% случаев, поэтому в этой статье объясняется, как вручную поднять или опустить заголовки столбцов в строках.

Повышение уровня строк до заголовков столбцов

В следующем примере Power Query заголовки столбцов таблицы определить не удалось, поскольку таблица содержит набор заголовков для первых трех строк. Заголовки столбцов таблицы находятся в строке 5.

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

В таблице с четырьмя колонками (Column1, Column2, Column3 и Column4) использован текстовый тип данных с семью строками, состоящими из заголовка сверху, заголовка колонки посередине и семи строк данных снизу.

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

Удаление верхних строк из образца таблицы.

В окне Удалить верхние строки введите 4 в поле Число строк .

Удалить окно с верхними строками.

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

Результатом операции будут заголовки в виде первой строки таблицы.

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

Расположения операции продвижения заголовков

Здесь у вас есть несколько мест, где вы можете выбрать для увеличения работы заголовка:

На вкладке Главная в группе Конвертирование .

Кнопка продвижения заголовков на вкладке Главная.

На вкладке Преобразование в группе Таблица.

Кнопка повышения заголовков на вкладке преобразование.

Кнопка повышения заголовков в контекстном меню.

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

Последняя таблица с операцией Promote Headers.

Таблица со столбцами даты, страны, общего количества единиц и общего дохода. Заголовок каждого столбца содержит тип данных Дата, Страна, Целое число и Десятичная дробь. Кроме того, заголовок столбца Total Units содержит тип данных Integer, а заголовок столбца Total Income — тип данных Decimal.

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

Понизить заголовки столбцов до строк

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

Читайте так же:
Как преобразовать валюту в текст слов в Excel?

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

Расположение операции понижения заголовков

Вы можете выбрать варианты понижения заголовков в нескольких местах:

На вкладке «Главная» в группе «Конвертация».

Кнопка понижения заголовков на вкладке Главная.

На вкладке Преобразование группы Таблица .

Кнопка понижения заголовков на вкладке преобразование.

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

Таблица после понижения заголовков до строк с заголовками столбцов теперь заданы значения Столбец1, Столбец2, Column3 и Column4.

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

Окончательная таблица после переименования заголовков столбцов.

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

Включение и отключение заголовков таблицы Excel

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

Пример данных в формате таблицы Excel

При создании таблицы можно использовать первую строку данных в качестве строки заголовка, установив флажок «Заголовок таблицы»:

Пример использования команды "Форматировать как таблицу" на вкладке "Главная" для автоматического выбора диапазона данных

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

Таблица Excel с данными в заголовке; флажок "Таблица с заголовками" не установлен, поэтому Excel добавил стандартные имена, такие как "Столбец1", "Столбец2".

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

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

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

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

Хотя в формулах можно ссылаться на отключенные заголовки, они могут быть не выделены, чтобы на них можно было ссылаться. Скрытые ссылки на заголовки таблиц возвращают нулевые значения (0), но они остаются неизменными и вернут свои значения при повторном отображении. Все другие ссылки на заголовки таблиц (например, ссылки на стиль A1 или RC) корректируются, когда заголовок отключен, что приводит к тому, что формулы возвращают неожиданные результаты.

Читайте так же:
Как очистить указанное содержимое ячейки при изменении значения другой ячейки в Excel?

Отображение и скрытие строки заголовков

Щелкните в любом месте таблицы.

Выберите Рабочий лист > Построитель.

Чтобы заголовки отображались, установите флажок Строка заголовка в группе Параметры стиля таблицы.

Изображение вкладки "Работа с таблицами" на ленте, которая появляется при выборе ячейки таблицы

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

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

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

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

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

Хотя на отключенные заголовки можно ссылаться в формулах, на них нельзя ссылаться, выделяя их. В скрытых ссылках заголовков таблиц появятся нулевые значения (0), однако они останутся неизменными и вернут свои значения при повторном открытии. Помимо ссылок на стиль A1 или RC, все остальные ссылки на заголовки таблиц корректируются, когда заголовок отключен, и это может привести к неожиданным результатам формул.

Отображение и скрытие строки заголовков

Нажмите любое место в таблице.

Перейдите к вкладке «Таблица» на ленте.

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

Изображение вкладки "Работа с таблицами" на ленте, которая появляется при выборе ячейки таблицы

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

Отображение и скрытие строки заголовков

Нажмите в любом месте таблицы.

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

На вкладке Конструктор таблиц выберите параметры стиля для строки с заголовком.

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

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

Читайте так же:
Как переместить последний символ в начало ячейки или другого столбца в Excel?

Как переместить или преобразовать заголовки столбцов в заголовки строк в Excel?

Мне всегда казалось странным, что Excel не поддерживает регулярные выражения (которые в кругах программистов называются регулярными выражениями или регексами). Они используются для выполнения сложной сортировки и фильтрации. Плагин SeoTools для Excel поддерживает регулярные выражения, но, как и большинство надстроек для Excel, он работает только на платформе ПК. Эта статья предназначена в первую очередь для пользователей Mac (мы не рекламируем SeoTools).

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

Немного теории

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

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

Подготовка к работе с расширенным фильтром

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

Расположен расширенный фильтр:

Для вашего компьютера: Данные > Сортировка и фильтр > Дополнительно.

Для Mac: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр

Пользователи Mac могут щелкнуть по таблице с ПК и выбрать нужный элемент из контекстного меню. На ПК это невозможно.

Небольшой совет

Базовые операторы

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

  • =равны, т.е. левая и правая части должны совпадать: =ticket [включает все ячейки, содержащие билет]
  • <> не равны, левая и правая части не должны совпадать: <> https://www.aviasales.ru/mag* [исключает этот адрес (блог)]
  • ‘ апостроф превращает формулу в текст, если поместить его в ячейку первым: ‘=buy a ticket
  • > greater than : >500
  • >= больше или равно: B4-C4>=3
  • < меньше, чем: C6
Читайте так же:
Как перенести ячейки в один столбец на основе уникальных значений в другом столбце?

Заголовки

Набор условий

Здесь представлены три основные модели для нескольких условий:

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

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

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

В этом примере выбираются строки, которые содержат слово «купить» в столбце Ключевое слово, а не слово «аэроплан», и где значение в Item больше 7 и значение в Monthly Average больше 1000. Помните, что звездочка обозначает любое количество символов. В нашем примере будут выбраны строки, содержащие слово «купить» в любой позиции ячейки. Обратите внимание, что результат получен для слова «аэроплан», «аэроплан» подходит.

О БА ОПЕРАТОРА: Вы можете продемонстрировать свои навыки, используя оба оператора для задания критериев.

Такая таблица условий несколько обескураживает. Тем не менее, этот фильтр отбирает только те строки, которые содержат слово «купить» или «аэрофлот» без слов «самолет» или «аэроплан»; и значение последнего столбца больше 1000.

Примечание: Чтобы получить текст вместо формул, просто поставьте апостроф перед операторами сравнения: ‘=to buy или ‘<>plane. На своем сайте Microsoft предлагает слишком сложный способ: заключите все выражение в двойные кавычки и поставьте перед ним знак равенства: .

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

Промежуток: Если вы хотите получить данные в пределах определенного промежутка, вы можете использовать следующую технику

Эти условия позволят выбрать ключевые слова со среднемесячным показателем от 3,700 до 10,000.

Формулы: Это очень мощный инструмент. Вместо таблицы критериев можно использовать формулы.

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

При использовании формул следует помнить о некоторых моментах:

  • Формула должна возвращать логическое значение FALSE или TRUE;
  • Заголовки над формулами должны отличаться от заголовков базы данных, можно использовать даже пустую строку;
  • Формула должна ссылаться на ячейки в первой строке под заголовками базы данных;
  • Ссылки на управляемые ячейки базы данных должны быть относительными: типа C4, с абсолютами, например, $C$4 — значение формулы должно быть статичным, т.е. всегда FALSE или всегда TRUE.
Читайте так же:
Как предотвратить сохранение, если конкретная ячейка в Excel пуста?

Общий алгоритм

Вот как работать с расширенным фильтром шаг за шагом (предполагается, что данные и условия уже подготовлены):

Шаг 1: Как упоминалось ранее, щелкните любую ячейку в подготовленном наборе данных и перейдите к ней:

Выберите Дополнительно для ПК, затем Сортировка и фильтр. Для Mac: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр.

Откроется меню. Это снимок экрана Excel 2013, но все элементы те же, что и на Mac.

2: Выберите местоположение для выбранных данных. Обычно имеет смысл определить «альтернативное местоположение», чтобы исходный набор данных оставался неизменным. Но выбор остается за вами.

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

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

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

Шаг 6: Если нужны только уникальные записи, установите этот флажок. К сожалению, большинство людей используют только эту функцию.

Шаг 7: Нажмите кнопку OK и посмотрите результат.

Шаг 8: Если список был отфильтрован на месте, вы можете сбросить фильтр, нажав кнопку Очистить, но это только для ПК. Этот элемент расположен над элементом Advanced. На Mac есть аналогичная функция «Очистить фильтр», но она недоступна. Еще один аргумент в пользу сброса фильтра. Чтобы сбросить фильтр, отмените действие — нажмите Command-Z или подключитесь к фильтру по умолчанию.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector