|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
|
|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
VI. Абсолютная и относительная адресация
Относительные ссылки До сих пор мы использовали относительную адресацию ячеек. При автозаполнении в каждой следующей записи в формуле изменялись имена ячеек. Такие имена ячеек или, точнее сказать, ссылки на ячейки называются относительными. В этом заключается основное правило при работе с относительными адресами. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Абсолютные ссылки Если возникла необходимость указать в формуле ячейку, которую нельзя менять при автозаполнении, используется знак $. Им фиксируются как столбцы, так и строки. Например: $А$10. Абсолютная ссылка ячейки в формуле всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Смешанные ссылки Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется.
Задание 12 Создайте следующую таблицу. Заполните нужные ячейки формулами, воспользуйтесь относительными, абсолютными или смешанными ссылками при автозаполнении формул.
Задание 13 Создайте следующую таблицу. Заполните нужные ячейки формулами, воспользуйтесь относительными, абсолютными или смешанными ссылками при автозаполнении формул. Для товаров, стоимость которых с учетом их количества превышает 500$, установите скидку в 1%, используя функцию «ЕСЛИ» (информацию о данной функции найдите в справке). Расчет приобретенных компанией канцелярских средств оргтехники Курс $ = 26, 89 руб.
Задание 14 Создать модель «Адаптация рыночной цены». Во многих случаях падение цены на товар при избыточном предложении на рынке и рост цены при избыточном спросе, т.е. установление равновесия рынка (равенство спроса и предложения) происходит не мгновенно, а в течение определенного конечного промежутка времени. Построить электронную таблицу расчета величины динамики установления равновесия Yn+1 (см. рис. ниже) и исследовать изменения данной величины в зависимости от величины параметра C, а также начального значения Yn, для этого: 1.Внести в таблицу начальные значения для параметра С (значение равно 6, 5) и цены (значение равно 2, 8). 2.Заполнить временной столбец n значениями от 0 до 100. 3.Произвести по формуле расчет величины динамики установления равновесия 4.Рассчитать среднюю цену и дисперсию цены, по соответствующим формулам. 5.Построить график изменения цены, используя точечный вид графика. 6.Изменяя начальные значения параметра С, выявить влияние параметра С на процесс установления равновесной рыночной цены.
Самостоятельная работа Вариант 1. БАНК–1
Исходная таблица — Приложение, таблица 1.
1. Используя автофильтр, выбрать проводки по Дт 51. Результат скопировать на Лист2 и переименовать его в «Счет 51». Аналогично для проводок по Кт 51. Отсортировать по полю Кт(Дт).
2. Определить сумму по Дт 51 и по Кт 51. Используя функцию ЕСЛИ сравнить значения и вывести в ячейке «положительное сальдо», если Дт больше или равно Кт и «отрицательное сальдо» в противном случае. 3. Определить сумму по Кт 90.
4. Построить график поступление выручки от магазинов Альфа, Гамма и Сигма по таблице из п.3, используя столбцы «Получатель» и «Сумма».
5. Подведение итогов. Найти сумму по КТ каждого счета за каждый день.
6. Сводные таблицы. Сколько средств перечислено в Налоговую инспекцию всего за квартал и по каким налогам?
7. Сводные таблицы. Определить, сколько средств поступило на 51 счет и сколько израсходовано в целом за квартал и по месяцам. 8. Сводные таблицы. Когда были сделаны перечисления Логике, по каким документам и на какую сумму? Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите сумму, перечисленную указанным получателям. Результат оформите в виде таблицы.
Получатель Сумма
ОФМС
Казначейство
Нал. инспекция
ОПФ по г. Челябинску
10. По таблице постройте круговую диаграмму «Структура платежей».
11. Функция СЧЕТЕСЛИ. Определите, сколько раз перечисляли каждый налог. Результат оформите в виде таблицы.
68-1
68-2
……..
12. Функции ДМАКС и ДМИН. Определите максимальную и минимальную суммы, поступившие на 51 счет и перечисленные со счета. Результат оформите в виде таблицы.
Кт 51
Вариант 2. БАНК–2
Исходная таблица — Приложение, таблица 1.
1. Используя автофильтр, выбрать проводки до 1 марта по Дт 51. Результат скопировать на Лист2 и переименовать его в «Янв-Фев». Аналогично для Кт. Отсортировать по полю Кт(Дт).
2. Определить сумму по Дт 51 и по Кт 51. Используя функцию ЕСЛИ сравнить значения и вывести в ячейке «положительное сальдо», если Дт больше Кт и «отрицательное сальдо» в противном случае.
3. Определить сумму по Дт 20.
4. Построить график расчетов с банком по таблице п.3, используя столбцы «Дата» и «Сумма». 5. Подведение итогов. Найти сумму по Дт каждого счета за квартал.
6. Сводные таблицы. Когда были сделаны перечисления в Пенсионный фонд и на какую сумму?
7. Сводные таблицы. Определить, сколько перечислено налогов (68, 69) всего за квартал, по месяцам и по каждому счету в отдельности (сгруппировать субсчета 68-1, 68-2 и т.д.). 8. Сводные таблицы. Сколько средств получено от реализации товаров по магазинам Альфа, Гамма Сигма всего, и в отдельности по каждому? Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите сумму по кредиту каждого счета.
10. Используя полученную таблицу, постройте круговую диаграмму, отображающую распределение средств по счетам.
11. Функция СЧЕТЕСЛИ. Определите, сколько раз перечисляли деньги каждому получателю. Результат оформить в виде таблицы.
12. Функции ДМАКС и ДМИН. Определите максимальную и минимальную сумму перечислений каждому получателю. Результат оформите в виде таблицы.
Вариант 3. МАГАЗИН–1
Исходная таблица — Приложение, Таблица 4.
1. Используя автофильтр, выбрать товары, у которых истек срок годности на 1 декабря 2002 года.. Результат скопировать на чистый лист и назвать лист «Списание». Отсортировать по наименованию и сроку годности.
2. Сколько кг апельсинов имеется на складе по цене меньше 30 руб.?
3. Построить график изменения цен на апельсины.
4. Вставить в исходную таблицу столбец «Списание». Если до окончания срока годности товара осталось менее 15 дней, то вывести в ячейке «Уценить», если срок годности истек, вывести в ячейке текст «Списать», иначе «Годен». Считать текущей датой 1 декабря 2002 года. 5. Подведение итогов. Составить ежедневный отчет о количестве и стоимости
товаров, полученных от каждой фирмы.
6. Сводные таблицы. Составить отчет о наименовании, количестве и стоимости товаров для каждой накладной.
7. Сводные таблицы. Составить таблицу, отражающую перечень товаров для каждой фирмы. 8. Сводные таблицы. Составить отчет для каждого наименования товара с разбивкой по ценам. Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите стоимость товаров годных к продаже, к списанию и уценке. Результат оформите в виде таблицы.
10. Используя полученную таблицу, постройте круговую диаграмму «Качество товаров на складе».
11. Функция СЧЕТЕСЛИ. Определите, сколько раз получали каждый товар. Результат оформить в виде таблицы.
12. Функции ДМАКС и ДМИН. Определите максимальную и минимальную цену каждого наименования товара. Результат оформите в виде таблицы.
Вариант 4. НАЛОГИ
Исходная таблица — Приложение, таблица 2.
1. Используя автофильтр, выбрать перечисления по налогу на имущество. Результат скопировать на новый лист и назвать его «Имущество». Отсортировать по дате. 2. Определить общую сумму перечислений банку за расчетно-кассовое обслуживание.
3. Построить диаграмму перечислений за расчетно-кассовое обслуживание по таблице п.2, используя столбцы «Дата» и «Сумма».
4. Определить общую сумму НДС. Если сумма больше 500000, то вывести в ячейке сообщение «Оплатить аванс по НДС».
5. Подведение итогов. Составить отчет о перечислениях каждому получателю.
6. Сводные таблицы. Составить отчет о ежемесячных перечислениях налогов.
7. Сводные таблицы Составить отчет о общей сумме перечислений по 68, 69 счетам (сгруппировать по счетам) и с разбивкой по налогам.
8. Сводные таблицы Определить сумму, перечисленную по каждому налогу за квартал.
Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите сумму по каждому налогу. Результат оформите в виде таблицы.
10. Используя полученную таблицу, постройте круговую диаграмму.
11. Функция СЧЕТЕСЛИ. Определите, сколько раз перечисляли деньги каждому получателю. Результат оформите в виде таблицы.
12. Функции ДМАКС и ДМИН. Определите максимальную и минимальную сумму, перечисленную каждому получателю. Результат оформите в виде таблицы.
Вариант 5. СКЛАД–1
Исходная таблица — Приложение, таблица 3.
1. Используя автофильтр, выбрать товары, отпущенные до 8 января 2002. Результат скопировать на новый лист и назвать его «Рождество». Отсортировать по дате и наименованию. 2. Определить количество мармелада, полученной одной из фирм.
3. Построить диаграмму изменения спроса на мармелад.
4. Подведение итогов. Составить ежедневный отчет по каждой фирме о стоимости полученного товара.
5. Сводные таблицы. Составить отчет для каждой фирмы о количестве и стоимости товара, полученного со склада, всего и по каждому наименованию. 6. Используя значения, полученные в предыдущем задании, определить скидку в оплате для каждой фирмы. Если стоимость товара больше 7000, скидка 15%, если от 3000 до 7000, то 10%, иначе скидки нет.
7. Сводные таблицы. Когда, сколько и по какой цене получала печенье фирма Весна.
8. Сводные таблицы. Составить отчет для каждой фирмы о количестве и стоимости товаров по каждой накладной. Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите стоимость товаров, отпущенных каждой фирме. Результат оформите в виде таблицы.
10. Используя полученную таблицу постройте круговую диаграмму «Структура поставок».
11. Функция СЧЕТЕСЛИ. Определите, сколько раз отпускали каждый товар. Результат оформите в виде таблицы.
12. Функции ДМАКС и ДМИН. Определите максимальную и минимальную цену каждого наименования товара. Результат оформите в виде таблицы.
Вариант 6. МАГАЗИН–2
Исходная таблица — Приложение, таблица 4.
1. Используя автофильтр, выбрать товары, поступившие после 1 октября 2002 года. Результат скопировать на чистый лист и назвать лист «Последние поступления». 2. Сколько кг яблок поступило по цене меньше 25 руб.? Отсортировать полученный список по полю «Цена».
3. Построить график изменения цен на яблоки.
4. Вставить в исходную таблицу столбец «Списание». Если на 1 ноября 2002 года до окончания срока годности товара осталось 30 и меньше дней, вывести в столбце текст «Уценить», если срок годности уже истек, то вывести «Списать», иначе «Годен». 5. Подведение итогов. Составить о количестве и стоимости товаров, подлежащих уценке, списанию и годных к употреблению всего и для каждого наименования.
6. Сводные таблицы. Составить отчет для каждой фирмы о количестве и стоимости товаров, подлежащих списанию.
7. Сводные таблицы. Составить таблицу, отражающую перечень и количество товаров от каждой из фирм, годных к продаже.
8. Сводные таблицы. Составить отчет для каждого наименования товара, подлежащего списанию, с разбивкой по ценам.
Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите количество товаров годных к продаже, к списанию и уценке. Результат оформите в виде таблицы.
10. Используя полученную таблицу постройте круговую диаграмму «Качество товаров».
11. Функция СЧЕТЕСЛИ. Определите, сколько раз получали каждый товар. Результат оформить в виде таблицы.
12. Функции ДМАКС и ДМИН. Определите максимальную и минимальную цену каждого наименования товара. Результат оформите в виде таблицы, аналогичной п.11.
Вариант 7. УСЛУГИ СВЯЗИ
Исходная таблица — Приложение, таблица 6.
Ставка по оплате без льгот и таблица льгот записаны в свободной области вне таблицы. Столбец «Льгота» заполняется следующим образом: 0 – нет льгот, 1 – скидка в оплате 25%, 2 – скидка в оплате – 50%.
1. Столбец «Оплата» заполнить с учетом льгот, используя функцию ЕСЛИ.
2. Используя автофильтр, составить списки абонентов по районам.
3. Используя автофильтр, выбрать абонентов, имеющих льготу 50%
4. Отсортировать список по полям «Район» и «ФИО» по возрастанию.
5. Построить гистограмму долга по районам.
6. Подведение итогов. А) Определить сумму оплаты и долга по каждому району. Б) Определить для каждого района сумму внесенной оплаты и долга по каждой льготной категории. 7. Сводные таблицы. Составить таблицу, отражающую количество льготников по каждой категории.
8. Сводные таблицы. Составить таблицу, отражающую сумму долга по каждой категории льготников для каждого района за каждый месяц.
Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите сумму долга по каждому району. Результат оформите в виде таблицы.
10. По полученной таблице постройте круговую диаграмму «Структура долга».
11. Функция СЧЕТЕСЛИ. Определите количество платежей в каждом месяце. Результат оформите в виде таблицы.
12. Функции ДМАКС и ДМИН. Определите максимальный размер долга по каждому району. Результат оформите в виде таблицы.
Вариант 8. ОТДЕЛ КАДРОВ
Исходная таблица — Приложение, таблица 5.
1. Определить сумму окладов всех сотрудников.
2. Используя автофильтр, составить списки сотрудников по должностям.
3. Используя функцию ЕСЛИ, вычислить надбавку к окладу. Если отработано менее 5 лет то 15%, от 5 до 10 лет — 25%, более 10 лет — 40% от оклада.
4. Используя автофильтр, выбрать всех служащих одной должности в возрасте старше 30 лет. Отсортировать по полю «ФИО».
5. Отсортировать исходный список по должностям.
6. Построить круговую диаграмму, отражающую распределение окладов по должностям. (построить вспомогательную таблицу по п.2)
7. Подведение итогов. А) Определить суммы надбавок по должностям. Б) определить суммы окладов по должностям для каждого подразделения. (Вложенные итоги).
8. Сводные таблицы. Определить для каждого подразделения суммы каждого вида надбавок (15%, 25%, 40%)
9. Сводные таблицы. Определить для каждой должности количество человек каждого года рождения. (группировка по дате).
Вычисления по формулам:
10. Функция СУММЕСЛИ. Определите сумму каждого вида надбавки. Результат оформите в виде таблицы.
11. По новой таблице постройте круговую диаграмму «Структура надбавок».
12. Функция СЧЕТЕСЛИ. Определите, сколько человек работает в каждой должности. Результат оформить в виде таблицы.
13. Функции ДМАКС и ДМИН. Добавьте в таблицу столбец «Стаж». Определите максимальный и минимальный стаж сотрудников каждой должности. Результат оформите в виде таблицы, аналогичной заданию 12.
Вариант 9. ЦЕХ–1
Исходная таблица — Приложение, таблица 8.
1. Используя автофильтр, составить списки рабочих по цехам.
2. Используя автофильтр, составить список рабочих, которые не имели больничных. Отсортировать его по полю “Месяц”
3. Построить диаграмму, отражающую количество отработанных смен по цехам.
4. Вставить в таблицу столбцы “Дата поступления” и “Надбавка” Дату поступления заполнить такими значениями, чтобы были сотрудники, отработавшие меньше 5 лет, больше 5 и меньше 15, больше 15 лет. Столбец “Надбавка” заполнить следующим образом: если стаж меньше 5 лет, то надбавка 0%, если больше 5 и меньше 10, то надбавка 15%, если больше 15 лет— 25%. 5. Подведение итогов. Определить количество больничных дней по каждому подразделению
6. Подведение итогов. Определить общее количество отработанных смен для каждого работника 7. Сводные таблицы. Составить отчет о среднем количестве больничных дней по каждому цеху.
8. Сводные таблицы. Составить ежемесячный отчет о количестве больничных дней по каждому подразделению
Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите количество больничных смен за каждый месяц. Результат оформите в виде таблицы.
10. По таблице п.9 постройте круговую диаграмму «Уровень заболеваемости».
11. Функция СЧЕТЕСЛИ. Определите, сколько человек не имели больничных. Результат оформить в виде таблицы.
12. Функции ДМАКС и ДМИН. Определите максимальное и минимальное количество отработанных смен по каждому цеху.
Вариант 10. ЦЕХ–2
Исходная таблица — Приложение, таблица 8.
1. Используя автофильтр, составить списки рабочих по месяцам, отсортированные по цехам.
2. Используя автофильтр, составить список рабочих, которые отработали больше 10 смен. Отсортировать его по полю “Месяц”
3. Построить диаграмму, отражающую количество отработанных смен по месяцам.
4. Вставить в таблицу столбцы “Дата рождения” и “Категория” Столбец “Категория” заполнить следующим образом: если возраст меньше 25 лет, то категория сотрудника — “молодой специалист”, если больше 25 и меньше 52, “специалист”, если больше 52 лет — “предпенсионный”. 5. Подведение итогов. определить количество отработанных смен по каждой категории рабочих. 6. Сводные таблицы. Составить ежемесячный отчет об общем количестве оплаченных смен.
7. Сводные таблицы. Составить ежемесячный отчет о количестве больничных дней по каждой категории для каждого подразделения. 8. Составить отчет для каждого рабочего о количестве отработанных и больничных дней.
Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите количество больничных смен по каждой категории сотрудников. Результат оформите в виде таблицы.
10. Используя полученную таблицу, постройте круговую диаграмму «Уровень заболеваемости».
11. Функция СЧЕТЕСЛИ. Определите, сколько человек не имели больничных. Результат оформить в виде таблицы.
12. Функции ДМАКС и ДМИН. Определите максимальное и минимальное количество отработанных смен по каждому цеху.
Цех2
Цех3
Цех4
Вариант 11. ПОЧТА–1
Исходная таблица — Приложение, таблица 7.
Цена единицы веса каждого вида корреспонденции задана во вспомогательной таблице.
Заполните столбец « Стоимость», используя функцию ЕСЛИ для выбора нужной цены из таблицы.
1. Используя автофильтр, составить списки корреспонденции по направлениям, отсортированные по весу.
2. Используя автофильтр, составить список корреспонденции за два последних дня. Отсортировать его по дате, направлению и виду корреспонденции. 3. Построить диаграмму, отражающую количество отправлений по каждому направлению (составить вспомогательную таблицу, используя функцию СЧЕТЕСЛИ).
4. Подведение итогов. Определить ежедневную стоимость отправленной корреспонденции
5. Подведение итогов. Определить стоимость корреспонденции по направлениям и виду корреспонденции (вложенные итоги). 6. Сводные таблицы. а)Составить ежедневный отчет о весе посылок для каждого направления. б) Выполнить группировку по дате с шагом два дня.
7. Составить отчет о весе и стоимости бандеролей для каждого направления (использовать поле страниц).
Вычисления по формулам:
8. Функция СУММЕСЛИ. Определите стоимость каждого вида корреспонденции. Результат оформите в виде таблицы.
9. Используя полученную таблицу, постройте круговую диаграмму.
10. Функция СЧЕТЕСЛИ. Определите, сколько раз отправляли каждый вид корреспонденции. Результат оформите в виде таблицы.
11. Функции ДМАКС и ДМИН. Определите максимальную и минимальную стоимость каждого вида корреспонденции. Результат оформите в виде таблицы.
Вариант 12. ПОЧТА–2
Исходная таблица — Приложение, таблица 7.
Вставьте в таблицу «Почта» столбец « Вид доставки». Заполните его значениями «Авто», «Авиа», «Ж.д.». Цена единицы веса каждого вида корреспонденции для каждого вида доставки задана во вспомогательной таблице.
1. Заполните столбец «Стоимость», используя функцию ВПР и ЕСЛИ для выбора нужной цены из таблицы. 2. Используя автофильтр, составить списки корреспонденции по видам доставки, отсортированные по направлениям. 3. Используя автофильтр, составить списки корреспонденции по направлениям, отсортированные по виду доставки и виду корреспонденции. 4. Построить круговую диаграмму, отражающую соотношение видов
корреспонденции по вспомогательной таблице (функция СЧЕТЕСЛИ).
5. Подведение итогов. Определить вес и стоимость отправленной корреспонденции по каждому виду доставки.
6. Подведение итогов. Определить стоимость отправленных бандеролей и посылок за каждый день (вложенные итоги) 7. Сводные таблицы. Определить вес отправленной корреспонденции для каждого направления.
8. Сводные таблицы. Составить отчет о стоимости каждого вида корреспонденции для каждого направления по каждому виду доставки
Вычисления по формулам:
9. Функция СУММЕСЛИ. Определите стоимость корреспонденции по каждому виду доставки. Результат оформите в виде таблицы.
10. По новой таблице постройте круговую диаграмму «Структура доставки».
11. Функция СЧЕТЕСЛИ. Определите, сколько раз отправляли корреспонденцию по каждому направлению. Результат оформить в виде таблицы. Направление Количество отправлений
Москва
………………..
Иркутск
12. Функции ДМАКС и ДМИН. Определите максимальную и минимальную стоимость каждого вида корреспонденции. Результат оформите в виде таблицы.
Вариант 13. ПОЛИКЛИНИКА
Исходная таблица — Приложение, Таблица 7.
1. Автофильтр. Составить:
a) списки больных с разным диагнозом;
b) список больных одного врача, имеющих скидку на лекарства более 15%; c) список больных, фамилии которых начинаются с одной буквы алфавита.
2. Диаграммы.
a) Построить кривую, отражающую количество посещений поликлиники за год по каждому району.
b) Построить диаграмму, отражающую количество посещений и скидку на лекарства каждому больному.
3. Расширенный фильтр.
Составить таблицу больных с количеством посещений больше среднего в виде:
Район ФИО Количество посещений за год
4. Команда Данные-Итоги. Составить:
a) отчет о количестве больных и количестве их посещений по каждому району
b) отчет о количестве больных по каждому району с разбивкой по заболеванию
5. Сводные таблицы.
Создать таблицу, отражающую минимальный Популярное:
|
Последнее изменение этой страницы: 2016-08-31; Просмотров: 4315; Нарушение авторского права страницы
Дт 51
