Что научитесь делать, посмотрев это видео?

Из этого видео вы узнаете, что такое сводная таблица в экселе и как с ней работать; как разделить информацию в одном столбце на два столбца и более; как работать с формулами ЕСЛИ и ДЕНЬНЕД.

О том  какие основные правила корректной записи формул в Excel  описано  тут.

Описание видео:

Рассмотрим все эти моменты на примере работы с данными по закупкам интернет-магазина. В первом столбце у нас указаны номера заказа, во втором – время заказа и дата, в третьем – стоимость заказа.

На примере столбца «Время заказа» рассмотрим, как разделить столбец с данными на два.

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

В зависимости от того какую информацию вам надо разделить в этом окне вы отмечаете соответствующие параметры.

Теперь в столбце «Время заказа» остались даты, а вместо времени появились нули. Чтобы их убрать, мы выделяем столбец «Время заказа», правой кнопкой мыши вызываем меню и кликаем «Формат ячеек». В открывшемся окне в левой части выбираем «Дата», в правой части выбираем тот способ отображения даты, который больше подходит, и нажимаем «Ок». Теперь в столбце остались только даты.

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

Напомню, что нам надо узнать в какое время чаще всего совершают покупки в интернет-магазине. Производить сортировку данных о времени покупке удобнее, если время отражается не в часах и минутах, а в более приемлемых для системы единицах. Поэтому после столбца с временем делаем дополнительный столбец. Затем выделяем в новом столбце 2-ую ячейку и прописываем формулу «=С2» и копируем в нее данные с ячейки со временем. Получаем время в виде обычного числа. Затем протягиваем формулу через весь столбец, чтобы в таком же виде отражалось содержимое всех остальных ячеек. После этого выделяем наш новый столбец и делаем «Формат ячеек» «Числовой». В столбце «1» равна 24 часам и т.д.

Для удобства сортировки данных разобьем все время на 8 промежутков. Для этого на новом листе присваиваем первому столбцу формат «Текстовый» и вписываем в его ячейки значения 0-3, 3-6, 6-9 и т.д. до 21-24. Во втором столбце автозаполением прописываем «1», выделяем его. В правом углу под столбцом появляется значок с синим и белыми прямоугольниками, кликаем по нему и выбираем «Заполнить». Теперь вместо единиц в столбце появились числа от 1 – 8. Это наши восемь критериев для сортировки.

Возвращаемся на «Лист 1». В столбце «Стоимость заказа» надо поменять все точки на запятые. Для этого выделяем столбец и нажимаем Ctrl+f, появилось окно «Найти и заменить». В строку «Найти» ставим точку, в строку «Заменить» ставим запятую и нажимаем «Заменить все». Система покажет, сколько замен произведено, нажимаем «Ок», закрываем окно.

Теперь перед столбцом с датами добавляем пустой столбец и прописываем формулу «=деньнед(С2;», чтобы в формуле появилось «С2» надо выбрать эту ячейку в таблице. После этого в 10-ом экселе появляется список типов дней недели. Мы выбираем 2-ой тип и в столбце появляются числа от 1 – 7, где 1 – понедельник, 2 – вторник и т.д.

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

Теперь будем делать сводную таблицу по количеству покупок в определенное время суток. Для этого опускаемся вниз таблицы и протягиваем вниз на 8 строчек столбец «Время заказа» и столбец «Время в числе». В столбце «Время заказа» вносим начало наших временных промежутков, т.е. полночь – 00:00, 3 часа ночи – 3:00 и т.д. В столбце «Время в числе» получаем значения 0,00; 0,13.

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

После столбца «Время в числе» добавляем новый столбец «Пер. времени». Именно под ним мы будем прописывать формулу «ЕСЛИ».

В свободной ячейке пишем формулу «=если(», щелкаем на ячейку со значением 0,125. Получаем «=если(Е235<0,125;1;если(Е235<0,25;2)». Теперь прописываем эту формулу до 8-ки, так как у нас всего 8 временных периодов.

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

Удаляем старую сводную таблиц и график.

Еще раз делаем сводную таблицу по дням недели и закупкам.

После этого создаем новую сводную таблицу. Заходим в «Вставку» и выбираем «Сводная таблица». Задаем таблицу и в «Списке полей сводной таблицы» выделяем «Номер заказа» и «Пер. времени». И получаем таблицу по закупкам в определенный период времени. Через кнопку «Гистограмма» делаем графики для наших сводных таблиц.

Сведенья о том, как отфильтровать исходные данные в сводной таблице Excel вы найдете пройдя по ссылке.

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

Сайт оказался для Вас полезным?

View Results

Loading ... Loading ...