Выделение ячеек и диапазонов.

Для выделения с помощью мыши:

столбца - щелкнуть мышью на букве - имени столбца;

несколько столбцов - не отпуская кнопку мыши после щелчка, протянуть курсор мыши;

строки - щелкнуть мышью на числе - имени строки;

несколько строк - не отпуская кнопку мыши после щелчка, протянуть мышь;

диапазона – 1)щелкнуть мышью на начальной ячейке диапазона и, не отпуская кнопку, протянуть мышь на последнюю ячейку; 2) щелкнуть на угловой ячейке диапазона, нажать клавишу «Shift», щелкнуть мышкой на ячейке в противоположном углу.

рабочего листа - щелкнуть мышью на пересечении имен столбцов и строк (левый верхний угол таблицы).

несколько несмежных областей – выделить одну область, нажать клавишу «Ctrl», выделять каждую следующую при нажатой клавише «Ctrl».

<Esc> - выход из режима выделения.

 

Редактирование и удаление данных.

– см. учебную карту №1.

Добавление строк, столбцов, листов

Для добавления нового листа достаточно в меню ВСТАВКА найти ЛИСТ. Лист встает перед текущим.

Для добавления строки (столбца) нужно выделить ту строку (или столбец), перед которой будет вставка, и выполнить ВСТАВКА, СТРОКИ

Автозаполнение ячеек.

 

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

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

Маркер заполнения. Указатель мыши на нем принимает форму черного перекрестия. ª

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

Создание списка для автозаполнения:. Два варианта. Первый: данные новые, выбираем мышкой меню Сервис, пункт Параметры, вкладка Списки, выбрать НОВЫЙ СПИСОК, в окне справа ввести элементы списка столбиком без знаков препинания, нажать кнопку Добавить и ОК. Второй: список уже введен на листе, для дальнейшего его использования нужно ввести его в память, нужно выделить его, выбирать мышкой меню Сервис, пункт Параметры, вкладка Списки, кнопка Импорт, ОК.

 

Действия, произведенные при перетаскивании маркера автозаполнения, в зависимости от выделенных ячеек и введенных данных. См. задание № 2.

 

Количество выделенных ячеек	Данные в ячейках	Произведенное действие	Примеры
Данные в ячейках	Кол-во заполненных ячеек.	Результат
Одна	Текст, не являющийся эл-том списка	Копирование текста	Слово «тема»		Во всех пяти слово «тема»
Одна	Число	Копирование числа	Число «120»		Во всех трех число «120»
Одна	Элемент списка	Вывод последовательности элементов списка	«понедельник»		Все дни недели по порядку
Две	Два числа	Арифметическая прогрессия, с шагом = разности первой и второй ячеек	1) 1 и 2   2) 2 и 4   3) 0 и 0,1		Ряд с1 до 10   Четные числа от1 до 20 Все десятые от 0 до 1
две	Любой текст и порядковое числительное	Повторяющийся тескт + прогрессия чисел	Упр. 1		Упр.1 Упр.2 Упр.3
одна	дата	Лев.кн.мыши выводит след дату, а пр.кн.мыши дает разные варианты дат, например, По рабочим дням	25.02.11   25.02.11		25.02.11 26.02.11 27.02.11   25.02.11 Пропущены 28.02.11 выходные
одна	Формула	Копирование формулы	* Этот случай будет рассмотрен в отдельном пункте.

Формат ячеек

Числовые данные, которые в дальнейшем будут использоваться в формулах для расчета, нельзя вводить с клавиатуры одновременно с текстовым сообщением об единицах измерения. Для отображения данного текста применяется формат ячеек – меню Формат®Ячейки.®вкладка Число. Выбрать из предлагаемого нужный и нажать ОК

 

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

Более подробно смотри Лабораторную работу №3.

Ввод формул.

Все вычисления в Excel выполняются при помощи формул, введенных в ячейку, где ожидается результат данных вычислений. Формула всегда начинается со знака «=», может содержать до 240 символов. Большинство формул не будут (и не должны) включать численные значения. Вместо этого, они должны ссылаться на другие ячейки, которые содержат такие значения. Ячейки такого типа часто называют переменными, поскольку их содержимое может изменяться, в то время как ссылка на ячейку в формуле остается неизменной (хотя результат формулы изменяется). Эта методика позволяет быстро модифицировать данные рабочих листов без необходимости редактирования самих формул.

 

Пример организации формул:

 

рабочий момент результат

	А	В			А	В
	Цена	2000р.			Цена	2000р.
	НДС	20%			НДС	20%
	Цена+ндс	=В1+В1*В2			Цена+ндс	2400р.

 

Изменение ячеек В1 или В2 ведет к изменению результата в В3.

Таким образом формула зависит от ячейки, на которую ссылается, - любые изменения значения ячейки влияют на результат формулы. Ячейка, содержащая формулу, называется зависимой ячейкой, ее значение зависит от значения другой ячейки. Ячейки, на которые существуют ссылки, называются влияющими. Цепочки влияющих и зависящих ячеек можно прослеживать с помощью команды Сервис-Зависимости.

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

§ + и - сложение и вычитание

§ * и / умножение и деление

§ ^ возведение в степень

§ = равно

§ <> не равно

§ >(>=) больше чем (больше или равно чем)

§ <(<=) меньше чем (меньше или равно чем)

Несколько замечаний по вводу формул, о которых нужно помнить:

§ Формула начинается со знака "="

§ Формула записывается в одну строку.

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

§ Адрес ячейки (ссылку) лучше вводить не с клавиатуры (в этом случае часто вводятся русские символы, что является ошибкой в понятии Excel), а щелчком мыши на влияющей ячейке.

§ Если во влияющей ячейке содержится число в процентном формате (например, В2 содержит 20%), то при умножении какого-либо числа на данное вычисляется заданный процент (т.е. число 20% воспринимается как 0.2 и ячейка, содержащая формулу =А2*В2, выдаст результат равный 20% от числа, содержащегося в ячейке А2)

§ Ввод формулы заканчивается нажатием клавиши <Enter>

 

Ссылки

 

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

Известно, что для ссылки на диапазон ячеек надо ввести адрес ячейки, находящейся в левом верхнем углу диапазона, двоеточие (:), а затем адрес ячейки, находящееся в правом нижнем углу диапазона. Существуют также, так называемые трехмерные ссылки. Они используются при необходимости анализа данных, одной и той же ячейки для диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Excel использует все листы, хранящиеся между начальным и конечным именами указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!В5) суммирует все значения, содержащиеся в ячейке В5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

Для создания такой формулы нужно:

1. Указать ячейку, в которую следует ввести функцию (эта ячейка будет содержать внешнюю ссылку).

2. Если создается новая формула, то сначала нужно набрать =(знак равенства), ввести имя функции, а затем ввести открывающую круглую скобку.

3. Указать ярлычок первого листа, на который нужно сослаться.

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

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

6. Для завершения ввода формулы нажать клавишу ENTER.

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

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

=СУММ('С: Отчеты[Бюджет.хls]Годовой' С 10:С25).

Исходная книга для этой формулы не открыта, поэтому ссылка включает полный путь. Если бы Бюджет.xls был открыт, формула имела бы вид

=СУММ([Бюджет.xls]Годовой!С 10:С25).

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

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

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

2. Из списка Исходный файл выбрать источник для связанного объекта, а затем выбрать команду Обновить. Для выделения нескольких связанных объектов, не опуская клавишу СTRL, выбрать каждый связанный объект.

Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. Предположим, что используется формула =СУММ(Лисг2:Лист6!А2:А5), суммирующая содержимое ячеек с А2 по А5 с лист2 по лист6 включительно.

Вставка или копирование. Если между листом 2 и листом 6 книги вставить новые листы, Microsoft Excel добавит в сумму содержимое ячеек с А2 по А5 на новых листах.

Удаление. Если между листом 2 и листом 6 книги удалить листы, Microsoft Excel исключит из суммы содержимое ячеек удаленных листов.

Перемещение. Если переместить листы, находящиеся между листом 2 и листом 6 книги и разместить их таким образом, что они будут расположены перед листом 2 или после листа 6 Microsoft Excel исключит из суммы содержимое ячеек перемещенных листов.

Перемещение граничного листа. Если переместить лист 2 или лист 6 в новое место книги. Microsoft Excel включит в сумму содержимое ячеек листов, находящихся между листом 2 и листом б включительно.

Удаление граничного листа. Если удалить лист 2 пли лист 6, Microsoft Excel включит в сумму содержимое ячеек листов, находившихся между ними.

 

Копирование формул.

 

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

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

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

· При перемещении ячеек, на которые имеются ссылки в формуле, эти ссылки настраиваются и «следуют» за ячейками в новое месторасположение.

· При копировании ячеек, на которые имеются ссылки в формуле, эти ссылки не изменяются, - они указывают на исходные ячейки.

 

 

Пример 1		A	B			А	В
	=A1+A2
						=А1+А2

 

 

Перемещение содержимого ячейки А3 в ячейку С5

 

Пример 1		A	B	C	D	E
	=A1+A2
			=С3+С4

 

Копирование содержимого ячейки А3 в С5

 

При копировании ячейки А3 в С5 ссылки на ячейки А1и А2 «переползут» на ячейки С3 и С4 соответственно. Эти ссылки (а также им соответствующие адреса) называются относительными. Этими ссылками (адресами) удобно пользоваться когда, к примеру, в одном из столбцов (одной строке) выводятся данные, вычисленные по одной и той же формуле, но по разным данным, расположенным в последовательно идущих строках (столбцах). Для этого вводят формулу в первую из расчетных ячеек, а затем, использую прием автозаполнения, копируют ее в последующие ячейки. Результат приведен в следующем примере

 

 

Пример2 Копирование ячейки, содержащей формулу	Цена	Количество	Выручка		Цена	Количество	Выручка
2090,35		=В2*С2	2090,35		=В2*С2
						=В3*С3
2248,42				2248,42		=В4*С4
2698,36				2698,36		=В5*С5
3315,72				3315,72		=В6*С6
3588,86				3588,86		=В7*С7

 

Если в формуле используется ссылка на какое-то, одинаковое для всех имеющихся данных число, но со временем изменяющее свое значение (например, процент пенсионного налога), то это число вводят в отдельно стоящую ячейку, а в формулах, использующих его, указывают «зафиксированный» адрес. Ссылка (адрес) в этом случае называется абсолютной. Абсолютный адрес ячейки записывается со знаком $ и выглядит это как в следующем примере.

 

	…	F	G	H
		Пенс.налог	1%
		Начислено	Отчисления в пенс фонд	К выдаче
			=F8*$G$5
			=F9*$G$5
			=F10*$G$5

 

Клавиша F4 производит переход из одного типа адреса в другой:

§ A1 – относительный,

§ $A$1– абсолютный (фиксируется адрес одной определенной ячейки,

§ $A1– смешанный (фиксируется ссылка на определенный столбец, а ссылка на строку смещается вслед за формулой),

§ A$1– смешанный (фиксируется ссылка на определенную строку, а ссылка на столбец смещается вслед за формулой),

§

Пример 4. Использование смешанной ссылки, где зафиксирован столбец.

 

	A	B	C	D	E	F	G	H
	Наименование товара	Закупочная цена	Магазин 1	Магазин 2	Магазин 3
	К-во	К оплате	К-во	К оплате	К-во	К оплате
	Молоко	5р		=$B3*C3		=$B3*E3		=$B3*G3
	Кефир	4р		=$B4*C4		=$B4*E4		=$B4*G4
	Масло(пачка)	8р		=$B5*C5		=$B5*E5		=$B5*G5
	Сметана	12р		=$B6*C6		=$B6*E6		=$B6*G6
	И т.д.

 

	A	B	C	D	E	F	G	H
	Наименование товара	Закупочная цена	Магазин 1	Магазин 2	Магазин 3
	К-во	К оплате	К-во	К оплате	К-во	К оплате
	Молоко	5р		250 р		500 р		350 р
	Кефир	4р		180 р		360 р		320 р
	Масло(пачка)	8р		160 р		480 р		320 р
	Сметана	12р		180 р		600 р		420 р
	И т.д.

 

Вставка функций

 

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

Мастер функций всегда дает краткое описание выбранной функции. Далее открывается специальное окно-форма для ввода параметров функции: количество полей для ввода в форме равно количеству аргументов функции. Есть аргументы обязательные – они выделены жирным шрифтом, а есть необязательные, которые могут быть не заданы или заданы в зависимости от условий задачи. В описании функции они помещены в квадратные скобки [].

Функции имеют следующий формат:

НАЗВАНИЕ_ФУНКЦИИ( Арг1;Арг2;…;АргN),
если есть необязательный аргумент и он не задан, то функция выглядит следующим образом:
НАЗВАНИЕ_ФУНКЦИИ (Арг1;Арг2;;Арг4;…;АргN) – пропущен Арг3

Финансовые функции:

Некоторые обозначения (в скобках названия версии Excel-97):

Ставка (норма) – процентная ставка за расчетный период (если год – годовой процент; квартал – годовой процент/4 и т.д)

Период – базовый период расчета, к которому относится процентная ставка

Количество периодов (кпер) – число периодов для которого производятся расчеты

Выплата – вносимая (получаемая) сумма. Вносимая сумма вводится со знаком минус.

Нз (Пс) – начальное значение

Бс – будущее значение, то которое нужно достичь после расчетного периода.

1 БC (ставка; количество периодов; выплата; [начальное значение];[тип]) – определяет будущее значение (будущую стоимость) вклада, как функцию <начального значения> вклада и срока хранения.

Примеры:
а)Вычислить значение суммы на расчетном счете по истечении 6 лет, если в банке размещено 10000 руб. под 10% годовых и начисление процентов производится один раз в год
БЗ(10%;6;;-10000)=17 715,61 р.

Ставку можно вводить и с %, и как 0,1 взнос вкладчика со знаком “-“, так как идет отток денег для него

Б)То же, но начисление процентов производится раз в полгода
БЗ(10%/2;6*2;;-10000)=17 958,56 р.

В) То же, но вкладчику в конце каждого полугодия выплачивают сумму в 800 рублей.
БЗ(10%/2;6*2;800;-10000)=5 224,86 р

2 БЗРАСПИС (начальный вклад; ставки) – определяет будущее значение инвестиции (начального вклада) с переменной процентной «ставкой» в разные периоды.

Пример: Положим, начальный вклад, составляющий 10000 руб., инвестирован на три года под 10%, 20% и 25% годовых соответственно. По окончании всего периода он составит:

БЗРАСПИС(10000;{0,1;0,2;0,25})=16 500. Перечень ставок введен как массив (в фигурных скобках).

БЗРАСПИС(В1;А4:А6) Аргументы функции размещены в соответствующих ячейках.

3 ПЗ (ставка; количество периодов; [периодические выплаты ];[разовая выплата];[тип]) – определяет настоящее (текущее) значение вклада в зависимости от ожидаемого дохода в будущем. Эта функция обратна функции БЗ().

Примеры: Пусть вы хотите накопить 20000$ за пять лет, положив некоторую сумму в банк при условии начисления 14% ежегодно. Найти этот начальный вклад.

ПЗ(14%;5;;20000)=-10 387,373$ Проверим данный результат при помощи БЗ:

БЗ(14%;5;;-10387,373)=20000

4 ПЛТ(ППЛАТ) (ставка; количество периодов; сумма кредита; [остаток]; [тип]) – определяет величину периодических выплат для погашения кредита (полного или до заданного остатка) при фиксированной годовой процентной ставке.

Примеры: Определить ежемесячные, ежеквартальные и ежегодные выплаты по взятому вами кредиту в размере 100000 руб., вносимые в течении 3 лет, при годовой ставке 6%.

ПЛТ(6%/12;3*12;100000) = -3 042,19 р/месяц

ПЛТ(6%/4;3*4;100000) = -9 168,00 р/квартал

ПЛТ(6%/4;3*4;100000) =-37 410,98 р/год