Составление формул и работа с ячейками (стр. 1 из 3)
Лабораторная работа № 2
Тема: Составление формул и работа с ячейками.
Информацию, содержащуюся на рабочих листах, можно обрабатывать с помощью формул, состоящих из арифметических действий, а также с помощью функций, являющихся встроенными формулами. MicrosoftEXCEL предлагает несколько сотен функций, призванных облегчить работу пользователя при составлении формул.
MicrosoftEXCEL предлагает богатые возможности для построения сложных формул.
| Все формулы должны начинаться со знака равенства, иначе запись будет принята как обычный текст. |
Используя несколько математических операторов и правила ввода значений в ячейки, можно превратить рабочий лист в мощный калькулятор.
Создадим несколько простых формул и посмотрим, как они работают.
Упражнение № 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. Ссылки на ячейки из равных листов (другой вариант).
Для удобства создания формулы, если есть ссылки на ячейки на разных листах, можно предварительно расположить листы одной и той же книги рядом в нескольких окнах.
 |
«Сальдо на конец месяца» из Листа 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 (суммирование по строкам размножено).