2014 dxdy logo

Научный форум dxdy

Математика, Физика, Computer Science, Machine Learning, LaTeX, Механика и Техника, Химия,
Биология и Медицина, Экономика и Финансовая Математика, Гуманитарные науки




Начать новую тему Ответить на тему
 
 Microsoft Excel - сгруппировать данные в таблицу
Сообщение15.04.2017, 05:43 


12/07/15
2907
г. Чехов
Жене нужно сделать презентацию статистики посещаемости социальных сетей на основе данных, выгруженных из сетей "Фейсбук", "Одноклассники", "ВКонтакте". Данные выгружаются в формате xls.
С Фейсбуком и Одноклассниками все в порядке, понятно, а ВКонтакте задал затруднительную задачку даже для меня, опытного эксельщика... Дело в том, что данные структурированы так, что с ними очень неудобно работать средствами Excel.

На данный момент таблица упрощенно представляет собой три столбца:
Код:
Дата          Критерий               Значение
01.01.2017   Уникальные посетители     3
01.01.2017   Просмотры                 14
02.01.2017   Уникальные посетители     5
02.01.2017   Просмотры                 22
02.01.2017   Клики                     1
03.01.2017   Уникальные посетители     3
03.01.2017   Просмотры                 18
03.01.2017   Клики                     1
........................


Хочу обратить внимание, что в отдельные дни записи, соответствующие некоторым критериям, отсутствуют, что делает исходную таблицу нелинейной. Например, как в примере, отсутствует запись "01.01.2017 - Клики - 0". Отсутствие записи, по видимому следует расценивать, как нулевое значение критерия.

Очевидно данные представляют собой записи из базы данных и любая СУБД справится с обработкой таких данных на ура. Но для работы в Excel нужно преобразовать в удобоваримый вид.

Нужно получить таблицу вида:
Код:
Дата          Уникальные посетители    Просмотры    Клики
01.01.2017               3                 14         0
02.01.2017               5                 22         1
03.01.2017               3                 18         1
...............................


Применял функцию БИЗВЛЕЧЬ(), удается извлечь одну строчку, но сформировать всю таблицу эта функция, на мой взгляд, не может.

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

 Профиль  
                  
 
 Re: Microsoft Excel - сгруппировать данные в таблицу
Сообщение15.04.2017, 05:55 
Аватара пользователя


21/01/09
3923
Дивногорск
Логическое "ЕСЛИ".

 Профиль  
                  
 
 Re: Microsoft Excel - сгруппировать данные в таблицу
Сообщение15.04.2017, 08:13 


01/12/11

1047
Для начала надо добавить отсутствующие строки
Допустим имеем
Код:
Дата          Критерий               Значение
01.01.2017   Уникальные посетители     3
01.01.2017   Просмотры                 14
02.01.2017   Уникальные посетители     5
02.01.2017   Клики                     1
03.01.2017   Уникальные посетители     3
03.01.2017   Просмотры                 18
03.01.2017   Клики                     1
........................

Добавляем недостающие строки
Код:
Дата          Критерий               Значение
01.01.2017   Уникальные посетители     3
01.01.2017   Просмотры                 14

02.01.2017   Уникальные посетители     5

02.01.2017   Клики                     1
03.01.2017   Уникальные посетители     3
03.01.2017   Просмотры                 18
03.01.2017   Клики                     1
........................

Понадобится знание макросов и VBL.

 Профиль  
                  
 
 Re: Microsoft Excel - сгруппировать данные в таблицу
Сообщение15.04.2017, 09:24 


01/12/11

1047
Можно поступить ещё проще. Разнести значения категорий по столбцам.

Код:
Дата                Критерий        Значение  Уникальные     Просмотры    Клики
                                              посетители
01.01.2017   Уникальные посетители     3          3
01.01.2017   Просмотры                14                        14
02.01.2017   Уникальные посетители     5          5
02.01.2017   Клики                     1                                    1
03.01.2017   Уникальные посетители     3          3
03.01.2017   Просмотры                18                        18
03.01.2017   Клики                     1                                    1
........................

 Профиль  
                  
 
 Re: Microsoft Excel - сгруппировать данные в таблицу
Сообщение15.04.2017, 10:43 


05/09/16
11469
Mihaylo в сообщении #1209569 писал(а):
Помогите пожалуйста советом, как преобразовать базу данных в нормальную таблицу. Желательно задачу решить средствами собой Экселя, хотя в принципе без разницы.

Это не просто, а очень просто.
Выделяете все данные вместе с названиями столбцов, затем "вставить" и "сводную таблицу" и потом в конструкторе сводных таблиц мышкой тащите поле "Дата" в строки, поле "Критерий" в столбцы, а поле "Значение" в "Значения".
Там где "Кликов" или "Просмотров" или "Уникальных посетителей" не будет, будет пустая клетка. Заполнить пустые клетки нулями (если надо) задача тривиальная.
Изображение

 Профиль  
                  
 
 Re: Microsoft Excel - сгруппировать данные в таблицу
Сообщение16.04.2017, 06:21 


12/07/15
2907
г. Чехов
Skeptic в сообщении #1209579 писал(а):
Для начала надо добавить отсутствующие строки

Слишком муторно: таблица очень большая, параметров больше, чем три.

wrest в сообщении #1209602 писал(а):
Это не просто, а очень просто.

Отличный вариант!

Создалась нужная сводная таблица, но возникла другая проблема. Записи сгруппировались по дате не как по типу "дата", а как по строковому типу. То есть в следующем порядке: 01.01.2017, 01.02.2017, 01.03.2017, 02.01.2017, 02.02.2017, ... Пока никак не могу сделать правильную сортировку.

-- 16.04.2017, 08:29 --

Сам бы не догадался, нагуглил решение:

Цитата:
Выделите оба столбца, на вкладке Главная в разделе Редактирование нажмите Сортировка и фильтр-Настраиваемая сортировка... В открывшемся окне, в строке Сортировать по, выберете необходимый столбец. В строке Порядок выберете Настраиваемый список... В открывшемся окне выберете месяц, например, Январь, Февраль, Март и т.д. Нажмите кнопку ОК.


Потом нужно выбрать "Все данные, похожие на числа, как числа" и еще раз нажать ОК.

 Профиль  
                  
 
 Re: Microsoft Excel - сгруппировать данные в таблицу
Сообщение16.04.2017, 08:58 


19/05/10

3940
Россия
или ДАТАЗНАЧ

 Профиль  
                  
 
 Re: Microsoft Excel - сгруппировать данные в таблицу
Сообщение16.04.2017, 09:52 
Аватара пользователя


11/12/16
13195
уездный город Н
Mihaylo
Mihaylo в сообщении #1209783 писал(а):
Записи сгруппировались по дате не как по типу "дата", а как по строковому типу.


Отформатировать дату в формат YYYY-MM-DD, и пусть сортирует как строки :D

 Профиль  
                  
 
 Re: Microsoft Excel - сгруппировать данные в таблицу
Сообщение16.04.2017, 10:13 


19/05/10

3940
Россия
EUgeneUS в сообщении #1209796 писал(а):
...Отформатировать дату в формат YYYY-MM-DD...
Не отформатируется

 Профиль  
                  
Показать сообщения за:  Поле сортировки  
Начать новую тему Ответить на тему  [ Сообщений: 9 ] 

Модераторы: Karan, Toucan, PAV, maxal, Супермодераторы



Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей


Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете добавлять вложения

Найти:
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group