MS Excel: Абсолютный и относительный адрес

Абсолютный адрес это такой адрес. Значение которого остается абсолютным, т.е при копировании значение содержимого ячейки не изменяется. Такой адрес содержит в себе знак $. Для получения абсолютного адреса войдите в режим редактирования ячейки нажатием клавиши F2, поставьте курсор после адреса, которую хотите сделать абсолютным и нажмите клавишу F4. Абсолютные адреса записываются в виде $A$1, $D$5 и т.д.

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

Теперь про абсолютные и относительные ссылки – чем они отличаются.
1. Абсолютная ссылка содержит знак $, причем если этот знак стоит перед буквой, то это абсолютная ссылка на поле (стобец), а не на поле и запись. Т.е. если ссылка на ячейку абсолютная полностью, то знак $ стоит перед двумя частями адреса – буквой и цифрой, например $B$1.
2. Относительная ссылка это адрес без знака $. Пример B1.
3. Конечно же ссылка вполне может быть и смешанной – например B$1.
В чем разница между ссылками.
Разница в том, что копируя формулу, в которой будет относительная ссылка на ячеку мы получим изменившуюся формулу. Например скопировав формулу =A1*2 из ячейки B1, в C1 мы получим =C1*2. Скопировав подобную формулу несколько раз вы можете получить разные результаты и тогда поймете зависимость – копируя в другой столбец мы получаем изменение буквы, а если копируем формулу в другую строку мы видим, что изменяется цифра. Соответственно, если вы копируете в соседнюю строку и столбец (поле), то меняются обе части адреса – и буква и цифра.
Если в вашей формуле перед какой то частью адреса стоит знак $, то эта часть является абсолютной ссылкой на строку или столбец. Например копируя ту же формулу =$A1*2 из ячейки B1, в C1 мы получим то же самое, т.к. должны была поменяться буква, но не поменялась т.к. ссылка на нее абсолютная.
Если скопируем формулу =A$1*2 из ячейки B1, в C2, то она превратится в =B$1*2. Ссылка копировалась и в соседний столбец и в соседнюю строку, соответственно, при копировании, должны были поменяться и буква и цифра, но поменялась только буква, потому, что ссылка на нее относительная.
Для обычных задач использовать знак $ и делать ссылки абсолютными не обязательно, если вы конечно не будете добавлять какие-то строки или столбцы - получится смещение расположения ячеек и соответственно изменятся адреса в некоторых формулах.

 

 

17[V2]. MS Excel: Графики

 

 

MS Excel: Диаграммы

Графики в Excel – эффективное средство наглядного отображения расчетов и результатов расчетов.

Каждый тип диаграммы имеет несколько вариантов представления. Так, например, стандартная гистограмма представлена в 7 вариантах, а линейчатая диаграмма - в 6 вариантах.
Чтобы увидеть, как ваши данные будут выглядеть при выборе различных типов диаграмм, нажмите и не отпускайте кнопку «Просмотр результата». Поле «Вид» при этом будет заменено полем «Образец», в котором будет отображена диаграмма.
Excel предлагает 14 типов диаграмм, каждый из которых подходит для эффективного представления данных определенного класса. Их область применения приведена в таблице

Область применения диаграмм различных типов

Тип диаграммы	Область применения
Гистограмма	Удобна для отображения изменения данных на протяжении отрезка времени. Для наглядного сравнения различных величин используются вертикальные столбцы, которые могут быть объемными и плоскими. Высота столбца пропорциональна значению, представленному в таблице.
Линейчатая	Дает возможность сравнивать значения различных показателей. Внешне напоминают повернутые на 90 градусов гистограммы. Такой поворот позволяет обратить большее внимание на сравниваемые значения, чем на время.
График	Показывает, как меняется один из показателей (Y) при изменении другого показателя (X) с заданным шагом. Excel позволяет построить объемные графики и ленточные диаграммы. Удобен для отображения математических функций.
Круговая диаграмма	Показывает соотношения между различными «Частями одного ряда данных, составляющего в сумме 100%». Обычно используется в докладах и презентациях, когда необходимо выделить главный элемент и для отображения вклада в процентах каждого источника.
Точечная диаграмма	Показывает изменение численных значений нескольких рядов данных (ось Y) через неравные промежутки (ось X), или отображает две группы чисел как один ряд координат х и у. Располагая данные, поместите значения х в один столбец или одну строку, а соответствующие значения у в соседние строки или столбцы. Обычно используется для научных данных.
Диаграмма с областями	Показывает изменения, происходящие с течением времени. Отличается от графиков тем, что позволяет показать изменение суммы значений всех рядов данных и вклад каждого ряда.
Кольцевая диаграмма	Позволяет показать отношение частей к целому. Может включать несколько рядов данных. Каждое кольцо кольцевой диаграмме соответствует одному ряду данных.
Лепестковая диаграмма	Вводит для каждой категории собственные оси координат, расходящиеся лучами из начала координат. Линии соединяют значения, относящиеся к одному ряду. Позволяет сравнивать совокупные значения нескольких рядов данных. Например, при сопоставлении количества витаминов в разных соках образец, охватывающий наибольшую площадь, содержит максимальное количество витаминов.
Поверхность	Используется для поиска наилучшего сочетания в двух наборах данных. Отображает натянутую на точки поверхность, зависящую от двух переменных. Как на топографической карте, области, относящиеся к одному диапазону значений, выделяются одинаковым цветом или узором. Диаграмму можно поворачивать и оценивать с разных точек зрения.
Пузырьковая диаграмма	Отображает на плоскости наборы из трех значений. Является разновидностью точечной диаграммы. Размер маркера данных показывает значение третьей переменной. Значения, которые откладываются по оси X, должны располагаться в одной строке или в одном столбце. Соответствующие значения оси Y и значения, которые определяют размеры маркеров данных, располагаются в соседних строках или столбцах.
Биржевая диаграмма	Обычно применяется для демонстрации цен на акции. Диаграмму можно использовать для демонстрации научных данных, например для отображения изменений температуры. Биржевая диаграмма, которая измеряет объемы, имеет две оси значений: одну для столбцов, которые измеряют объем, и другую - для цен на акции. Для построения биржевых диаграмм необходимо расположить данные в правильном порядке.
Цилиндрическая, коническая и пирамидальная диаграммы	Имеют вид гистограммы со столбцами цилиндрической, конической и пирамидальной формы. Позволяют существенно улучшить внешний вид и наглядность объемной диаграммы.

«Мастер диаграмм», шаг 2. Корректирование интервала данных для диаграммы

На втором шаге построения диаграммы Мастер диаграмм дает возможность коррекции размеров выделенного диапазона с данными.
На вкладке «Диапазон данных» можно уточнить диапазоны ячеек и определить какие данные на диаграмме будут строками, а какие - столбцами.
Большинство типов диаграмм может быть представлено несколькими рядами данных. Исключение составляет круговая диаграмма, отображающая только один ряд данных.
Названия рядов можно изменить на вкладке «Ряд», в поле «Имя», не изменяя при этом текст на листе.

«Мастер диаграмм», шаг 3. Оформление диаграммы

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

«Мастер диаграмм», шаг 4. Выбор места расположения диаграммы

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

Построение графиков, отображающих связь между X и У

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

выделить столбец, в котором представлены значения переменной У;

нажать кнопку «Мастер диаграмм» на панели инструментов;

в диалоговом окне «Мастер диаграмм» на первом шаге открыть вкладку «Нестандартные», выбрать тип: «Гладкие графики» и нажать кнопку «Далее».

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

Редактирование диаграммы

Если выделить диаграмму, то ее можно перемещать, добавлять в нее данные, можно выделять, форматировать, перемещать и изменять размеры большинства входящих в него элементов.
Можно даже изменить тип уже созданной диаграммы. Для изменения типа диаграммы выделите ее. В контекстном меню выберите пункт «Тип диаграммы».
Если лист диаграммы активен, то в него можно добавлять данные и форматировать, перемещать и изменять размеры большинства входящих в него объектов. При перемещении указателя мыши по диаграмме отображаются всплывающие подсказки, с названием элемента диаграммы. Чтобы выбрать элемент диаграммы с помощью клавиатуры, используйте клавиши со стрелками.
Ряды данных, подписи значений и легенды можно изменять поэлементно. Например, чтобы выбрать отдельный маркер данных в ряде данных, выберите нужный ряд данных и укажите маркер данных. Каждый из элементов диаграммы можно форматировать отдельно. Имя элемента диаграммы выводится в подсказке в случае, если установлен флажок «Показывать имена» на вкладке «Диаграмма» диалогового окна «Параметры».
Чтобы перейти в режим форматирования какого-либо элемента: координатной оси, названия диаграммы, отдельных рядов данных, щелкните на этом элементе. Вокруг выделенного элемента появится штриховая рамка. Имя графического объекта отобразится в поле строки формул. Выделенный элемент можно переместить, удерживая нажатой кнопку мыши.

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

 

MS Excel: ВПР

ВПР

Ищет значение в первом столбце массива таблица и возвращает значение в той же строке из другого столбца массива «таблица».

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

Синтаксис

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

Искомое_значение. Значение, которое должно быть найдено в первом столбце массива «таблица». Искомое_значение может быть значением или ссылкой. Если искомое значение меньше наименьшего значения в первом столбце массива «таблица», ВПР возвращает значение ошибки #Н/Д.

Таблица. Два или более столбцов данных. Можно использовать ссылку на интервал или имя интервала. Значения в первом столбце массива «таблица» являются значениями, поиск которых выполняется с помощью аргумента «искомое_значение». Эти значения могут быть текстовыми строками, числами или логическими значениями. Текстовые строки сравниваются без учета регистра букв.

Номер_столбца. Номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее. Если «номер_столбца»:

Меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!.

Больше, чем количество столбцов массива «таблица», то функция ВПР возвращает значение ошибки #ССЫЛ!.

Интервальный_просмотр. Логическое значение, которое определяет, нужно ли, чтобы функция ВПР искала точное или приближенное соответствие:

Если этот аргумент имеет значение ИСТИНА или опущен, возвращается точное или приблизительно соответствующее значение. Если точное соответствие не найдено, то возвращается следующее максимальное значение, которое меньше, чем искомое_значение.

Значения в первом столбце массива «таблица» должны быть отсортированы по возрастанию. В противном случае ВПР может возвратить неправильные результаты. Данные можно упорядочить следующим образом: в меню Данные выбрать команду Сортировка и установить переключатель По возрастанию. Дополнительные сведения см. в разделе Порядок сортировки по умолчанию.

Если значение этого аргумента равно ЛОЖЬ, ВПР вернет только точное соответствие. В этом случае значения в первом столбце массива «таблица» не обязательно должны быть отсортированы. Если в первом столбце массива «таблица» аргументу «искомое_значение» соответствует два и более значений, используется первое найденное значение. Если найти точное соответствие не удается, то возвращается значение ошибки #Н/Д.

Замечания

При поиске текстовых значений в первом столбце массива «таблица» убедитесь, что в данных в первом столбце массива «таблица» отсутствуют пробелы в начале и конце строки, несовместимые знаки прямых (' или ") и изогнутых (‘ или “) кавычек или непечатаемые знаки. В подобных случаях ВПР может вернуть неправильное или неожиданное значение.

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

Если массив «интервальный_просмотр» имеет значение ЛОЖЬ, а значения массива «интервальный_просмотр» имеют текстовый формат, в массиве «интервальный_просмотр» можно использовать подстановочные знаки, вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку; звездочка соответствует любой последовательности знаков. Если нужно найти вопросительный знак или звездочку, то следует поставить перед ними знак тильда (~).

Пример 1

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

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

А B C Плотность Вязкость Температура 0,457 3,55   0,525 3,25   0,616 2,93   0,675 2,75   0,746 2,57   0,835 2,38   0,946 2,17   1,09 1,95   1,29 1,71   Формула Описание (результат)   =ВПР(1;A2:C10;2) Используя приближенное соответствие, ищет значение 1 в столбце A, находит максимальное значение, меньшее или равное 1 в столбце A (0,946), а затем возвращает значение из столбца B в той же строке (2,17).   =ВПР(1;A2:C10;3;ИСТИНА) Используя приближенное соответствие, ищет значение 1 в столбце A, находит максимальное значение, меньшее или равное 1 в столбце A (0,946), а затем возвращает значение из столбца C в той же строке (100).   =ВПР(0,7;A2:C10;3;ЛОЖЬ) Используя точное соответствие, ищет значение 0,7 в столбце A. Так как в столбце A точное соответствие отсутствует, возвращается сообщение об ошибке (#Н/Д).   =ВПР(0,1;A2:C10;2;ИСТИНА) Используя приближенное соответствие, ищет значение 0,1 в столбце A. Так как 0,1 меньше, чем наименьшее значение в столбце A, возвращается сообщение об ошибке (#Н/Д).   =ВПР(2;A2:C10;2;ИСТИНА) Используя приближенное соответствие, ищет значение 2 в столбце A, находит максимальное значение, меньшее или равное 2 в столбце A (1,29), а затем возвращает значение из столбца C в той же строке (1,71).