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



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

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

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

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 в строке формул выведет {= A 1: E 1+ A 2: E 2+ A 3: E 3}

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

Задача 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

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 /

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

Функции EXCEL.

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

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

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

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

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

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

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

 


Дата добавления: 2020-01-07; просмотров: 189; Мы поможем в написании вашей работы!

Поделиться с друзьями:






Мы поможем в написании ваших работ!