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

5/27/19 ИЗУЧАЕМ EXCEL 7.0 22

_________________________________________________________________________________

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

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

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

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

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

4. В строке формул будет записано = А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

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

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

№ 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

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

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

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

3. 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. Использование массивов в формулах.

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

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