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

Question

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

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

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

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

Answer 1

NoCatharsis,

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

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

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

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

JDB

Answer 2

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

Answer 3

Я не могу придумать способ сделать это без макропрограммирования, поэтому я не уверен насчет 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

Answer 4

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

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

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

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

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

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

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

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

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

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

Нажмите ОК.

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

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

Answer 5

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

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