Составление формул и работа с ячейками

12/13/10 ИЗУЧАЕМ EXCEL 7.0 14

_________________________________________________________________________________

Лабораторная работа № 2

Тема: Составление формул и работа с ячейками.

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

Microsoft EXCEL предлагает богатые возможности для построения сложных формул.

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

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

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

Упражнение № 1. Создание простых формул

1.В ячейку А1 ввести = 10*5+20*(20-2*5)

2. Нажмите ENTER.

3. Появится число 250.

4. Набираемая формула отражается в строке формул. (рис. 1)

рис. 1

При выполнении арифметических и логических операций сохраняется приоритет действия.

Задание В ячейке B5 Подсчитайте сумму отчислений в соцстрах, если :

Общий фонд заработной платы составил 49521,0 руб.

Начислено премий 12380,3 руб.

Выделено помощи 1233,0 руб.

Отчисления в соцстрах составляют 5,4 % от общей суммы.

Упражнение № 2 Использование ссылок:

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

Например, в ячейку B1 надо записать сумму значений ячеек A5+A6.

Сделаем активной ячейку B1

Запишем =

Выделим A5

В ячейке B1 будет = A5. Наберем +.

Выделим ячейку A6 и нажмем ввод.

Упражнение № 3 Ссылки на ячейки из равных листов.

На листе 1 в ячейке А1 записано 150.

На листе 2 в ячейке а1 записано 200.

В ячейку А2 надо записать сумму этих двух ячеек.

Активизируйте лист 1.

Активизируйте ячейку А2 и запишите в ней =.

Выделите ячейку А1 и наберите +.

Активизируйте в лист 2.

Выделите ячейку а1 и нажмите ввод.

EXCEL вернет Вас в ячейку а2 листа 1.

В строке формул будет:

= A1+Лист2!A1.

Упражнение № 4. Ссылки на ячейки из равных листов (другой вариант).

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

Для этого надо выполнить команду ОКНО®НОВОЕ, а затем ОКНО®Расположить.

Задание. На листе1 записана информация, изображенная на рис 2.

На лист 2 столбец «Сальдо на начало месяца» перенести значение столбца

«Сальдо на конец месяца» из Листа 1 (рис 2)

лист 1 лист 2

рис 2

Занесите информацию в столбцы А, В, С

В ячейку D3 запишите формулу: =А3+В3-С3

Нажмите ENTER

Установите курсор на маркер автозаполнения и протяните его вниз по столбцу (скопируйте формулу)

Активизируйте ячейку D3 на листе 2

Наберите =

Выделите ячейку D3 листе 1

Нажмите ENTER

Скопируйте формулу

Упражнение № 5. Ссылка на ячейки из других книг.

Открыта книга.

На листе 1 в ячейке а1 набрано 150.

Откройте другую книгу, нажав кнопку на панели инструментов «Создать книгу». ( 1-я кнопка слева).

Занесите в ячейку А1 число 400.

Выполните команду Окно®Расположить®Выберите опцию

«Рядом» («сверху вниз», «слева направо», «каскадом»).

В ячейке а2 надо получить результат от сложения: кн.2яч.а1 + кн.1яч.а1 + кн.1яч.в1.

В ячейке В2 кн.2 набираем =

Выделяем ячейку А1 и набираем +.

Переходим в книгу 1, выделяем А1 и набираем +.

Выделяем В2 и набираем 999

В строке формул будет записано = А1[Книга1]Лист1!

$A$1+[Книга]Лист1$B$1

Упражнение № 6. Работа с текстом.

Текстовые значения можно объединять.

В ячейках

а4 записано Морозова

а5 записано Александра

а6 записано Андреевна

Записать в ячейку а8: Морозова Александра Андреевна.

значения символьных констант или переменных объединяются знаком & - амперсант.

В ячейкеА8 наберите =а4&а5&а6

нажмите ENTER

В ячейке А8 появится =МорозоваАлександраАндреевна. ( без пробелов.)

Наберите в ячейке А10 = А4&’ ‘&А5&’ ’&А6

Нажмите ENTER

В ячейке A10 появится =Морозова Александра Андреевна. ( c пробелами.)

Использование имен в формулах.

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

Сальдо на конец месяца= Сальдо на начало месяца + поступление - выбытие

Поэтому в при создании формул удобнее использовать не ссылку на адреса ячеек, а на имена ячеек или диапазонов ячеек. При создании имен должны использоваться следующие правила:

Имя должно начинаться с буквы / или ___

В имени могут использоваться только буквы, цифры /___

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

В качестве имен могут использоваться 1 буква, только не R и C.

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

Упражнение № 7. Создать имя.

В ячейке А5 записано 12550,85 ( Это цена за 1 единицу основного средства)

В ячейке А6 записано 10 (Это количество)

В ячейку А7 надо записать А5*А6(Количество*Цена)

Выделить ячейку А5

Зайти в текстовое поле имени.

Записать Цена

Нажать ввод.

Выделить ячейку А6

Зайти в текстовое поле имени

Записать Количество

Нажать ввод.

Выделить ячейку А7

Зайти в текстовое поле имени.

Записать Сумма

Нажать ввод.

Проверить работу формулы

Если несколько имен, то прокруткой открываем Список имен, выбираем нужное, курсор устанавливается на ячейку, которой присвоено данное имя.

Упражнение № 8. Определение имени с использованием команды ИМЯ .

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

№ 1. Например, ячейка А4 содержит имя «изделие 1».

A	B	C	D	E
1
2
3
4 изделие

и надо использовать этот текст в качестве имени для ячеек B4:E4.

Выделите диапазон ячеек B4:E4

Выполнить команду:

Вставка®Имя®Присвоить®окно Присвоить имя.

В поле имени появится изделие 1.

В поле формулы появится ссылка

Лист 1!$B$4:$E$4

Нажмите ввод.

При следующем открытии окна «Присвоить имя’. Это имя уже будет в списке имен.

№ 2. Можно задавать имя, не выделяя ячейки.

Например: ячейке B5 надо присвоить имя «Тест».

Вставка®Имя®Присвоить®окно Присвоить имя.

В поле Имя введите Тест.

В поле Формула введите =D20.

Нажать кнопку Добавить.

После этого в поле Формула появится определение имени =Лист1D20.

Можно присваивать несколько имен разным ячейкам, не выходя из окна «Присвоить имя». Каждый раз надо нажимать кнопку «Добавить». OK - закрывает окно.

Определение имен на уровне листа.

Вставить®Имя®Присвоить®окно Присвоить имя.

В окне имя запишем Лист2!Листовое имя.

OK.

Примечание. Листовое имя видно в окне диалога только для данного листа.

Например, если открыть Лист2 и окно «Присвоить имя», то в поле имени

Лист1!Листовое имя видно не будет.

Переопределение имен.

Вставка®Имя®Присвоить.

В поле Имя открывшегося окна выделить имя.

В поле Формула ввести новые ссылки, либо выделить новые ячейки при открытом окне «Присвоить имя».

OK.

Создание имен из текстовых значений.

Можно воспользоваться командой ВставкаХИмяХСоздать.

EXCEL использует текстовые значения в строке (столбце), для создания имени.

A B C D E

Объем продаж АО «Свет»
	кв 1	кв 2	кв 3	кв 4
Изделие 1
Изделие 2
Изделие 3

Выделяем диапазон.

A2:E4

ВСТАВКАХИмяХСоздать

Появится окно диалога «Создать имена».

Установить флажок «в столбце слева».

OK.

Диапазону присвоено имя Изделие 1.

Присвоим имена столбцам.

Выделим диапазон B2:E4.

ВставкаХИмяХСоздать

Установить флажок в строке выше.

OK.

Использование имен в формулах.

Например: надо просуммировать значения по строке 2:

1.В ячейке E2 набираем: =сумм(

2.ВставкаХИмяХВставить.

3.В окне «Вставка имени» выбрать Изделие 1.

4.OK.

1. Автосуммирование выполняет:

суммы по строкам;

суммы по столбцам;

суммы по строкам и столбцам.

Упражнение № 9

Составьте таблицу

	А	B	C	D
1	300	224	176
2	400	350	143
3	175	189	326
4	385	456	357
5	625	731	657
6	430	286	787
7

Выделите строку 1 (щелкните вначале строки мышкой).

Нажмите кнопку автосуммирование.

В ячейке D1 будет сумма по строке.

Выделите ячейку D1 и протащите мышью до D6 (суммирование по строкам размножено).

Выделите столбец А и нажмите кнопку автосуммирование.

В ячейке А7 будет суммирование по столбцу.

Содержимое ячейки перетащите по строке D7.

Упражнение № 10

Удалите содержимое строки 7 и столбца D (выделите сначала строку 7 и нажмите клавишу Del, затем выделите столбец D и нажмите Del).

Подсчитайте ИТОГИ “на уголок” в ячейку D7.

Использование массивов в формулах.

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

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

Задача 1. Задана таблица чисел

	А	B	C	D	E
1	10	5	20	30	20
2	10	15	30	20	10
3	20	10	10	50	40
4	40	30	60	100	70

Требуется подсчитать сумму в строках 1,2,3 суммы для каждого столбца, введч одну единственную формулу.

Выделить диапазон А4: Е4

Ввести формулу =A1:E1+A2:E2+A3:E3

Нажать сочетание клавиш CTRL+SHIFT+ENTER

EXCEL в строке формул выведет {=A1:E1+A2:E2+A3:E3}

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

Задача 2. Задана таблица чисел. Найти произведение столбцов (А+В)*С. Результат запишите в столбец D.

	A	B	C	D
1	10	10	2	40
2	20	10	3	90
3	15	5	4	80
4	10	10	2	40

Выделить диапазон D1:D4

Записать формулу =(А1:А4+В1:В4)*С1:С4

Нажать сочетание клавиш CTRL+SHIFT+ENTER

В пенрых двух задачах мы имели множественный результат. Формула массива позволяет подсчитать ОДИН результат на множестве значений.

Задача 3. По данным таблицы предыдущей задачи найти сумму произведений столбцов

А*В+А*С+В*С

Выделить любую ячейку. (Например, Е5)

Записать формулу =А1:А4*В1:В4+А1:А4*С1:С4+В1:В4*С1:С4

Нажать сочетание клавиш CTRL+SHIFT+ENTER

Задача 4. Использование констант в массиве.

В месяце 22 рабочих дня. Подсчитать фактический заработок работников по формуле

Начислено.=Ставка/К-во рабочих дней в месяце*Отработано дней

Исходные данные представлены в таблице.

	А	В	С	D
1	Фамилия И.О.	Ставка	Отраб. дней	Начислено
2	Заводов А.И.	380	22	380.00
3	Кимов И.Г.	450	19	388.64
4	Зеленский И.И.	800	21	763.64
5	Рогов П.А.	350	22	350.00

Выделить диапазон D2:D5

Записать =В2:В5/22*С2:С5

Нажать CTRL+SHIFT+ENTER

Построится формула {=В2:В5/22*С2:С5}

Некоторые правила для формулы массива.

1.Чтобы ввести формулу массива, сначала выделите ячейку или диапазон, который будет

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

Для фиксации формулы массива надо нажать CTRL+SHIFT+ENTER

Нельзя самим вводить фигурные скобки. Иначе EXCEL поймет это как текст.

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

После изменения формулы нажать CTRL+SHIFT+ENTER.

Для быстрого выделения массива нажать сочетание клавиш CTRL /

Использование функций.

Функции EXCEL.

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

Microsoft EXCEL имеет более 300 встроенных функций, в том числе и логических, функций дат, финансовых, математических и т.д. Это категории функции. Каждой категории соответствует свой набор функций. Сведения о функциях можно получить из Справочной системы (?)

Синтаксис функций. Функция состоит из двух частей- имени и аргумента.

Запись функции ,также как и формулы, начинается со знака “=”.

ИМЯ функции описывает операцию, которую она выполняет. Например СУММ – суммирование ; СРЗНАЧ – нахождение среднего значения и т. д.

АРГУМЕНТЫ (ОПЕРАНДЫ) задают значения или ячейки, над которыми выполняются действия

Аргументы должны быть заключены в скобки. Аргументы могут быть записаны через “:” или через “;”. Например: =СУММ(А1:В12) означает, что суммируются значения ячеек в дипазоне от А1 до В12. , но =СУММ(А1;В12) означает сумму двух яеек А1 и В12 .

Упражнение № 11 Создайте таблицу и выполните расчеты

A	B	C	D	E	F	G
1	Январь	февраль	март	Всего	Среднее за квартал	% март к февралю
2 Затраты	300	250	430
3 Приход	800	550	730
4 Прибыль	=В3-В2
5 Налог на прибыль	В4*20%

Порядок расчета:

Подсчитайте Прибыль и Налог на прибыль.

Используя функцию СУММ подчситайте значение “Всего”

С помощью функции СРЗНАЧ рассчитайте “Среднее за квартал”

Столбец G