В электронной таблице (Excel, Google Docs, OpenOffice) как перенести данные из строк в столбцы, но только в ненулевые ячейки?

5841
NoCatharsis

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

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

Например, если у меня есть строка со значениями «A», NULL, «C», «D» в последовательных ячейках, я хочу транспонировать так, чтобы новый столбец отображался «A», «C», «D» с нет пустой ячейки для NULL.

Надеюсь, что мое описание носит описательный характер. Благодарю.

2

5 ответов на вопрос

2
JDB

NoCatharsis,

Простой ответ, который у меня есть для вас, относится к Excel (у Документов Google не было похожих фильтров, а в Open Office Calc копирует отфильтрованные ячейки):

1) Используйте «Автофильтр» («Данные»> «Фильтр»> «Автофильтр») и отфильтруйте незаполненные поля.

2) Скопируйте оставшиеся ячейки

3) Специальная вставка (Правка> Специальная вставка) и выберите «Транспонировать» из опций

JDB

1
Tim Lara

Я не могу придумать способ сделать это без макропрограммирования, поэтому я не уверен насчет Google Docs или OpenOffice, но вот один из способов сделать это в Excel.

Этот макрос предполагает:

  1. Ваши данные находятся на отдельном листе с именем "Sheet1"
  2. Ваш пункт назначения - чистый лист с именем "Sheet2"

Чтобы использовать макрос, выберите строки, которые вы хотите транспонировать на Листе 1, затем выполните макрос с выделенными строками. Результаты должны быть скопированы в Sheet2.

Sub CopyNonNulls()  Dim DestinationCellAddress As String DestinationCellAddress = "A1"  Dim CurrentCellAddress As String CurrentCellAddress = DestinationCellAddress  For Each rw In Selection.Rows  For Each c In rw.Cells  If c.Value <> "" Then Worksheets("Sheet2").Range(CurrentCellAddress).Value = c.Value CurrentCellAddress = Worksheets("Sheet2").Range(CurrentCellAddress).Offset(1, 0).Address End If  Next  CurrentCellAddress = Worksheets("Sheet2").Range(DestinationCellAddress).Offset(0, 1).Address  Next  Worksheets("Sheet2").Select  End Sub 
1
AdamV

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

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

Теперь у вас есть колонка с пробелами.

В ячейке над ней дайте столбцу метку, скажем «данные». Сделайте его жирным (Ctrl-B), чтобы Excel обнаружил, что это заголовок, хотя он и текст, как и остальная часть столбца.

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

Перейдите в раздел «Лента данных»> «Группа сортировки и фильтрации» и нажмите «Расширенный фильтр».

Галочка "Копировать в другое место"

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

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

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

Нажмите ОК.

Работа выполнена. Теперь у вас есть новый набор непрерывных данных, и вы можете удалить другие столбцы.

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

1

Вы можете сделать это с помощью функции TRANSPOSE в Google Spreadsheets. Их объяснение о том, как это сделать, можно найти здесь: http://docs.google.com/support/bin/answer.py?hl=ru&answer=71291.

0
jmarston

Используйте формулу в новом наборе ячеек и столбцов, как =A270 | =A272 | =A274, затем заполните.

Он автоматически переставляет этот столбец, например, А, затем копирует и вставляет значения на новый лист.

Похожие вопросы