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

Лабораторная работа № 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.

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

2) Запишем =

3) Выделим A5

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

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

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

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

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

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

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

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

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

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

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

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

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

=A1+Лист2!A1.

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

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

(=А3+В3-С3)

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

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

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

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

лист 1 лист 2

рис 2

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

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

НажмитеENTER

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

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

Наберите =

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

НажмитеENTER

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В ячейках

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

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

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

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

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

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

2. нажмите ENTER

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

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

5. Нажмите ENTER

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4. Нажать ввод.

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

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

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

8. Нажать ввод.

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

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

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

12. Нажать ввод.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. OK.

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

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

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

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

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

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

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

4. OK.

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

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

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

A B C D E

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

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

A2:E4

2. ВСТАВКАÕИмяÕСоздать

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

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

5. OK.

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

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

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

1. ВставкаÕИмяÕСоздать

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

3. OK.

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

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

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

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

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

4.OK.

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

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

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

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

Упражнение № 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. Выделите строку 1 (щелкнитевначале строки мышкой).

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

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

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

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

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

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

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

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

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

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

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

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

Задача 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 суммы для каждого столбца, введч одну единственную формулу.

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

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

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

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

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

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

10 10 2

20 10 3

15 5 4

10 10 2

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

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

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

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

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

А*В+А*С+В*С

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

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

3. Нажать сочетание клавиш 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

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

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

4. Нажать CTRL+SHIFT+ENTER

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

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

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

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

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

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

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

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

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

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

Функции EXCEL.

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

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

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

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

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

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

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

Упражнение № 11 Создайте таблицу и выполнитерасчетыA B C D E F G

Январь февраль март Всего Среднее за квартал % март к февралю 2 Затраты 300 250 430

3 Приход 800 550 730

4 Прибыль =В3-В2

5 Налог наприбыль

В4*20%

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

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

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

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

4. Столбец G подсчитываетсяпо формуле: D/C*100%

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

1. В ячейке F1 запишите«Среднее за квартал».

2. Выделите ячейку F2,затем на Панели инструментов щелкните по кнопке Мастер функций (FunctionWizard).

3. Откроется диалоговое окно Мастерафункций.

4. В списке Категория (FunctionCategory) статистические (Statistical).

5. В окне функция (Function) появится список статистических функций.

6. В этом окне надо выделить строкуСРЗНАЧ (AVERAGE) и щелкнуть по кнопке ДАЛЕЕ (NEXT).

7. Откроется окно Мастер функций -шаг2 из 5 (FunctionWizard-step2 of5).

8. Поместить указатель мыши назаголовок диалогового окна, нажать левую кнопку мыши и перетащить окно книжней границе экрана.

9. Щелкните в поле Число 1 (Number1).

10. Выделитедиапазон ячеек от А2 до D2.

Впроцессе выделения диапазона в поле диалогового окна «Значение» ((VALUE) указывается средняявеличина содержимого выделенных ячеек. В поле Число1 (Number 1) появилисьадреса A2:D2.

11. Щелкнитепо кнопке Готово (Finish). В ячейке Е2 появится среднее значение.

12. Выделитеячейку Е2, установите в ней указатель мыши в правый нижний угол и протяните принажатой левой кнопке мыши в следующую ячейку Е3.

13. Отпуститеклавишу мыши. В ячейке Е3 будет средний приход.

14. Остальныевычисления проведите, используя полученные знания в предыдущих упражнениях

Принцип работы с функцией AVERAGE распространяетсяна все остальные функции.

Математическиефункции.

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

Функция =СУММ (число).Аргумент числа может содержать до 30 элементов, каждый из которых может бытьили const или ссылкой на ячейку. Функция =СУММ ( )соответствует автосуммированию (å). Привыполнении СУММ ( ) можно назначить имя диапазону ячеек. Ячейки могут быть ине смежными (чтобы выделить несмежные ячейки, надо, удерживая CTRLщелкнуть мышкой по ячейкам). Чтобы назначить имя диапазону ячеек, надо:

1. Выделить диапазон ячеек

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

3. Назначить имя диапазону.

4. В дальнейшем в формуле можноиспользовать это имя.

Задача.Требуется определить итогилицензирования а/м.

Вид собственности

Легковые а/м

Автобусы

Грузовые а/м

опасные грузы

Специальные

Федеральные

Муниципальные

Частная

С иностр. кан.

Итого

1. Определим имена

2. В В6: =СУММ (легковые).

Функция =СУММ ( ) достачно гибкая, но при добавленииячейки в конец (начало) диапазона могут возникнуть трудности.

Например:

1 100

2 200

3 100

4 100

5 200

6 100 — ввести

7 =СУММ (А1: А5)®700

Вставим строку 6 и внесем в нее число. Например, 100.Результат не изменится. Надо в ячейку А7 ввести =СУММ (А1: А6).

ABS — возвращает абсолютное значение чисел. Имеет синтаксис =ABS (число).

Например:

В А1 записано — 200, в ячейку А2 введем =ABS (A1).Возвращает значение 200.

Функция ЗНАК возвращает знак числа.

= ЗНАК (число).

Если число больше 0, функция ЗНАК возвращает 1.

Если число меньше, то -1.

Если число =0, то Æ.

Например:

Остаток на нач.

Приход

Расход

Остаток

АО «РАССВЕТ»

100

200

350

В ячейке Е1 записано =ЗНАК (СУММ(А1:D1)).Возвращает -1.

Функция ОКРУГЛ.

=ОКРУГЛ (число; число_цифра)

Аргумент число — это число, ссылка на ячейку.Аргумент число цифр может быть положительным или отрицательным.Указывает на то количество цифр, до которого округляется.

Формула. Результат.

=ОКРУГЛ(897,457; — 2) 900

=ОКРУГЛ(897,457; — 1) 900

=ОКРУГЛ(897,457; 0) 897

=ОКРУГЛ(897,457; 1) 897,5

Функция ЧЕТН округляет число до ближайшегонечетного числа.

ЧЕТН (число); нечетн. (число)

ЧЕТН (5) 6

НЕЧЕТН (7,2) 9

Функция целое.

=целое (число)

=целое (185,37) ® 185.

=целое (200,99) ® 200.

Функция корень.

=Корень (число). Возвращает значение квадратногокорня.

Функция остаток.

=ОСТАТОК (число; делитель)

n возвращает остаток от деления.

=остаток (33;4) ® 1.

Функция степень.

=степень (число; степень).

log 10(число) — десятичный логоритм.

log (число;основание).

log (12;2)

log2 12.

LN(число) — натуральный логоритм.

Текстовые функции.

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

ЗНАЧЕН (текст).

Аргумент ТЕКСТ может быть строкой, заключенной вдвойные кавычки или ячейкой в которой содержится текст. Преобразуемые текстовыезначения могут быть записаны в любом допустимом формате. Например:

=ЗНАЧЕН (А1)

А1 содержит «0025»

Ответ: 25.

Функция РУБМ.

=ДЛСТР (текст).

Аргумент текст может быть как текстовым, так ичисловым. Сама функция возврвщает длину строки или числа.

Задача. Определитьдлину текста в ячейке А1:.

«Функции EXCEL».

=ДЛСТР (А1)

Или в ячейке А1 записано число 156.

=ДЛСТР (А1)

Ячейка, на на которую ссылается функция ДЛСТР можетсодержать другие текстовые функции. В ячейку А1 записано выражение:

=ПОВТОР ("*В"; 25)

=ДЛСТР (А1)

50.

=СЖПРОБЕЛЫ (текст) — удаляет начальные и конечныепробелы из строки и между словами оставляет по одному пробелу. В А1 записано#### Тарасов ##### Алексей ###### Петрович. Записать =СЖПРОБЕЛЫ (А1).

=СОВПАД (текст 1; текст 2) ВА1: Адрес:, 355000, 26,СТАВРОПОЛЬ Г, ВАСИЛЬЕВА УЛ, 45 В С3 записано.

Адрес:, 355000, 26, СТАВРОПОЛЬ, УЛ ВАСИЛЬЕВА, 45.

СОВПАД (А1; С3). Если не совпадет, то ложь.

=ПРОПИСН (текст)

В А1 записано: с: статистические функции.

В А2 записать:

СТАТИСТИЧЕСК ФУНК

=ПРОПИСН (А1)

=СТРОЧНАЯ

=СТРОЧН (А1) — статистичекие функции.

ПРОПНАЧ (текст)

Статистичекие функции

=ПРОПНАЧ (А1).

Работа с элементами строк.

А1 А2 А3

Иванов Петр Иванович

Надо записать в А2

в А4: Иванов П. И.

ЛЕВСИМВ (текст; число знаков)

=ПРАВСИМВ (текст; количество символов)

=ПС (текст; начальная позиция; количество символов)

В А1 запишем 20-11-35, 20 часов 11 минут 35 секунд

Выделить минуты (А1, 4, 2). Ответ: 11

=Сцепить – эквивалент &

Выполнить двумя способами: Иванов Петр Иванович.

Логические функции.

=Если (Условие, выраж_если_истина; выражение_если_ложь)

Задача.Имеем свободную таблицу успеваемости, в которой надо обозначить пометку оназначении стипендии.

ФИО

экз. 1

экз. 2

экз. 3

отметка о стипенд.

Иванов

Косов

Симонов

В ячейку Е2 записать Если (и (В2 >3; С2 > 3; D2> 3); «да»; «нет»).

Вложенные функции.

Если в ячейке А1 записана сумма вычетов из заработнойплаты для определения налогооблагаемой суммы. Этот вычет может быть равенодному минимальному зароботку, двум, трем пяти. Каждому из них присваваетсякод.: 21, 22, 23, 25.

=Если (А1=1; «21»; если (А1=2;«22»; если А1=3; «23»; если А1=5; «25»)))).

Допускается до 7 вложений.

Функция ДАТ

1. Создание упорядоченного ряда.Правка®Содать®Пргрессия®окно прогрессия®Установить флажки:

n по строкам столбцами секция тип дата.

n В секции единицы выбрать день месяц, год.

Ввести с клавиатуры дату: например 25.04.99 ископировать.

2. Ввод дат с использованиемавтозаполнения

n вводим начальную дату и за ней следующую

3. Форматирование дат и времени:

Формат®Ячейка®вкладка число®Дата®Тип.

4. Арифмитические операции с датами: