Как составить интерактивную таблицу

26.02.2020

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

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

Каждую ячейку таблицы можно сделать интерактивной. Это может быть не только её увеличенный вариант, но и более подробное содержание, не видимое в общем контексте таблицы. Именно в этом режиме наиболее привлекателен визуальный ряд, который можно будет представить в наиболее приемлемом масштабе.

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

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

Как научиться делать интерактивную таблицу? Посмотрите это видео.

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

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

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

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

Об особенностях и требованиях к такой укрупнённой дидактической единице читайте статью в Дидакторе Анимированные таблицы: методический аспект.

Как создавать интерактивные отчеты и «оживить» данные в Excel?

Power View — это надстройка, с помощью которой можно создавать интерактивные графики, диаграммы, карты и таблицы, а также живые отчеты.

Чтобы начать работу с PowerView, необходимо добавить отображение команды, в одну из вкладок Excel, например, «Вставка».

Выполняем довольно простые действия: Выбрать Файл> Параметры> Настроить ленту> Вставка> Создать группу> Новая группа (настраиваемая)> Переименовать в PowerView> выбрать команды> Все команды> найти PowerView> Добавить> созданную группу PowerView (настраиваемая).

Панель Power View — своего рода рабочий лист Excel, но без ячеек. Для того чтобы информация отобразилась на листе PowerView, нужно отметить данные, которые расположены в панели «Поля» PowerView.

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

• Таблица.
• Линейчатая диаграмма. Применяется, когда вам нужно показать несколько рядов информации; информация включает положительные, отрицательные и нулевые значения; нужно сравнить всю информацию по многим категориям.
• Гистограмма: наглядно показывает изменения данных с течением времени и сравнение различных величин. На гистограммах категории расположились по горизонтали и значения по вертикали.
• Другая диаграмма показывает данные в виде одной из трех диаграмм: точечная, строчная и круговая.
• Карта показывает информацию в виде круговой диаграммы с геоданными.

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

Интерактивные или анимированные пузырьковые диаграммы наглядно показывают динамику развития с течением времени, при чем строится такая диаграмма с использованием Power View за считанные минуты. Для этого необходимо:

кликнуть по вкладке «Конструирование»> другая диаграмма> точечная диаграмма> перетащить поля> для того чтобы диаграмма стала анимированной и могла во времени и в динамике двигать пузырьки необходимо использовать ось воспроизведения и поместить поле с временным отрезком> нажать кнопку Play на диаграмме.

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

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

Источник

Как сделать интерактивную таблицу в excel?

Helen Bradley объясняет, как добавлять интерактивные элементы на лист Excel, используя Полосу прокрутки и Счетчик.

Всякий раз, когда пользователь выбирает из дискретного набора вариантов, какие данные ввести на лист Excel, Вы можете сэкономить время, автоматизировав процесс ввода. Это можно сделать различными способами, и один из них – использовать интерактивные элементы Spin Button (Счетчик) или Scroll Bar (Полоса прокрутки). Сегодня я познакомлю Вас с использованием Счетчика и Полосы прокрутки, а также покажу, как добавить к графику в Excel интерактивный элемент Счетчик.

• Открываем вкладку Разработчик
• Делаем Счетчик более полезным
• Как работает Полоса прокрутки
• Интерактивный график со счетчиком

Открываем вкладку Разработчик

В Excel 2007 и Excel 2010 элементы Счетчик и Полоса прокрутки доступны на вкладке Developer (Разработчик). Если у Вас эта вкладка не отображается выполните следующее:

• в Excel 2010 откройте File >Options >Customize Ribbon (Файл > Параметры > Настроить ленту) и на панели справа поставьте галочку возле названия вкладки Developer (Разработчик).
• в Excel 2007 нажмите кнопку Office, выберите Excel Options (Параметры Excel) и далее в разделе Popular (Основные) включите опцию Show Developer tab in the Ribbon (Показывать вкладку Разработчик на Ленте).

Чтобы увидеть инструменты, перейдите на вкладку Developer > Insert (Разработчик > Вставить) и выберите элемент Spin Button (Счетчик) или Scroll Bar (Полоса прокрутки) из группы Form Controls (Элементы управления формы). Крайне важно выбирать именно из этой группы, а не из ActiveX Controls (Элементы ActiveX), так как они работают абсолютно по-разному.

Перетащите на рабочий лист Excel Счетчик и Полосу прокрутки. Заметьте, что Вы можете изменять положение этих элементов, а также поворачивать их вертикально или горизонтально. Чтобы передвинуть или изменить размер элемента управления, щелкните по нему правой кнопкой мыши, а затем изменяйте размер или перетаскивайте.

Чтобы увидеть, как все это работает, щелкните правой кнопкой мыши по объекту и выберите пункт Format Control (Формат объекта). В открывшемся диалоговом окне на вкладке Control (Элемент управления) находятся параметры для настройки Счетчика. По своей сути, Счетчик помещает в ячейку какое-то значение, а Вы, нажимая стрелки, увеличиваете или уменьшаете его.

Элемент Счетчик имеет ограничения: значение должно быть целым числом от 0 до 30000, параметр Incremental Change (Шаг изменения) также должен быть любым целым числом от 1 до 30000.

Установите Current Value (Текущее значение) равным , Minimum Value (Минимальное значение) равным , Maximum Value (Максимальное значение) равное и Incremental Change (Шаг изменения) равным . Кликните по полю Cell Link (Связь с ячейкой), затем выделите ячейку A1 и закройте диалоговое окно.

Щелкните в стороне от Счетчика, чтобы снять с него выделение, и понажимайте стрелки. Вы увидите, что с каждым нажатием значение в ячейке A1 изменяется. Вы можете увеличить значение до 300, но не более, и уменьшить до 0, но не менее. Обратите внимание, что значение изменяется с шагом 10.

Делаем Счетчик более полезным

Вы можете использовать Счетчик таким образом, чтобы пользователь мог вводить значения на листе простым нажатием кнопки, вместо ввода вручную с клавиатуры. Вероятно, у Вас возникает вопрос: как быть, если значения, которые должен ввести пользователь не целые числа в диапазоне от 0 до 30000?

Решение есть – используйте промежуточную ячейку для вычисления нужного Вам значения. Например, если Вы хотите, чтобы пользователь вводил значения между 0% и 5% с шагом 0,1%, нужно масштабировать значение, которое дает счетчик, чтобы получить результат от до с шагом .

Есть множество вариантов, как это можно реализовать математически и, если Ваше решение работает, то не имеет значения, как Вы это сделали. Вот одно из возможных решений: кликните по счетчику правой кнопкой мыши, выберите Format Control (Формат объекта) и установите Minimum Value (Минимальное значение) = 0, Maximum Value (Максимальное значение) = 500 и Incremental Change (Шаг изменения) = 10. Установите связь с ячейкой A1. Далее в ячейке A2 запишите формулу =A1/10000 и примените к ней процентный числовой формат с одним десятичным знаком.

Теперь, нажимая на кнопки счетчика, Вы получите в ячейке A2 именно тот результат, который необходим – значение процента между 0% и 5% с шагом 0,1%. Значение в ячейке A1 создано счетчиком, но нас больше интересует значение в ячейке A2.

Всегда, когда нужно получить значение более сложное, чем целое число, используйте подобное решение: масштабируйте результат, полученный от счетчика, и превращайте его в нужное значение.

Как работает Полоса прокрутки

Полоса прокрутки работает таким же образом, как и Счетчик. Кроме этого, для Полосы прокрутки Вы можете настроить параметр Page Change (Шаг изменения по страницам), который определяет на сколько изменяется значение, когда Вы кликаете по полосе прокрутки в стороне от её ползунка. Параметр Incremental Change (Шаг изменения) используется при нажатии стрелок по краям Полосы прокрутки. Конечно же нужно настроить связь полосы прокрутки с ячейкой, в которую должен быть помещен результат. Если нужно масштабировать значение, Вам потребуется дополнительная ячейка с формулой, которая будет изменять значение, полученное от Полосы прокрутки, чтобы получить правильный конечный результат.

Представьте организацию, которая предоставляет кредиты от $20 000 до $5 000 000 с шагом изменения суммы $10 000. Вы можете использовать Полосу прокрутки для ввода суммы займа. В этом примере я установил связь с ячейкой E2, а в C2 ввел формулу =E2*10000 – эта ячейка показывает желаемую сумму займа.

Полоса прокрутки будет иметь параметры: Minimum Value (Минимальное значение) = 2, Maximum Value (Максимальное значение) = 500, Incremental Change (Шаг изменения) = 1 и Page Change (Шаг изменения по страницам) = 10. Incremental Change (Шаг изменения) должен быть равен 1, чтобы дать возможность пользователю точно настроить значение кратное $10 000. Если Вы хотите создать удобное решение, то очень важно, чтобы пользователь имел возможность легко получить нужный ему результат. Если Вы установите Шаг изменения равным, к примеру, , пользователь сможет изменять сумму займа кратно $50 000, а это слишком большое число.

Параметр Page Change (Шаг изменения по страницам) позволяет пользователю изменять сумму займа с шагом $100 000, так он сможет быстрее приблизиться к сумме, которая его интересует. Ползунок полосы прокрутки не настраивается ни какими параметрами, так что пользователь способен мгновенно перейти от $20 000 к $5 000 000 просто перетащив ползунок от одного конца полосы прокрутки к другому.

Интерактивный график со счетчиком

Чтобы увидеть, как будут работать эти два объекта вместе, рассмотрим таблицу со значениями продаж за период с 1 июня 2011 до 28 сентября 2011. Эти даты, если преобразовать их в числа, находятся в диапазоне от до (даты в Excel хранятся в виде кол-ва дней, прошедших с 0 января 1900 года).

В ячейке C2 находится такая формула:

Эта формула скопирована в остальные ячейки столбца C. К ячейкам C2:C19 применено условное форматирование, которое скрывает любые сообщения об ошибках, т.к. формулы в этих ячейках покажут множество сообщений об ошибке #N/A (#Н/Д). Мы могли бы избежать появления ошибки в столбце C, написав формулу вот так:

Но в таком случае график покажет множество нулевых значений, а этого нам не нужно. Таким образом, ошибка – это как раз желательный результат.

Чтобы скрыть ошибки, выделите ячейки в столбце C и нажмите Conditional Formatting > New Rule (Условное форматирование > Создать правило), выберите тип правила Format Only Cells That Contain (Форматировать только ячейки, которые содержат), в первом выпадающем списке выберите Errors (Ошибки) и далее в настройках формата установите белый цвет шрифта, чтобы он сливался с фоном ячеек – это самый эффективный способ скрыть ошибки!

В ячейке G1 находится вот такая формула =40000+G3, а ячейку G3 мы сделаем связанной со Счетчиком. Установим вот такие параметры: Current Value (Текущее значение) = 695, Minimum Value (Минимальное значение) = 695, Maximum Value (Максимальное значение) = 814, Incremental Change (Шаг изменения) = 7 и Cell Link (Связь с ячейкой) = G3. Протестируйте Счетчик, нажимая на его стрелки, Вы должны видеть в столбце C значение, соответствующее дате в ячейке G1.

Minimum Value (Минимальное значение) – это число, добавив к которому 40000 мы получим дату 1 июня 2011, а сложив Maximum Value (Максимальное значение) и 40000, – получим дату 28 сентября 2011. Мы установили Шаг изменения равным 7, поэтому даты, которые появляются в ячейке G1, идут с интервалом одна неделя, чтобы соответствовать датам в столбце A. С каждым кликом по стрелкам Счетчика, содержимое ячейки G3 изменяется и показывает одну из дат столбца A.

Добавляем график

График создаем из диапазона данных A1:C19, выбираем тип Column chart (Гистограмма). Размер графика сделайте таким, чтобы он закрывал собой столбец C, но, чтобы было видно первую строку листа Excel.

Чтобы настроить вид графика, необходимо щелкнуть правой кнопкой мыши по одиночному столбцу, показывающему значение для Series 2 (Ряд 2), выбрать Change Series Chart Type (Изменить тип диаграммы для ряда), а затем Line Chart With Markers (График с маркерами). Далее кликаем правой кнопкой мыши по маркеру, выбираем Format Data Series (Формат ряда данных) и настраиваем симпатичный вид маркера. Ещё раз щелкаем правой кнопкой мыши по маркеру и выбираем Add Data Labels (Добавить подписи данных), затем по Легенде, чтобы выделить её и удалить.

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

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник:/> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Преимущества создания интерактивного форматирования таблиц в Excel для взаимодействия показателей данных с пользовательскими критериями.

Форматирование таблиц для взаимодействия с пользователем

Как копировать таблицу в Excel сохраняя формат ячеек.

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

Вводим в ячейку число как текст.

Преобразование числовых значений в текстовый формат с помощью двух способов форматирования ячеек. Особенности при математических вычислениях с числами преобразованные в текст.

Таблицы в Excel представляют собой ряд строк и столбцов со связанными данными, которыми вы управляете независимо друг от друга.

Работая в Excel с таблицами, вы сможете создавать отчеты, делать расчеты, строить графики и диаграммы, сортировать и фильтровать информацию.

Если ваша работа связана с обработкой данных, то навыки работы с таблицами в Эксель помогут вам сильно сэкономить время и повысить эффективность.

Как работать в Excel с таблицами. Пошаговая инструкция

1. Выделите область ячеек для создания таблицы

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

2. Нажмите кнопку “Таблица” на панели быстрого доступа

На вкладке “Вставка” нажмите кнопку “Таблица”.

3. Выберите диапазон ячеек

В всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.

4. Таблица готова. Заполняйте данными!

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

Форматирование таблицы в Excel

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

Если 7-ми стилей вам мало для выбора, тогда, нажав на кнопку, в правом нижнем углу стилей таблиц, раскроются все доступные стили. В дополнении к предустановленным системой стилям, вы можете настроить свой формат.

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

• Отображение строки заголовков – включает и отключает заголовки в таблице;
• Строку итогов – включает и отключает строку с суммой значений в колонках;
• Чередующиеся строки – подсвечивает цветом чередующиеся строки;
• Первый столбец – выделяет “жирным” текст в первом столбце с данными;
• Последний столбец – выделяет “жирным” текст в последнем столбце;
• Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
• Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

Как добавить строку или столбец в таблице Excel

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

• Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.

• Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.

Как отсортировать таблицу в Excel

Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.

Как отфильтровать данные в таблице Excel

Для фильтрации информации в таблице нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

• “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
• “Фильтр по цвету” также как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
• “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
• В всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

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

Как посчитать сумму в таблице Excel

Для того чтобы посчитать сумму колонки в конце таблицы, нажмите правой клавишей мыши на любой ячейке и вызовите всплывающее окно:

В списке окна выберите пункт “Таблица” => “Строка итогов”:

Внизу таблица появится промежуточный итог. Нажмите левой клавишей мыши на ячейке с суммой.

В выпадающем меню выберите принцип промежуточного итога: это может быть сумма значений колонки, “среднее”, “количество”, “количество чисел”, “максимум”, “минимум” и т.д.

Как в Excel закрепить шапку таблицы

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

Для того чтобы закрепить заголовки сделайте следующее:

• Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:

• Выберите пункт “Закрепить верхнюю строку”:

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

Как перевернуть таблицу в Excel

Представим, что у нас есть готовая таблица с данными продаж по менеджерам:

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

• Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):

• Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:

• В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:

• Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.

В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!

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

На рис. 1 показан лист Excelс отчетом Первые N заказчиков, включающий ряд срезов. Исходные данные и сводная таблица находятся на других листах.

Рис. 1. Рабочий лист Excel, отображаемый в виде веб-страницы

Скачать заметку в формате Word иди pdf, примеры в формате Excel

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

Чтобы создать веб-страницу на основе книги Excel, поместите видимые части отчета, которые будут просматриваться всеми посетителями веб-страницы, на отдельном листе. Вырежьте всю лишнюю информацию, и вставьте ее на скрытый лист. Обратите внимание на то, что вместо ячеек ввода данных используется срез. Это связано с тем, что срезы весьма удобны для выбора значений на веб-страницах (подробнее см. Excel 2013. Срезы сводных таблиц; создание временной шкалы). Срез Укажите количество заказчиков связан с десятистрочным набором данных и со сводной таблицей, находящейся на скрытом листе. Формула, находящаяся в диапазоне ячеек F2:F11, «вытягивает» значения из сводной таблицы, расположенной на Листе2 (см. приложенный Excel-файл). Если пользователь изменит настройки среза, соответствующим образом изменяется количество отображаемых первых заказчиков.

Превратите лист Excel в веб-страницу. Начните с того, что на вкладке Вид отмените установку флажков Строка формул, Сетка и Заголовки. На рис. 1 показана рабочая книга, отображаемая в окне программы Excel. Чтобы выбрать элементы, отображаемые в окне браузера, откройте меню Файл и на левой навигационной панели выберите параметр Сведения. Щелкните на кнопке Параметры просмотра в браузере, находящейся в нижней части окна. На вкладке Показать раскройте список и выберите в нем вместо пункта Вся книга пункт Листы. Установите флажки возле листов, которые должны отображаться в окне браузера (рис. 2).

Рис. 2. Выберите отображаемые и скрытые листы

Сохраните книгу в хранилище OneDrive. Прежде чем предоставить книгу в общее пользование, протестируйте ее. Проверьте, чтобы все параметры работали правильно, и книга выглядела корректно. Если вы зарегистрированы в учетной записи OneDrive и откроете книгу, будет автоматически выбран режим редактирования. Кликните Редактировать книгу. Выберите пункт Изменить в ExcelOnline(рис. 3). Появится лента Excel. Перейдите на вкладку Вид и выберите параметр Режим чтения (рис. 4). На рис. 5 показана рабочая книга, отображаемая в окне браузера. Если щелкнуть на области среза, фильтры изменятся, что приведет к обновлению сводных таблиц и сводных диаграмм.

Рис. 3. Редактировать книгу

Рис. 4. Режим чтения

Рис. 5. Интерактивная веб-страница, созданная в Excel

Отправка ссылок на интернет-версии книг

Простейший способ пригласить других пользователей для просмотра интернет-версии книги Excel — воспользоваться командой OneDrive Поделиться. В результате выполнения этой команды другие пользователи получат возможность просматривать и изменять содержимое вашей книги, а также смогут загружать книги на свои компьютеры. Во время просмотра книги выполните команду Общий доступ → Поделиться. Откроется диалоговое окно, в котором доступны две опции:

• Отправить ссылку для доступа (рис. 6). Выберите адрес получателя и настройте опции.
• Получить ссылку (рис. 7). Выберите вариант доступа и кликните Создать ссылку (рис. 7а). Вы получите адрес ссылки на ваш интерактивный Excel-файл (рис. 7б).

Рис. 6. Отправьте ссылку на вашу интерактивную веб-страницу

Рис. 7. Получите ссылку, которую можно вставить в блог или отправить кому-либо

Если кто-либо перейдет по ссылке, он увидит копию приложения, предназначенную только для чтения (как на рис. 5). Любые изменения, внесенные в срез или параметры, не сохраняются в книге. Поэтому следующий посетитель сайта увидит прежнюю копию книги.

Внедрение книги в рассылку блога или в веб-страницу

При наличии блога или веб-страницы можно внедрить туда книгу. Вернитесь к меню Общий доступ и выберите пункт Внедрить (рис. 8).

Рис. 8. Опция Внедрить меню Общий доступ

Excel отображает окно предупреждения, в котором сообщается о том, что пользователи, которым неизвестен ваш пароль, могут получить доступ к веб-странице (рис. 9).

Рис. 9. Щелкните на кнопке Создать, чтобы подтвердить создание кода для внедрения книги

На следующем экране можно настроить параметры кода для внедрения (рис. 10):

• Можно открыть доступ пользователям не ко всей книге, а только к диапазону, например, А1:М20.
• Если вы ранее не скрывали линии сетки, а также заголовки строк и столбцов, самое время сделать это сейчас.
• По умолчанию установлен флажок Включить ссылку для загрузки. Если вы хотите защитить данные, находящиеся на скрытом листе, отмените установку этого флажка.
• Установите первый флажок в разделе Взаимодействие. С помощью этого флажка обеспечивается возможность фильтрации базовой сводной таблицы с помощью срезов. Можно задать начальную активную ячейку в первой ячейке ввода.
• При необходимости настройте высоту и ширину внедренного фрейма.

После настройки всех параметров внедренный код появится в нижней части экрана. Чтобы просмотреть внедренный код, можно выполнить прокрутку вниз. Скопируйте внедренный код в буфер обмена. Создайте новую веб-страницу или сообщение блога и вставьте внедренный код в нужном месте. В результате получим безопасную веб-страницу, использующую формулы и данные, которая не отображает таблицы на скрытых листах (рис. 11).

Рис. 10. Настройте параметры внедрения объекта и скопируйте код

Рис. 11. Эта веб-страница содержит внедренный объект – лист Excel. Понажимайте на кнопочки срезов, вам понравится!

Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 8.

Источник

Dashboard в переводе с английского означает «приборная панель». В контексте работы с данными дашборд – это интерактивная информационная панель, заменяющая собой несколько таблиц или слайдов.

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

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

В этой заметке я расскажу, как вы можете быстро сделать интерактивный дашборд в Excel. Для этого не нужно знать ни язык программирования VBA, ни устанавливать какие-либо надстройки на Эксель.

Все что вам понадобится – это обычный Эксель и данные, которые нужно превратить в дашборд.

Итак, давайте приступим.

Что нужно для создания дашборда в Excel

Чаще всего дашборды используются для анализа каких-то финансовых показателей, но их можно применять к абсолютно любым данным. Главное понять алгоритм их создания.

Итак, у нас есть данные по продажам определенных товаров за определенный период времени, а именно за 2020-ый год (скачать файл).

Необходимо создать интерактивный отчет по продажам за этот год.

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

Давайте сформулируем ряд вопросов.

1. Какова была динамика продаж в течение года? Так как нам нужен отчет за год, то в нем должен быть общий график продаж по месяцам.
2. Каковы были объемы продаж отдельных категорий товаров по месяцам, торговым сетям и городам?
3. Составить товаров, принесших наибольшую выручку.

Думаю, этих вопросов будет достаточно, чтобы ответить на них простым, но функциональным дашбордом.

В начале стоит прикинуть структуру дашборда на бумаге. Такой эскиз позволит спланировать всю работу.

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

Итак, план готов и переходим ко второму этапу – подготовка данных.

Подготовка данных для дашборда

У нас есть таблица с данными, но даже в такой структурированной форме проанализировать данные крайне сложно. Максимально просто и быстро привести данные к нужному виду позволяют сводные таблицы. Им было посвящено подробное видео, поэтом если вы не знакомы с этим инструментом, то обязательно его посмотрите.

Создадим сводную таблицу, которая будет отвечать на первый вопрос – какова динамика продаж в течение года?

Перетянем поле Дата в область Строк, а Итого в Значения.

Все готово!

Переименуем лист со сводной таблицей в «Динамика продаж».

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

Чуть позже мы поработаем над внешним видом диаграммы, а пока ответим на второй вопрос – каковы были объемы продаж отдельных категорий товаров? При этом нас будут интересовать три фильтра – по месяцам, торговым сетям и городам.

Создадим еще одну сводную таблицу на новом листе. В область Строк поместим поле Категории товаров, а в Значения – Итого. Изменим название листа на «Категории товаров», а затем построим гистограмму.

Над внешним видом диаграммы поработаем позже, тогда же вставим необходимые фильтры.

Третья задача – составить рейтинг товаров.

Снова создаем сводную таблицу на новом листе и в область Строки поместим Товар, а в область Значения поле Итого. Переименуем лист в «Товары» и затем создадим линейчатую диаграмму.

В общих чертах все готово. Осталось создать дашборд и поработать над внешним видом диаграмм.

Компоновка дашборда

Сначала создадим новый лист, который так и назовем «Дашборд», а затем подкорректируем и перенесем на него ранее созданные диаграммы.

Во-первых, изменим название диаграмм. Я предпочитаю задавать все параметры диаграммы на листе – в ячейках листа или таблицы. Так проще в случае необходимости вносить изменения. Поэтому в ячейке над сводной таблицей введем заголовок (1) и затем свяжем ячейку с ним с названием диаграммы. Для этого выбираем на диаграмме ее название (2) и в строке формул вводим знак равенства. Далее просто указываем ячейку с заголовком (1).

На сводных диаграммах выводятся дополнительные кнопки (1), позволяющие производить фильтрацию и сортировку данных. Нам они не нужны, поэтому отключим их отображение через контекстное меню (2), вызванное на любой кнопке диаграммы. Можем убрать и легенду (3), так как она не информативна.

Теперь копируем диаграмму в буфер обмена (например, с помощью сочетания клавиш Ctrl + C) и вставляем ее на лист дашборда.

Аналогичные действия проделаем и с другими диаграммами.

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

Добавляем интерактивность

Осталось добавить фильтры и поработать над оформлением.

Выбираем диаграмму с категориями товаров (1) и на контекстной вкладке Анализ сводной диаграммы (2) вставляем два среза (3) – по Заказчику и Городу (4).

Разместим срезы в левой части экрана.

Так как заголовки срезов (1) не особо нужны, то можно их отключить через настройки среза, выбрав соответствующий пункт в контекстном меню (2).

Также через контекстную вкладку Срез (1) можем задать одинаковую ширину для срезов и выбрать оптимальную высоту для каждого из них (2). Здесь же задается высота и ширина отдельной кнопки в срезе (3), поэтому поработав с настройками можно добиться оптимального результата (4).

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

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

Аналогично вставим и настроим временную шкалу и свяжем ее с двумя диаграммами.

Затем уберем со шкалы все лишнее – для этого отключим все чек-боксы на панели инструментов Показать контекстной вкладки Временная шкла.

Теперь можем упорядочить данные на дашборде.

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

Каркас дашборда готов и осталось поработать над его внешним видом.

Форматирование дашборда

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

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

Аналогично поступим и со строками. В этом нам поможет сочетание клавиш Ctrl + Shift + стрелка вниз.

Затем объединим верхние ячейки над графиком и зальем их цветом из основной палитры. Вставим название дашборда и изменим его шрифт.

Откорректируем диаграммы. На первой диаграмме выведем подписи к точкам данных.

Расположим подписи точек данных сверху.

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

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

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

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

Давайте решим эти задачи.

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

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

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

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

На вертикальной оси третьей диаграммы выводятся товары и их довольно много, поэтому Excel автоматически скрыл часть наименований, которые перекрывались. Чтобы включить отображение всех подписей в окне форматирования оси в разделе Подписи изменим интервал между подписями с Авто на 1.

На этой диаграмме должен выводится рейтинг товаров, поэтому нужно, чтобы они шли по порядку в зависимости от объема продаж. Перейдем на лист соответствующей сводной таблицы и щелкнем правой кнопкой мыши на столбце со значениями объемов продаж. Из контекстного меню выберем нужное направление сортировки.

Диаграмма перестроится автоматически.

Ну и осталось поработать со срезами.

Чтобы убрать их контур можно продублировать один из готовых стилей.

Затем отредактировать новый стиль, убрав контур.

Аналогично убирается контур и у временной шкалы.

Ну а теперь осталось на вкладке Вид отключить отображение строки формул, заголовков строк и столбцов, а также сетку листа.

Свернем ленту с инструментами, чтобы максимально расширить область дашборда и откорректируем его масштаб.

Все готово и замечательно работает.

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

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

В курсе по шагам рассматривается процесс создания дашборда и подробно разбираются инструменты Excel, которые позволяют сделать дашборд максимально информативным и легко воспринимаемым.

Если тема дашбордов вам интересна, то обратите внимание на этот курс.

Нажмите на эту ссылку, чтобы перейти на страницу курса >>

_________________________________________________________________________________________

Ссылки на мои ресурсы по Excel

★ YouTube-канал Excel Master

★ Серия видеокурсов “Microsoft Excel Шаг за Шагом”

★ Авторские книги и курсы

Power View — это надстройка, с помощью которой можно создавать интерактивные графики, диаграммы, карты и таблицы, а также живые отчеты.

Чтобы начать работу с PowerView, необходимо добавить отображение команды, в одну из вкладок Excel, например, «Вставка».

Выполняем довольно простые действия: Выбрать Файл> Параметры> Настроить ленту> Вставка> Создать группу> Новая группа (настраиваемая)> Переименовать в PowerView> выбрать команды> Все команды> найти PowerView> Добавить> созданную группу PowerView (настраиваемая).

Панель Power View — своего рода рабочий лист Excel, но без ячеек. Для того чтобы информация отобразилась на листе PowerView, нужно отметить данные, которые расположены в панели «Поля» PowerView.

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

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

Интерактивные или анимированные пузырьковые диаграммы наглядно показывают динамику развития с течением времени, при чем строится такая диаграмма с использованием Power View за считанные минуты. Для этого необходимо:

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

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