Конвертировать данные Excel

Question

Я провел опрос в Sharepoint и хочу преобразовать результаты во что-нибудь полезное в Excel 2003. Моя проблема в формате ответов на некоторые вопросы.

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

альтернатива А; # альтернатива Б; # уникальная альтернатива свободного текста
альтернатива А; # альтернатива С

Таким образом, все альтернативы, выбранные пользователем, находятся в одном столбце, разделенном знаком ;#

Что я хотел бы сделать, это преобразовать это в

альтернатива А = 2
альтернатива B = 1
альтернатива с = 1
уникальная альтернатива свободного текста = 1

Таким образом, будет легко преобразовать в график.

Как бы я делал это с наименьшим количеством ручной работы? Есть около 60-70 строк, и каждая строка имеет около 15-20 столбцов с этой проблемой.

Я думаю, мне нужно как-то посчитать альтернативы, разделенные знаком; #?

Если все остальное терпит неудачу, я думаю, я мог бы справиться с этим с кучей операторов IF

Answer 1

Вы можете использовать текст для столбцов с пользовательским разделителем ;# . (Примечание: вам может понадобиться идентифицировать разделители отдельно и сказать Excel, чтобы они treat consecutive delimiters as one). Это разбивает текст по строке.

Затем вы можете cut и paste special - transpose . Единственным недостатком этой процедуры является то, что вам нужно будет вставлять ячейки между строками, когда вы хотите paste special - transpose потому что вы не можете insert cells таким образом. Вы можете transpose их по-другому, а затем cut - insert cells чтобы сделать их так, как вы хотите.

Answer 2

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

=(SUM(LEN($B$6:$B$70))-SUM(LEN(SUBSTITUTE($B$6:$B$70,B1,""))))/LEN(B1)

Это должно быть введено как формула массива, с Ctrl - Shift - Enter

Это предполагает, что ваши ответы находятся в B6:B70. Это чувствительно к регистру, поэтому в ваших критериях примера не будет подобрано строчных букв c.

Answer 3

Это не самый красивый или, казалось бы, самый элегантный, но он прост и будет отлично работать:

1. Положите все ваши результаты в столбце.
2. Поместите все возможные ответы в верхней части столбцов рядом с результатами.
3. Используйте простую формулу для поиска в столбце результатов значения в заголовке столбца. (Я использовал SEARCH() для поиска без учета регистра по вашему вопросу. Вы также можете использовать FIND() если хотите получить результаты с учетом регистра.
4. Включите IFERROR() ^1, если значение не найдено.
5. Добавьте итоговую строку внизу.

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

---

¹ Excel 2003 не имеет функции рабочего листа IFERROR .Обходной путь должен использовать IF(NOT(ISERROR(...))) . В этом случае пример формулы со скриншота должен иметь вид =IF(NOT(ISERROR(SEARCH(B$1,$A2)>0)),1,0)