Абсолютные и относит. ссылки Excel

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

Использование относительных ссылок аналогично указанию направления движения по улице - "идти три квартала на север, затем два квартала на запад". Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.

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

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

Абсолютная ссылка на ячейку .иди область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.

Абсолютная ссылка может быть создана только при наборе формулы, перед адресом строки и столбца вводится знак доллара - $.

Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.

Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).

11.Меню "формат ячейки/число" Excel

Выбираем числовой формат для ячейки, числовой, денежный, финансовый, дата, время.

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

12.Меню «Вставка» Excel

13.Меню «Сервис/настройка» Excel

сервис/настройка: 1.Панель инструментов – выбираем какие панели инструментов отображать. 2. Команды – вынос команд на панель задач. 3. Параметры - настройка меню и панели инструментов, значков, эффектов.

Меню Формат Excel

Общие понятия Excel

1. Структура таблицы (пересечения строк и столбцов создают ячейки, куда заносятся данные);

2. Стандартный набор математических и бухгалтерских[1] функций;

3. Возможности сортировки данных;

4. Наличие средств визуального отображения данных (диаграмм).

Microsoft Excel - средство для работы с электронными таблицами, намного превышающее по своим возможностям существующие редакторы таблиц, первая версия данного продукта была разработана фирмой Microsoft в 1985 году. Microsoft Excel - это простое и удобное средство, позволяющее проанализировать данные и, при необходимости, проинформировать о результате заинтересованную аудиторию, используя Internet. Microsoft® Excel разработан фирмой Microsoft, и является на сегодняшний день самым популярным табличным редактором в мире. Кроме стандартных возможностей его отличает следующие возможности, он выводит на поверхность центральные функции электронных таблиц и делает их более доступными для всех пользователей. Для облегчения работы пользователя упрощены основные функции, создание формул, форматирование, печать и построение графиков.

Работа с макросами Excel

Выберите в меню Сервис команду Макрос и подкоманду Начать запись.

• В поле Имя макроса введите имя макроса (Макрос. Действие или набор действий, используемые для автоматизации выполнения задач. Макросы записываются на языке программирования Visual Basic для приложений (VBA).).

Примечания

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

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

• Если этот макрос потребуется запускать нажатием сочетания клавиш (Клавиши быстрого вызова. Функциональные клавиши или сочетания клавиш, такие как F5 или CTRL+A, которые служат для выполнения команд меню. В отличие от них, клавишами доступа называют сочетания клавиш, такие как ALT+Ф, которые переводят фокус в меню, на команду или на элемент управления.) на клавиатуре, введите букву в поле Сочетание клавиш. Допускается использование сочетаний CTRL+ буква (для строчных букв) или CTRL+SHIFT+ буква (для прописных букв), где буква — любая буквенная клавиша на клавиатуре. Нельзя использовать сочетания клавиш с цифрами и специальными знаками, такими как @ или #.

Примечание. Выбранное сочетание клавиш заменяет все совпадающие стандартные сочетания клавиш Microsoft Excel на то время, пока открыта книга, содержащая данный макрос.

• В поле Сохранить в выберите книгу, в которой требуется сохранить макрос.

Если этот макрос требуется всегда при работе в Microsoft Excel, выберите вариант Личная книга макросов.

• Если необходимо добавить описание макроса, введите его в поле Описание.

• Нажмите кнопку OK.

• Если макрос требуется выполнять относительно позиции активной ячейки, запишите его, используя относительные ссылки на ячейки. На панели инструментов Остановить запись нажмите кнопку Относительная ссылка , чтобы она осталась нажатой. Запись макроса будет продолжена с использованием относительных ссылок, пока не будет закрыт Microsoft Excel или не будет еще раз нажата кнопка Относительная ссылка , после чего она останется не нажатой.

• Выполните макрокоманды, которые нужно записать.

• На панели инструментов Остановить запись нажмите кнопку Остановить запись .

Сортировка Excel.

Сортировка строк по возрастанию (от A до Я или от 0 до 9) или убыванию (от Я до A или от 9 до 0)

Укажите ячейку в сортируемом списке.

Нажмите кнопку Сортировать по возрастанию или Сортировать по убыванию .

Сортировка строк по двум или трем условиям отбора (столбцам)

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

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

В меню Данные выберите команду Сортировка.

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

Выберите остальные параметры сортировки и нажмите кнопку OK.

Сортировка строк по четырем условиям отбора (столбцам)

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

В меню Данные выберите команду Сортировка.

В окне Сортировать по сначала укажите столбец наименьшей важности.

Нажмите кнопку OK.

В меню Данные выберите команду Сортировка.

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

Выберите остальные параметры сортировки и нажмите кнопку OK.

Сортировка по столбцам, а не по строкам

В большинстве случаев требуется отсортировать строки. Данная процедура описывает сортировку столбцов.

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

В меню Данные выберите команду Сортировка.

Нажмите кнопку Параметры.

Установите переключатель Сортировать в положение Столбцы диапазона и нажмите кнопку OK.

Выберите строки, по которым требуется отсортировать столбцы, в полях Сортировать по и Затем по.

Формат ячейки Excel

Формат – ячейки (CTRL+1)

Число: общий, денежный, финансовый, дата, время, процент, дробь, текстовый.

Выравнивание:по горизонтали, по вертикали, ориентация (град), отображение, направление текста.

Шрифт: стиль, начертание, размер, подчеркивание, цвет.

Граница: тип линии, цвет, внешние, внутренние.

Вид: заливка, узор, образец

Защита: защищаемая ячейка, скрыть формулы.

Формат данных Excel

Данные в MS Excel выводятся на экран в определенном формате. По умолчанию информация выводится в формате Общий. Можно изменить формат представления информации в выделенных ячейках. Для этого выполните команду Формат / Ячейки.

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

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

Стиль представления данных

Одним из способов упорядочения данных в Excel является введение стиля. Для создания стиля используется команда Формат / Стиль. Выполнение этой команды открывает окно диалога “Стиль”.

Формулы Excel

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

=5+2*3

Создание простой формулы: =128+345

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

Пример формулы Описание

=128+345 Складывает 128 и 345

=5^2 Возводит 5 в квадрат

1. Щелкните ячейку, в которую требуется ввести формулу.

2. Введите = (знак равенства).

3. Введите формулу.

4. Нажмите клавишу ENTER.

Создание формулы, содержащей ссылки или имена: =A1+23

Приведенные ниже формулы содержат относительные ссылки (Относительная ссылка. Адрес ячейки в формуле, определяемый на основе расположения этой ячейки относительно ячейки, содержащей ссылку. При копировании ячейки относительная ссылка автоматически изменяется. Относительные ссылки задаются в форме A1.) на другие ячейки и их имена (Имя. Слово или строка знаков, представляющие ячейку, диапазон ячеек, формулу или константу. Понятные имена, такие как «Продукты», используют для ссылок на диапазоны, названия которых трудно запомнить, например, Продажи!C20:C30.). Ячейка, содержащая формулу, называется зависимой ячейкой, если ее значение зависит от значений в других ячейках. Например, ячейка B2 является зависимой, если она содержит формулу =C2.

Пример формулы Описание

=C2 Использует значение в ячейке C2

=Лист2!B2 Использует значение в ячейке B2 на Лист2

=Ответственность-Актив Вычитает ячейку Ответственность из ячейки Актив

1. Щелкните ячейку, в которую требуется ввести формулу.

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

3. Выполните одно из следующих действий.

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

o Чтобы создать ссылку на именованный диапазон, нажмите клавишу F3, выберите имя в поле Вставка имени и нажмите кнопку ОК.

4. Нажмите клавишу ENTER.

Создание формулы, содержащей функцию: =СРЕДНЕЕ(A1:B4)

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

Пример формулы Описание

=СУММ(A:A) Суммирует все числа в столбце A

=СРЕДНЕЕ(A1:B4) Вычисляет среднее значение всех чисел в диапазоне

1. Щелкните ячейку, в которую требуется ввести формулу.

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

3. Выберите функцию, которую нужно использовать. Можно ввести запрос с описанием операции, которую требуется выполнить, в поле Поиск функции (например, по словам «сложение чисел» будет найдена функция СУММ), или выбрать категорию в поле или категорию.

4. Введите аргументы (Аргумент. Значения, используемые функцией для выполнения операций или вычислений. Тип аргумента, используемого функцией, зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и именами.). Чтобы ввести ссылки на ячейки в качестве аргументов, нажмите кнопку Свернуть диалоговое окно чтобы временно скрыть его. Выделите ячейки на листе и нажмите кнопку Развернуть диалоговое окно .

5. По завершении ввода формулы нажмите клавишу ENTER.

Создание формулы с вложением функций: =ЕСЛИ(СРЕДНЕЕ(F2:F5)>50; СУММ(G2:G5);0)

Вложенные функции используют функции как один из аргументов другой функции. Следующая формула суммирует набор чисел (G2:G5), только если среднее значение другого набора чисел (F2:F5) больше 50. В противном случае она возвращает значение 0.

1.Щелкните ячейку, в которую требуется ввести формулу.

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

3.Выберите функцию, которую нужно использовать. Можно ввести запрос с описанием операции, которую требуется выполнить (например, «сложение чисел» возвращение функции СУММ) в поле Поиск функции, или выбрать категорию в поле или категорию.

4.Введите аргументы (Аргумент. Значения, используемые функцией для выполнения операций или вычислений. Тип аргумента, используемого функцией, зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и именами.).

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

oЧтобы ввести другую функцию в качестве аргумента, введите функцию в соответствующее окно аргумента. Например, можно добавить СУММ(G2:G5) в текстовое поле значение_если_истина.

oЧтобы переключиться между частями формулы, отображаемыми в диалоговом окне Аргументы функции, нажмите кнопку мыши на имени формулы в списке формул. Например, при нажатии на функции ЕСЛИ появится аргумент для данной функции.

Работа с функциями Excel

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

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

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

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

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

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

Типы функций

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

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

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

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

Итоги Excel

Вы можете подвести промежуточные итоги в базе данных Microsoft Excel, предварительно отсортировав список по столбцу, для которого необходимо подвести промежуточный итог. Для подведения итогов необходимо выбрать команду Subtotals (Итоги) из меню Data (Данные).

В диалоговом окне Subtotals (Промежуточные итоги) нужно указать следующее:

•Столбец, содержащий группы, по которым необходимо подвести итоги, из списка At each change in (При каждом изменении в). Это должен быть столбец, по которому проводилась сортировка списка.

•Функцию, необходимую для подведения итогов, из списка Use function (Операция).

•Столбцы, содержащие значения, по которым необходимо подвести итоги, в спискеAdd subtotal to (Добавить итоги по).

Вы можете также

•Replace current subtotals (Заменить текущие итоги), если они уже подводились раньше.

•Добавить Page break between groups (Конец страницы между группами).

•Подвести окончательные Summary below data (Итоги под данными).

•Remove All (Убрать все) промежуточные итоги.

Автофильтр Excel

сформируйте список так, как это было описано в выпуске про сортировку.

Затем выберите пункт меню <Данные - Фильтр - Автофильтр> (menu Data - Filter - AutoFilter).

Совет! Если задать список через меню <Данные - Списки - Создать список> (menu Data - List - Create List) или клавиши Ctrl+L, в списке автоматически включится автофильтр.

В результате в заголовке списка появятся <флажки> с выпадающими меню автофильтра. Здесь Вы увидите несколько вариантов отбора строк нашего списка.

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

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

В-третьих, можно отобрать строки по сложным критериям, выбрав пункт <Условие:>.

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

Условия, как и в функции =ЕСЛИ(), могут относиться как к текстовым значениям, так и к числам. Например:

* Чтобы показать все записи, начинающиеся на букву А, выберите тип условия <равно> и значение <А*>;

* Чтобы показать все записи, объем продаж в которых больше 2000 руб., выберите тип <больше или равно> и значение 2000.

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

О том, что список отфильтрован, свидетельствует надпись в строке состояния:

и отображение номеров строк ячеек синим цветом:

Обратите внимание: на рисунке вторая строка скрыта. Это можно заметить по более жирной границе между подписью первой и третье строк.

Чтобы снова показать все записи, выберите соответствующий пункт в выпадающем меню автофильтра в каждом столбце или просто отключите автофильтр. Для этого снова войдите в меню <Данные - Фильтр - Автофильтр> (menu Data - Filter - AutoFilter) и снимите галочку.

Расширенный фильтр Excel

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

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

1. Поместите указатель ячейки внутри списка. В этом случае Excel автоматически распознает диапазон списка и представит ссылку на диапазон в диалоговом окне.

2. Выполните команду Данные – Фильтр – Расширенный фильтр. Поместите курсор ввода в поле “Диапазон условий” и выделите соответствующий диапазон в рабочем листе.

3. Закройте диалоговое окно нажатием кнопки “ОК”. На экране теперь будут отображены записи, удовлетворяющие заданным критериям.

Вы можете применить в рабочем листе только один расширенный фильтр.

Если в результате применения расширенного фильтра не должны быть отображены повторяющиеся записи, в диалоговом окне “Расширенный фильтр” установите флажок параметра “Только уникальные записи”.

Чтобы после фильтрации задать отображение в рабочем листе всех записей списка, выполните команду Данные – Фильтр – Отобразить все.

25.Меню "формат ячейки/шрифт" Excel

26.Меню "формат ячейки/выравнивание" Excel

27.Меню "формат ячейки/границы" Excel

28.30. Меню "формат/столбец" Excel

29.Меню "формат/строка" Excel

31.Меню "формат/автоформат" Excel

Для быстрого оформления таблицы можно воспользоваться командой Автоформат :

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

Из меню Format ( Формат ) выберите команду AutoFormat ( Автоформат ).

Из списка доступных форматов выберите стиль.

Щелкните на кнопке ОК, согласившись на форматирование таблицы с использованием выбранного стиля.

Для частичного применения автоформата нажмите кнопку Options (Параметры) и снимите флажки для форматов , которые не нужно применять.

32.Меню "формат/условное форматирование" Excel

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

Задайте Condition (Условие), при котором к содержимому ячеек будет применен специальный формат и определите необходимое форматирование, кликнув на клавише Format (Формат) . При необходимости можно добавить еще одно условие, щелкнув на клавише Add (А также): условный формат может содержать до трех условий.