Практическая работа №5
Тема: Использование логических функций и выполнение экономических расчетов в MS Excel.
Цель: — получить практические навыки работы в программе MS Excel, вводить и редактировать стандартные функции электронной таблицы.
— изучение технологии проведения экономических расчетов, расчет точки окупаемости инвестиций, накопления и инвестирования средств.
Задание 1. Работа с функциями Год и Сегодня
Ячейки, в которых выполнена заливка серым цветом, должны содержать формулы!
1. Создать и отформатировать таблицу по образцу (Фамилии ввести из списка с помощью автозаполнения)
2. Вычислить стаж работы сотрудников фирмы по формуле:
(Полученный результат может не совпадать со значениями в задании. Почему?)
3. Переименовать Лист1 в Сведения о стаже сотрудников
1. Скопировать таблицу из задания № 1 на Лист2 и переименовать его в Тарифные ставки
3. Добавить столбец Тарифные ставки и вычислить их таким образом:
1- если стаж меньше 5 лет, 2- если стаж больше или равен 5 лет
Задание 3. Работа с вложенными функциями ЕСЛИ
1. Скопировать таблицу из задания № 2 на Лист3 и переименовать его в Налоги.
3. Добавить столбцы Ставка, Начислено, Налог, Заработная плата и заполнить их таким образом:
Налог = 0 , если Начислено меньше 1000, 12%, если Начислено больше 1000, но меньше 3000, и 20%, если Начислено больше или равно 3000
Задание 4. Оценка рентабельности рекламной компании фирмы.
1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.
2. Создайте таблицу оценки рекламной кампании по образцу рис. 1. Введите исходные данные: Месяц, Расходы на рекламу А (0) (р.), Сумма покрытия В (0) (р.), Рыночная процентная ставка (j)=13,7%.
Выделите для рыночной процентной ставки, являющейся константой, отдельную ячейку – С3, и дайте этой ячейки имя «Ставка».
Краткая справка. Присваивание имени ячейки или группе ячеек.
Выделите ячейку (группу ячеек или несмежный диапазон), которой необходимо присвоить имя.
Щелкните на поле Имя, которое расположено слева в строке формул.
Помните, что по умолчанию имена являются абсолютными ссылками.
Краткая справка. Расходы на рекламу осуществлялись в течение нескольких месяцев, поэтому выбираем динамический инвестиционный учет. Это предполагает сведение всех будущих платежей и поступлений путем дисконтирования на сумму рыночной процентной ставки к текущему значению.
Примечание. Ячейка А6 в формуле имеет комбинированную адресацию: абсолютную адресацию по столбцу и относительную по строке, и записывается в виде $A6.
При расчете расходов на рекламу нарастающим итогом надо учесть, что первый платеж равен значению текущей стоимости расходов на рекламу, значит в ячейку D6 введем значение =С6, но в ячейке D7 формула примет вид =D6+C7. Далее формулу ячейки D7 скопируйте в ячейки D8:D17.
Обратите внимание, что в ячейках настоящего итога с мая по декабрь будет находиться одно и то же значение, поскольку после мая месяца расходов на рекламу не было (рис. 2).
Рисунок 2 — Рассчитанная таблица оценки рекламной кампании
Выберем сумму покрытия в качестве ключевого показателя целесообразности инвестиций в рекламу. Она определяет, сколько приносит продажа единицы товара в копилку возврата инвестиций.
Для расчета текущей стоимости покрытия скопируйте формулу из ячейки С6 в ячейку F6. В ячейке F6 должна быть формула
Далее с помощью маркера автозаполнения скопируйте формулу в ячейки F7:F17.
Сумма покрытия нарастающим итогом рассчитывается аналогично расходам на рекламу нарастающим итогом, поэтому в ячейку G6 поместим содержимое ячейки F6 (=F6), а в G7 введем формулу
Далее формулу из ячейки G7 скопируем в ячейку G8:G17. В последних трех ячейках столбца будет представлено одно и то же значение, ведь результаты рекламной кампании за последние три месяца на сбыте продукции уже не сказывались.
Сравнив значения в столбцах D и G, уже можно сделать вывод о рентабельности рекламной кампании, однако расчет денежных потоков в течение года (колонка Н), вычисляемый как разница колонок G и D, показывает, в каком месяце была пройдена точка окупаемости инвестиций. В ячейке Н6 введите формулу = G6 – D6, и скопируйте ее на всю колонку.
Проведите условное форматирование результатов расчета, колонки Н: отрицательных чисел – синим курсивом, положительных чисел – красным цветом шрифта. По результатам условного форматирования видно, что точка окупаемости приходится на июль месяц.
4. В ячейке Е19 произведите расчет количества месяцев, в которых сумма покрытия имеется (используйте функцию «Счет» (Формулы – Вставить функцию — Статистические), указав в качестве диапазона «Значение 1» интервал ячеек Е7:Е14). После расчета формула в ячейке Е19 будет иметь вид = СЧЕТ (Е7:Е14).
5. В ячейке Е20 произведите расчет количества месяцев, в которых сумма покрытия больше 100000 р. (используйте функцию СЧЁТЕСЛИ, указав в качестве диапазона «Значение» интервал ячеек Е7:Е14, а в качестве условия > 100000). После расчета формула в ячейке Е20 будет иметь вид =СЧЁТЕСЛИ (Е7:Е14) (рис. 3).
Рисунок 4 — График определения точки окупаемости инвестиций
«Сальдо дисконтированных денежных потоков, нарастающим итогом» по результатам расчетов колонки Н;
«Реклама: расходы и доходы» по данным колонок D и G (диапазоны D5:D17 и G5:G17 выделяйте, удерживая нажатой клавишу [Ctrl]).
Графики дают наглядное представление об эффективности расходов на рекламу и графически показывают, что точка окупаемости инвестиций приходится на июль месяц.
Задание 5. Фирма поместила в коммерческий банк 45000 р. на 6 лет под 10,5% годовых. Какая сумма окажется на счете, если проценты начисляются ежегодно? Рассчитать, какую сумму надо поместить в банк на тех же условиях, чтобы через 6 лет накопилось 250000 р.?
1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу или перейдите на новый лист книги, созданной в Задании 1.
2. Создайте таблицу констант и таблицу для расчета наращенной суммы вклада по образцу (рис. 5).
с помощью формулы А(n)=А(0) * (1+j) n (в ячейку B 10 ввести формулу =$B$3*(1+$B$4)^A10 или использовать функцию СТЕПЕНЬ);
Краткая справка. Функция БС возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.
Все аргументы, обозначающие деньги, которые платятся (например, депозитные вклады), представляются отрицательными числами. Деньги, которые получены (например, дивиденды), представляются положительными числами.
Для ячейки С10 задание параметров расчета функции БС имеет вид, как на рис. 6.
Конечный вид расчетной таблицы приведен на рис. 7.
Рисунок 7 — Результаты расчета накопления финансовых средств фирмы (Задание 2)
Рисунок 8 — Подбор значения суммы вклада для накопления 250000 р.
Задание 6. Сравнить доходность размещения средств организации, положенных в банк на один год, если проценты начисляются m раз в год, исходя из процентной ставки j = 9,5% годовых (рис. 9); по результатам расчета построить график изменения доходности инвестиционной операции от количества раз начисления процентов в году (капитализации).
Рисунок 9 — Исходные данные для Дополнительного задания
Выясните, при каком значении j доходность (при капитализации m = 12) составит 15%.
Краткая справка. Формула для расчета доходности
Примечание. Установите формат значений доходности – «Процентный».
Для проверки правильности ваших расчетов сравните полученный результат с правильным ответом:
Произведите обратные расчет (используйте режим Подбор параметра) для выяснения, при каком значении j доходность (при капитализации m=12) составит 15% (рис. 10).
[expert_bq id=»1570″]Столбец На конец месяца у нас задан формулой, поэтому при удалении значений из предыдущих столбцов, он автоматически обнуляется. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Текстовые значения – самый общий тип данных в экселе. По большому счету этим типом данных является всё, что программа не может определить как формулу или число. Любое значение помеченное как текстовое, используется excel “как есть” – его нельзя умножить, сложить или поделить как число, а также как-то изменить с помощью вычислений.
Как вставить данные из одной таблицы в другую в excel — все про Ексель
4. В ячейке Е19 произведите расчет количества месяцев, в которых сумма покрытия имеется (используйте функцию «Счет» (Формулы – Вставить функцию — Статистические), указав в качестве диапазона «Значение 1» интервал ячеек Е7:Е14). После расчета формула в ячейке Е19 будет иметь вид = СЧЕТ (Е7:Е14).
Как вставить данные из одной таблицы в другую в excel
Как вставить данные из одной таблицы в другую в excel
Всем, кто работает с Excel, периодически приходится переносить данные из одной таблицы в другую, а зачастую и просто копировать массивы в разные файлы. При этом необходимо сохранять исходные форматы ячеек, формулы, что в них находятся, и прочие переменные, которые могут потеряться при неправильном переносе.
Давайте разберёмся с тем, как переносить таблицу удобнее всего и рассмотрим несколько способов. Вам останется лишь выбрать тот, что наилучшим образом подходит к конкретной задачи, благо Microsoft побеспокоилась об удобстве своих пользователей в любой ситуации.
Копирование таблицы с сохранением структуры
Если у вас есть одна или несколько таблиц, форматирование которых необходимо сохранять при переносе, то обычный метод Ctrl+C – Ctrl+V не даст нужного результата.
В результате мы получим сжатые или растянутые ячейки, которые придётся вновь выравнивать по длине, чтобы они не перекрывали информацию.
Расширять вручную таблицы размером в 20-30 ячеек, тем более, когда у вас их несколько, не самая увлекательная задача. Однако существует несколько способов значительно упростить и оптимизировать весь процесс переноса при помощи инструментов, уже заложенных в программу.
Способ 1: Специальная вставка
Этот способ подойдёт в том случае, если из форматирования вам достаточно сохранить ширину столбцов и подтягивать дополнительные данные или формулы из другого файла/листа нет нужды.
- Выделите исходные таблицы и проведите обычный перенос комбинацией клавиш Ctrl+C – Ctrl+V.
- Как мы помним из предыдущего примера, ячейки получаются стандартного размера. Чтобы исправить это, выделите скопированный массив данных и кликните правой кнопкой по нему. В контекстном меню выберите пункт «Специальная вставка».
Нам потребуется «Ширины столбцов», поэтому выбираем соответствующий пункт и принимаем изменения.
Способ 2: Выделение столбцов перед копированием
В этом случае вы сразу получите нужный формат, достаточно выделить столбцы или строки, в зависимости от ситуации, вместе с заголовками. Таким образом, изначальная длина и ширина сохранятся в буфере обмена и на выходе вы получите нужный формат ячеек. Чтобы добиться такого результата, необходимо:
- Выделить столбцы или строки с исходными данными.
- Просто скопировать и вставить, получившаяся таблица сохранит изначальный вид.
Способ 3: Вставка формул с сохранением формата
Специальную вставку можно использовать, в том числе, и для переноса значений формул с сохранением форматов ячеек. Это наиболее простой и при этом быстрый способ произвести подобную операцию. Может быть удобно при формировании таблиц на распечатку или отчётностей, где лишний вес файла влияет на скорость его загрузки и обработки.
- Выделите и скопируйте исходник.
- В контекстном меню вставки просто выберите «Значения» и подтвердите действие.
Вместо третьего действия можно использовать формат по образцу. Подойдёт, если копирование происходит в пределах одного файла, но на разные листы. В простонародье этот инструмент ещё именуют «метёлочкой».
Как сделать, чтобы информация из одного листа автоматически передавалась и отображалась на втором листе
Данная статья о том, как связать таблицы, чтобы данные из одной таблицы автоматически переносились во вторую.
Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.
Первый, самый простой и примитивный способ связи двух таблиц на разных листах документа -вставка данных при помощи опции специальной вставки.
Первый шаг.
Необходимо выделить ту таблицу, из которой будут транслироваться данные во вторую таблицу.
Второй шаг.
Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»
Третий шаг.
Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.
Четвертый шаг.
Поставить курсор в первую (левую верхнюю) ячейку таблицы и выбрать в меню «Вставка» пункт «Вставить связь». В некоторых версиях программы «Excel» этот пункт находится в меню «Специальная вставка»
После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.
Результат вставки связи
Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».
При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.
Как обновить сводную таблицу
При клике правой кнопкой мыши по сводной таблице и нажатии на пункт «Обновить» сводная таблица автоматически перенесет все данные из связанного массива информации («таблицы донора»).
О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:
Как делать сводные таблицы в программе «Excel» и для чего они нужны.
Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».
Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».
Смысл способа в следующем:
Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».
Из таблицы -Power Query
Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок».
Источник данных для запроса Power Query
После выбора области данных появится окно настройки вида новой таблицы. В этом окне Вы можете настроить последовательность вывода столбцов и удалить ненужные столбцы.
После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»
Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»
Обновление запроса в PowerQuery
Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически
То, что вы описали — относится к довольно популярным задачам, которые относительно просто и быстро решать с помощью Excel. Достаточно загнать в программу две ваши таблицы, и воспользоваться функцией ВПР . О ее работе ниже.
Пример работы с функцией ВПР
Ставим указатель мышки в ячейку B2 — то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:
A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);
$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;
2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца — то значение можно было бы копировать из 2-го или 3-го столбца);
ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).
Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!
Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.
Растягиваем формулу (копируем формулу в другие ячейки)
После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!
Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).
Перенос данных из одной таблицы Excel в другую
Таблица в Excel – это сложный массив с множеством параметров. Он может состоять из значений, текстовых ячеек, формул и быть отформатирован разными способами (ячейки могут иметь определенное выравнивание, цвет, направление текста, специальные пометки и т.п.).
При копировании таблицы иногда требуется перенести не все ее элементы, а только некоторые. Рассмотрим, как это можно сделать.
Специальная вставка
Очень удобно выполнять перенос данных таблицы с помощью специальной вставки. Она позволяет выбрать только те параметры, которые нам необходимы при копировании. Рассмотрим пример.
Имеем таблицу с показателями по наличию макарон определенных марок на складе магазина. Наглядно видно, сколько килограмм было в начале месяца, сколько докуплено и продано, а также остаток на конец месяца. Два важных столбца выделены разными цветами. Остаток на конец месяца рассчитан по элементарной формуле.
Попробуем воспользоваться командой СПЕЦИАЛЬНАЯ ВСТАВКА и скопировать все данные.
Сначала мы выделяем имеющуюся таблицу, правой кнопкой вызываем меню и жмем КОПИРОВАТЬ.
В свободной ячейке снова вызываем меню правой кнопкой и нажимаем СПЕЦИАЛЬНАЯ ВСТАВКА.
Если мы оставим все как есть по умолчанию и просто нажмем ОК, то таблица вставится полностью, со всеми ее параметрами.
Попробуем поэкспериментировать. В СПЕЦИАЛЬНОЙ ВСТАВКЕ выберем другой пункт, например, ФОРМУЛЫ. Получили уже неформатированную таблицу, но с работающими формулами.
Теперь вставим не формулы, а только ЗНАЧЕНИЯ результатов их вычислений.
Чтобы новая таблица со значениями получила внешний вид аналогичный образцу выделим ее и вставим ФОРМАТЫ с помощью специальной вставки.
Теперь попробуем выбрать пункт БЕЗ РАМКИ. Получили полную таблицу, но только без выделенных границ.
Полезный совет! Чтобы перенести формат вместе с размерами столбцов нужно перед копированием выделить не диапазон исходной таблицы, а столбцы целиком (в данном случаи это диапазон A:F).
Аналогично можно поэкспериментировать с каждым пунктом СПЕЦИАЛЬНОЙ ВСТАВКИ, чтобы наглядно увидеть, как она работает.
Перенос данных на другой лист
Перенос данных на другие листы книги Excel позволяет связывать несколько таблиц. Это удобно тем, что при замене какого-то значения на одном листе, меняются значения на всех остальных. При создании годовых отчетов это незаменимая вещь.
Рассмотрим, как это работает. Для начала переименуем листы Excel в месяцы. Затем с помощью уже знакомой нам СПЕЦИАЛЬНОЙ ВСТАВКИ перенесем таблицу на февраль и удалим значения из трех столбов:
Столбец «На конец месяца» у нас задан формулой, поэтому при удалении значений из предыдущих столбцов, он автоматически обнуляется.
Перенесем данные по остатку макарон каждой марки с января на февраль. Это делается буквально в пару нажатий.
- На листе ФЕВРАЛЬ ставим курсор в ячейку, показывающую количество макарон марки А на начало месяца. Можно посмотреть на рисунок выше – это будет ячейка D3.
- Ставим в этой ячейке знак РАВНО.
- Переходим на лист ЯНВАРЬ и кликаем на ячейку, показывающую количество макарон марки А на конец месяца (в нашем случае это ячейка F2 на листе «январь»).
Получаем следующее: в ячейке C2 образовалась формула, которая отсылает нас на ячейку F2 листа ЯНВАРЬ. Протянем формулу вниз, чтобы знать количество макарон каждой марки на начало февраля.
Аналогично можно перенести данные на все месяцы и получить наглядный годовой отчет.
Перенос данных в другой файл
Аналогично можно переносить данные из одного файла в другой. Данная книга в нашем примере так и называется EXCEL. Создадим еще одну и назовем ее ПРИМЕР.
Примечание. Создавать новые файлы Excel можно даже в разных папках. Программа будет автоматически искать указанную книгу, независимо от того, в какой папке и на каком диске компьютера она находится.
Скопируем в книгу ПРИМЕР таблицу с помощью все той же СПЕЦИАЛЬНОЙ ВСТАВКИ. И опять удалим значения из трех столбцов. Проведем те же действия, что и в предыдущем параграфе, но переходить уже будем не на другой лист, а на другую книгу.
Получили новую формулу, которая показывает, что ячейка ссылается на книгу EXCEL. Причем видим, что ячейка F2 выглядит как $F$2, т.е. она зафиксирована. И если мы хотим протянуть формулу на остальные марки макарон, сначала нужно удалить значки доллара, чтобы снять фиксацию.
Теперь вы знаете, как грамотно переносить данные из таблиц в рамках одного листа, с одного листа на другой, а также с одного файла в другой.
Иллюстрированный самоучитель по Microsoft Excel 2003
Как копировать и перемещать данные из одной рабочей таблицы в другую
-
Выделите данные, которые нужно перенести или скопировать в другую рабочую таблицу.
Щелкните на ярлычке рабочей таблицы, в которую должны быть скопированы данные.
Щелкните на ячейке, в которой разместится верхний левый угол массива копируемых данных. Затем нажмите клавишу Enter, в результате чего данные будут вставлены или скопированы в таблицу. В данном примере был скопирован столбец формул, поэтому их результаты были обновлены в соответствии с данными таблицы II квартал.
Раздел 1. Принцип работы электронных таблиц
Для расположения текста внутри ячейки в несколько строк можно воспользоваться вкладкой «Выравнивание» в диалоговом окне «Формат» ячейки и отметить там пункт «Переносить по словам» или нажать в ячейке комбинацию клавиш « Alt + Enter ».
