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

REDMOND

Трехмерные диаграммы (поверхности и изолинии) в EXCEL

history 16 мая 2015 г.
    Группы статей

  • Диаграммы и графики

Рассмотрим трехмерные диаграммы в MS EXCEL 2010. С помощью трехмерных диаграмм отображают поверхности объемных фигур (гиперболоид, эллипсоид и др.) и изолинии.

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

При попытке построить диаграмму типа Поверхность на основе одного ряда данных, MS EXCEL может сказать, что ему требуется как минимум 2 ряда. На самом деле, для более или менее наглядной поверхности потребуется как минимум 10 рядов (лучше 20 – для сложных поверхностей). К счастью, ряды не нужно создавать в ручную – достаточно правильно сформировать таблицу с исходными данными.

Построим график функции z=-sin(x*х+y*y)+1 (см. файл примера ).

В качестве источника данных используем таблицу, в которой в верхней строке указаны значения Х, а в левом столбце Y. В остальных ячейках – значения Z для всех пар (Х,Y)

Чтобы значения Z были более информативны — можно использовать Условное форматирование .

Условное форматирование показывает некий вариант изолиний (см. ниже): близкие значения Z выделены одним цветом. Это также позволяет предварительно оценить вид создаваемой диаграммы.

Теперь выделите любую ячейку таблицы и постройте диаграмму типа Поверхность (вкладка Вставка , группа Диаграммы , Кнопка Другие ).

Полученную фигуру можно повращать (кнопка Поворот объемной фигуры на вкладке Макет ).

Для построения диаграммы используется 3 оси: горизонтальная ось (категорий), вертикальная ось (значений), ось Z (рядов значений). Все 3 названия осей требуют пояснения.

Во-первых, ось Z на самом деле является нашей осью Х и отображает имена рядов. Если имена рядов убрать, то изменятся и подписи оси (убрать имена рядов можно нажав кнопку Изменить в окне Выбор источника данных , которое доступно через вкладку Конструктор ).

REDMOND

Этот факт роднит диаграмму Поверхность с Диаграммой График , где по Х отображаются лишь подписи. Т.е. при попытке построить поверхность с неравномерным шагом по Х (например, 1, 2, 5, 9, 77, 103, ..) получим искажения масштаба: значения по Х будут откладываться последовательно 1, 2, 3, … (конечно, Z будет рассчитаны правильно). Интересно, что формат подписей по оси Z можно настроить только изменяя формат ячеек, на которые ссылаются имена рядов (изменение формата на вкладке Число (в окне Формат Оси) ни к чему не приводит).

Во-вторых, по вертикальной оси (значений) на самом деле откладываются значения Z (это очевидно из рисунка).

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

Так как диаграмма Поверхность – это по сути трехмерный График , в котором одному Х соответствует один Y, то некоторые объемные фигуры отобразить на диаграмме Поверхность не удастся. Например, не удастся отобразить гиперболоид, т.к. каждой паре (Х;Y) на диаграмме Поверхность соответствует только один Z, а для гиперболоида это не так (каждой паре соответствует 2 значения Z). Поэтому, в MS EXCEL придется довольствоваться только половиной гиперболоида.

Как видно из диаграммы, близкие значения Z выделены одним цветом, которые указаны в Легенде. К сожалению, в MS EXCEL невозможно управлять этими уровнями (даже при повороте фигуры количество уровней и диапазоны значений существенно изменяются). Это приводит к тому, что построение изолиний ( линии уровня, контурный график, англ. Contour plot ) реализовано в MS EXCEL в достаточно ограниченном виде: невозможно указать на диаграмме значение изолинии (диапазоны цветов указаны на Легенде), разбиение на диапазоны по Z производится MS EXCEL автоматически и нет возможности на это повлиять. Ниже приведены изолинии для упомянутого выше гиперболоида. Это особый вид диаграммы – Проволочная контурная .

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

Объемные таблицы в Excel

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

Использование рабочих книг с несколькими листами обеспечивает следующие преимущества:

  • гибкость в оформлении рабочих листов;
  • возможность консолидации (объединения) данных.

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

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

Трехмерные ссылки

Excel использует ярлыки листов для определения адреса листа. Имя каждого листа последовательно увеличивается на единицу, начиная с Лист! (Sheetl). Трехмерные формулы включают области, которые охватывают более одного листа, т. е. определяют диапазон листов, содержащих данные.

Лист!: Лист2!В11
Квартал!: Квартал4!А1:А10

Объединение листов

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

  1. Выделите ярлык первого листа.
  2. Нажмите Shift и щелкните ярлык последнего листа.

Ярлыки объединенных листов станут выделенными. Для объединения несмежных листов:

  1. Выделите ярлык первого листа.
  2. Нажмите Ctrl и щелкайте ярлыки тех листов, которые должны быть включены.

Ярлыки объединенных листов станут выделенными.

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

Создание общего заголовка для группы листов

  1. Создайте в новой рабочей книге четыре листа с именами Отдел1, Отдел2, Отдел3 и Общий список.
  2. Выберите ярлык листа Отдел1.
  3. Нажмите Shift и щелкните ярлык листа ОтделЗ (будут выделены листы Отдел1, Отдел2, Отдел3, которые должны быть включены в группу).
  4. Выделите ячейку А1 и введите текст: Список сотрудников по отделам на 01.07.2000 г.
  5. Примените форматирование шрифта Размер 12, Полужирный.
  6. Выделите несколько соседних ячеек (А1:Е1 по размеру таблицы).
  7. Нажмите кнопку Объединить и поместить в центре — на панели Форматирование.
  8. Для отмены объединения листов щелкните ярлык листа Общий список, не принадлежащего к группе.
  9. Просмотрите заголовки на трех листах (Отдел1, Отдел2, Отдел3) и убедитесь, что заголовка на листе Общий список нет.
  10. Введите элементы списка по отделам.
  11. Скопируйте введенные данные на лист Общий список и сохраните файл с именем Список_сотрудников.х1s.

REDMOND

Ссылка на основную публикацию