Способ 2. Трехмерные формулы

Пример 1.

Задание: По исходной таблице “Учет количества деревьев” (Приложения, табл.1) построить СТ, сгруппировав данные по породе, № делянки и возрасту. Подсчитать количество деревьев в указанных группах.

Выполнение: В макете (рис.1) в область строка размещены два поля: порода дерева и Nп.п.делянки, в область столбец - поле Возраст. В области данные: Сумма по полю Количество. Готовая СТ (рис.2) выводится на листе рабочей книги Excel в режиме просмотра, где клетки, содержащие имена полей, выделены темным цветом. На пересечении строк с номерамиделянок (которые повторяются для каждой породы деревьев) и столбцов с возрастом выводятся величины, обозначающие общее количество деревьев данного возраста и породы деревьев, произрастающих на данной делянке. По внешнему полю Порода выводятся промежуточные итоги (Дуб всего, Клен всего и т.д.) и общие итоги по строкам и столбцам.

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

= Равно

> Больше

< Меньше

>= Больше или равно

<= Меньше или равно

<> Не равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

=ЕСЛИ(А1>3;10;20); =И(логическое_значение1;логическое_значение2...)
=ИЛИ(логическое_значение1;логическое_значение2...); =ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100); =ИСТИНА()

36. В окне открытого листа отсортируйте диапазон данных.

Перейдите к вкладке «Данные» и в группе «Структура» щелкните по кнопке «Структура». В окне «Настройка» (рис. 5.28) для расположения строки промежуточных итогов под диапазоном данным активируйте пункт «Итоги в строках под данными». Для расположения сверху диапазона данных отключите данный пункт. Закройте окно кнопкой «ОК».

37. Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.

Способ 1. Простые формулы

Самый простой способ. Ввести в ячейку чистого листа формулу

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

и скопировать ее на четыре ячейки вправо и на четыре вниз

Способ 2. Трехмерные формулы

Чуть более изящный способ, чем предыдущий. Формула может выглядеть немного иначе:

=СУММ('2001 год:2003 год'!B3)

Фактически - это суммирование всех ячеек B3 на листах с 2001 по 2003. То есть, в будущем, возможно поместить между этими листами дополнительные листы с данными, которые также станут учитываться при суммировании.

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

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

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

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

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

Для построения диаграммы нужно вызвать Мастер диаграмм. Это специальное средство для поэтапного (пошагового) построения диаграммы. Каждому этапу соответствует диалоговое окно, в котором необходимо указать нужные параметры. Окончание одного этапа приводит к вызову другого.
Диапазон данных, для которых строится диаграмма, может быть предварительно выделен перед вызова Мастера, а может быть указан на втором шаге Мастера. Рассмотрим работу Мастера диаграмм.

Вызов Мастера можно осуществить щелчком по кнопке

или ВставкаДиаграмма.
Шаг1 – «Тип диаграммы» - определение типа и разновидности диаграммы;
Шаг 2 – «Источние данных диаграммы» - определение диапазона данных, для которого диаграмма строится, подписей данных, расположение рядов, указание их имен и значений.

Шаг 3 – «Параметры диаграммы» - установка параметров диаграммы в соответствии с выбранным типом.
Шаг 4 – «Размещение диаграммы». Построенная диаграмма может быть расположена на том же листе, где размещены данные для ее построения, на другом листе книги, а может быть на специальном листе «Диаграмма».

40. Функция ПРОСМОТР возвращает значение из строки, столбца или массива. Функцияимеет две синтаксические формы: форму вектора и форму массива. Если требуется выполнить сложную проверку или проверку, превышающую предел для вложенных функций, то вместо функции ЕСЛИ можно использовать функцию ПРОСМОТР. См. примеры для формы массива.

Для правильной работы функции ПРОСМОТР данные, в которых производится просмотр, должны быть отсортированы в возрастающем порядке. Если это невозможно, рекомендуется использовать функции ВПР, ГПР или ПОИСКПОЗ.

41. При необходимости, Вы можете легко добавить к стандартным числовым форматам Excel свои собственные. Для этого выделите ячейки, к которым надо применить пользовательский формат, выберите в меню Формат - Ячейки - вкладка Число, далее - Все форматы. В появившееся справа поле Тип: введите маску формата из последнего столбца этой таблицы: На самом деле все, как обычно, очень просто. Как Вы уже, наверное, заметили Excel использует несколько спецсимволов в масках форматов:

0 (ноль) - одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 12 применить маску 0000, то получится 0012, а если к числу 1,3456 применить маску 0,00 - получится 1,35.

# (решетка) - одно необязательное знакоместо - примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится

(пробел) - используется как разделитель групп разрядов

[ ] - в квадратных скобках перед маской формата можно указать название цвета шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.

Плюс пара простых правил:

Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) - надо обязательно заключать в кавычки.

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