Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью Microsoft Excel. 6-е издание E-Book
Уэйн Винстон
13,99 €
Mehr erfahren.
- Herausgeber: Питер
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Russisch
Уэйн Винстон научит вас быстро анализировать данные, принимать решения, подводить итоги, составлять отчеты, обрабатывать данные и строить аналитические модели в Microsoft Excel 2019 и Office 365. В новом шестом издании вас ждут более 800 бизнес-задач, основанных на реальных ситуациях, а также обсуждение новых инструментов и функций. Где бы вы ни работали — в крупной корпорации, небольшой компании, государственной или некоммерческой структуре, — это поможет вам увеличить прибыль, снизить издержки или эффективно управлять производством. Прочитав эту книгу, вы сможете cпрогнозировать результаты выборов, научитесь определять точки безубыточности, рассчитывать вероятность выигрыша в кости или победы любимой команды в турнире. Хотите обогнать конкурентов? Решайте в Excel реальные задачи!
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 1121
Veröffentlichungsjahr: 2023
Ähnliche
Переводчик А. Коцюба
Уэйн Винстон
Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью Microsoft Excel. 6-е издание. — СПб.: Питер, 2022.
ISBN 978-5-4461-1446-7
© ООО Издательство "Питер", 2022
Все права защищены. Никакая часть данной книги не может быть воспроизведена в какой бы то ни было форме без письменного разрешения владельцев авторских прав.
Оглавление
Вивиан, Джен и Грегу. Вы замечательные, и я вас очень люблю!
Об авторе
Уэйн Л. Винстон — почетный профессор Школы бизнеса Kelley School of Business при Университете Индианы. Он также преподавал в Университете Хьюстона и Уэйк-Форест. Он получил свыше 40 наград для преподавателей, обучил работе в Excel и моделированию в среде Excel множество бизнес-аналитиков из компаний, входящих в Fortune 500, бухгалтерских фирм, армии и флота США. Двукратный чемпион игры Jeopardy! и соавтор системы мониторинга игроков, применяемой Марком Кубаном и командой НБА «Даллас Мэверикс».
Введение
Работаете ли вы в крупной корпорации, в небольшой компании, в правительственной или некоммерческой структуре — если вы читаете эту книгу, то, скорее всего, вы используете Microsoft для выполнения своих повседневных задач. Возможно, вы занимаетесь тем, что подводите итоги, составляете отчеты и анализируете данные. Или же вы строите аналитические модели, призванные помочь работодателю увеличить прибыль, снизить издержки или эффективно управлять операциями.
Начиная с 1999 г. я обучил продуктивному использованию программы Microsoft Excel тысячи аналитиков в таких организациях, как Abbott Labs, консалтинговая компания Booz Allen Hamilton, Bristol-Myers Squibb, Broadcom, Cisco Systems, Deloitte Consulting, Drugstore.com, eBay, Eli Lilly, Ford, General Electric, General Motors, Intel, Microsoft, MGM Hotels Morgan Stanley, NCR, Owens Corning, Pfizer, Proctor & Gamble, PWC, Schlumberger, Tellabs, 3M, Армия США, Министерство обороны США, Военно-морской флот США и Verizon. Студенты часто говорят мне, что инструменты и методы, которые они освоили на занятиях, сэкономили им массу рабочего времени благодаря тому, что важные бизнес-задачи теперь анализируются быстрее и рациональнее.
Методы решения многих бизнес-задач, которые описаны в этой книге, я использую и сам, когда консультирую компании. Например, мы с Excel помогли менеджерам баскетбольных команд НБА «Даллас Мэверикс» и «Нью-Йорк Никербокерс» оценить судейство, игроков и позиции игроков на поле. Последние 20 лет я также веду занятия по бизнес-моделированию и анализу данных в Excel для студентов, обучающихся по программам MBA в Школе бизнеса Kelley School of Business при Университете Индианы, в Бауэровском колледже бизнеса Bauer College of Business при Университете Хьюстона и в Уэйк-Форест. (Мой преподавательский опыт подтвержден более чем 45 наградами для преподавателей, включая шесть наград Школы для преподавателей программ MBA.) Хочу заметить, что 95% студентов MBA из Университета Индианы выбрали мой курс моделирования в электронных таблицах, который даже не входит в обязательную программу.
В этой книге я попытался изложить этот популярный курс так, чтобы его мог пройти каждый. Эта книга научит вас более эффективной работе в Excel, и вот почему:
• Материалы протестированы на тысячах аналитиков из компаний, входящих в Fortune 500, и правительственных организаций.
• Книга написана в разговорном стиле. Я считаю, что такой стиль позволяет перенести дух успешной работы в аудитории на печатные страницы.
• Для обучения я использую задания, упрощающие освоение концепций. В заданиях разбираются реальные ситуации, часто встречающиеся на практике. Многие задания основаны на вопросах, которые задавали мне работники компаний, входящих в Fortune 500.
Я даю все инструкции, необходимые для изучения моих методов работы в Excel. Вы будете читать мои пояснения и отслеживать процесс решения по примерам листов Excel. Кроме того, я разместил файлы шаблонов для заданий на сопроводительном сайте книги (MicrosoftPressStore.com/Excel2019DataAnalysis/downloads). Вы можете использовать эти шаблоны для работы прямо в Excel и самостоятельно выполнить все задания.
Как правило, главы невелики по размеру и посвящены чему-то одному. Я старался сделать так, чтобы каждую главу можно было освоить за два часа. Вопросы в начале каждой главы дадут вам общее представление о тех проблемах, которые вы будете в состоянии разрешить после освоения темы данной главы.
Помимо формул Excel вы безболезненно освоите некоторые важные разделы математики. Например, вы познакомитесь со статистическими методами, прогнозированием, моделями оптимизации, моделированием по методу Монте-Карло, построением моделей управления запасами и теорией очередей. Также вы получите представление о таких новшествах в деловом мышлении, как реальные опционы, потребительская ценность и математические модели ценообразования.
В конце каждой главы я поместил список практических задач (всего их более 800), с которыми вы можете работать самостоятельно. Многие задачи основаны на реальных ситуациях, с которыми имеют дело бизнес-аналитики компаний из списка Fortune 500. Эти задачи помогут до конца понять изложенный в главе материал. Ответы к задачам находятся на сопроводительном сайте книги.
И, самое главное, обучение прежде всего должно доставлять удовольствие. Прочитав эту книгу, вы узнаете, как прогнозировать президентские выборы в США, как определить точки безубыточности для футбольных матчей, как вычислить вероятность выигрыша в кости и вероятность победы определенной команды в турнире Национальной ассоциации студенческого спорта (NCAA). Благодаря подобным интересным примерам вы получите множество интересных и важных сведений о том, как решать бизнес-задачи с использованием Excel.
ПРИМЕЧАНИЕ
Для работы с этой книгой необходимо иметь программу Microsoft Excel 2019 или Office 365. Однако для большей части книги будет вполне достаточно Excel 2013 или 2016. Предыдущие издания этой книги можно использовать с программами Excel 2003, Excel 2007 или Excel 2010.
Новое в этом издании
В это издание книги внесены следующие изменения:
• Добавлена новая глава (глава 40) об инструменте «Получить и преобразовать данные» (Get&Transform).
• Добавлена новая глава (глава 41) о новых типах данных — «География» и «Акции».
• Обсуждение шести новых функций, входящих в Office 365, — ЕСЛИМН, МАКСЕСЛИ, МИНЕСЛИ, СМЕЩ, ОЪЕДИНИТЬ и СЦЕП.
• Обсуждение (в главе 6) функции ТЕКСТ.
• Обсуждение (в главе 48) картограмм и 3D-карт Power Map.
• Обсуждение (в главе 65) инструмента «Лист прогноза».
• Обсуждение (в главе 12) функции ВЫБОР.
• Обсуждение (в главе 76) загрузки данных по различным акциям одновременно.
• Обсуждение (в главе 93) продвинутого анализа чувствительности.
Что необходимо знать для работы с книгой
Для выполнения описанных в книге заданий вам не нужно знать Excel досконально. В принципе, достаточно уметь выполнять два ключевых действия.
• Вводить формулы. Формулы всегда начинаются со знака равенства (=). Следует также знать знаки основных математических операторов. Например, звездочка (*) используется для умножения, слеш (/) — для деления, а знак вставки (^) — для возведения в степень.
• Работать со ссылками на ячейки. Важно помнить, что при копировании формулы, содержащей ссылку на ячейку, оформленную как $A$4 (для создания абсолютной ссылки используется знак доллара), там, куда вы скопируете формулу, формула будет продолжать ссылаться на ячейку A4. При копировании формулы с такой ссылкой, как $A4 (смешанная ссылка), номер столбца останется прежним, а номер строки изменится. И, наконец, при копировании формулы со ссылкой вида A4 (относительная ссылка) изменится и номер строки, и номер столбца ячейки, ссылку на которую содержит формула.
Эти понятия подробно описаны в главе 1.
Как работать с книгой
Для выполнения заданий, представленных в книге, вы можете выбрать один из двух подходов.
• Можно открыть файл шаблона, соответствующий изучаемому заданию, и шаг за шагом выполнять задания по мере прочтения книги. Вы удивитесь, насколько легким окажется процесс обучения и как много всего вы сможете узнать и запомнить. Именно такой подход я использую при обучении студентов.
• Вместо того чтобы работать с шаблоном, можно читать пояснения в книге к окончательному варианту каждого файла с примером.
Сопроводительный сайт
У этой книги есть сопроводительный сайт, на котором предоставлен доступ ко всем используемым в заданиях файлам (на сайт выложены как окончательные варианты книг Excel, так и исходные шаблоны, с которыми можно работать самостоятельно). Книги Excel и шаблоны лежат в папках, названных поглавно. К заданиям, которые помещены в конце каждой главы, есть файлы примеров и ответы. Каждому файлу с ответами присвоено имя, по которому его можно легко идентифицировать. Например, файл с ответом на задание 2 к главе 10 называется s10_2.xlsx.
Для работы с заданиями необходимо скопировать файлы примеров на свой компьютер. Эти файлы и другие данные вы можете скачать по адресу:
MicrosoftPressStore.com/Excel2019DataAnalysis/downloads
Для загрузки файлов откройте страницу в браузере и следуйте инструкции.
Благодарности
Я бесконечно благодарен Дженнифер Скуг (Jennifer Skoog) и Норму Тонине (Norm Tonina), которые поверили в меня и первыми пригласили вести занятия по Excel для Microsoft Finance. В частности, Дженнифер помогла мне составить план и методику занятий, на основе которых написана книга. Кейт Ланге (Keith Lange) из компании Eli Lilly, Пэт Китинг (Pat Keating) и Дуг Хопп (Doug Hoppe) из корпорации Cisco Systems, а также Дэннис Фуллер (Dennis Fuller) из армии США помогли мне прояснить мое понимание того, как следует преподавать анализ и моделирование данных.
Редакторы проекта Рик и Шарлотта Куген (Rick and Charlotte Kughen) — превосходно справились с копированием и редактурой рукописи. Выпускающий редактор Лоретта Йейтс (Loretta Yates) буквально нянчилась с проектом до самого его успешного завершения, а еще она сразу же отвечает на письма! Также я благодарен своим слушателям из организаций, где преподавал, и студентам из Школы бизнеса Kelley School of Business при Университете Индианы и из Бауэровского колледжа бизнеса Bauer College of Business при Университете Хьюстона. Они научили меня таким вещам об Excel, которые я сам не знал.
Алекс Блантон (Alex Blanton), ранее работавший в Microsoft Press, горячо приветствовал этот проект с самого начала и разделял мою точку зрения на создание текста в непринужденном стиле, ориентированного на бизнес-аналитиков.
И наконец, моя любимая талантливая жена Вивиан (Vivian) и мои замечательные дети, Дженнифер (Jennifer) и Грегори (Gregory), мирились с тем, что я проводил долгие часы в выходные дни за клавиатурой.
Поддержка и обратная связь
Ниже вы найдете информацию об опечатках, клиентской поддержке и о том, куда направлять ваши отзывы и предложения.
Контакты
Давайте продолжим наше общение. Twitter: http://twitter.com/MicrosoftPress.
Найденные опечатки и поддержка
Мы приложили все усилия к тому, чтобы информация в книге и на сопроводительном сайте была точной и правильной.
Сведения о найденных опечатках и соответствующих исправлениях печатаются на этой странице (на английском языке):
MicrosoftPressStore.com/Excel2019DataAnalysis/errata
Если вы найдете какую-нибудь новую ошибку, пожалуйста, сообщите нам о ней через эту же страницу.
Если вам требуется дополнительная помощь, напишите в клиентский отдел «Microsoft Press Book» на электронный адрес [email protected].
Пожалуйста, обратите внимание, что по указанным выше адресам не предлагается поддержка программных и аппаратных продуктов Microsoft, поэтому за таковой вам следует обращаться сюда: http://support.microsoft.com.
От издательства
Ваши замечания, предложения, вопросы отправляйте по адресу [email protected] (издательство «Питер», компьютерная редакция).
Мы будем рады узнать ваше мнение!
На веб-сайте издательства www.piter.com вы найдете подробную информацию о наших книгах. Когда мы только начинали перевод книги, перед нами встала сложная задача: подготовить книгу таким образом, чтобы ей могли пользоваться владельцы как английской, так и русской версии Excel. У большинства пользователей в России и других странах СНГ установлена русская версия Excel, однако многие интернациональные компании используют только английские версии Excel, поэтому все команды, формулы, функции и элементы интерфейса даются на двух языках.
В книге сохранены авторские скриншоты интерфейса, так как большая их часть показывает процесс решения авторских задач. Перевод элементов интерфейса дается в тексте, в скобках сохранены оригинальные названия, английские названия функций приводятся в начале глав, далее по тексту идет только русское название.
Обратите внимание: в английской версии Excel десятичные дроби отделяются от целого числа точкой, в русской версии — запятой. Числа с точкой не воспринимаются в русскоязычных версиях Excel, как числовой формат. В файлах для самостоятельной работы используются числа с точкой.
Задачи для самостоятельной работы можно скачать по адресу: MicrosoftPressStore.com/Excel2019DataAnalysis/downloads.
Глава 3. Функции поиска
Обсуждаемые вопросы
• Как создать формулу для вычисления налоговых ставок на основе дохода?
• Как посмотреть цену продукта по идентификатору продукта?
• Допустим, что цена продукта изменяется со временем. Известна дата продажи продукта. Как создать формулу для вычисления цены продукта?
Синтаксис функций поиска
Функция поиска в Excel позволяет просматривать значения в диапазонах на листах книги. В Microsoft Excel 2016 доступен как вертикальный (с помощью функции ВПР), так и горизонтальный просмотр (с помощью функции ГПР). При вертикальном просмотре операция поиска начинается с первого столбца диапазона. При горизонтальном просмотре поиск начинается с первой строки диапазона. Я сосредоточусь на функции ВПР, поскольку в большинстве формул используется вертикальный просмотр.
Синтаксис функции ВПР
Ниже приведен синтаксис функции ВПР(VLOOKUP). В квадратных скобках ([]) указаны необязательные аргументы.
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
• искомое_значение (lookup_value) — значение для поиска в первом столбце таблицы;
• таблица (table_range) — диапазон таблицы. В него входит первый столбец, в котором выполняется поиск искомого значения, и любые другие столбцы, в которых требуется просмотреть результаты расчетов по формулам;
• номер_столбца (column_index) — номер столбца в таблице, из которого функция поиска возвращает значение;
• интервальный_просмотр (range_lookup) является необязательным аргументом. Он устанавливает точное или приблизительное совпадение. Если значение интервальный_просмотр равно ИСТИНА или опущено, первый столбец диапазона таблицы должен быть отсортирован по возрастанию. Если значение интервальный_просмотр равно ИСТИНА или опущено и в первом столбце таблицы найдено точное совпадение, Excel основывает поиск на табличной строке, в которой найдено точное совпадение. Если значение интервальный_просмотр равно ИСТИНА или опущено и нет точного совпадения, то поиск основывается на наибольшем значении в первом столбце, не превышающем искомое значение. Если значение интервальный_просмотр равно ЛОЖЬ и в первом столбце таблицы найдено точное совпадение, поиск основывается на табличной строке, в которой найдено точное совпадение. Если точного совпадения нет, Excel выдает сообщение об ошибке #Н/Д (недоступно). В главе 12 вы узнаете, как использовать функцию ЕСЛИОШИБКА (IFERROR), чтобы Excel не выдавал подобной ошибки (#Н/Д). Обратите внимание, что значение 1 для аргумента интервальный_просмотр эквивалентно значению ИСТИНА, а значение 0 эквивалентно значению ЛОЖЬ.
Синтаксис функции ГПР
Функция ГПР(HLOOKUP) ищет значение в первой строке (а не в первом столбце) таблицы. Для функции ГПР используйте синтаксис функции ВПР, только поменяйте номер_столбца на номер_строки.
Теперь рассмотрим несколько интересных примеров с использованием функций поиска.
Ответы на вопросы
Как создать формулу для вычисления налоговых ставок на основе дохода?
Вот как работает функция ВПР с первым столбцом таблицы, состоящим из чисел, отсортированных по возрастанию. Допустим, налоговая ставка зависит от дохода, как показано в таблице.
Уровень доходов
Налоговая ставка
$0–$9999
15%
$10 000–$29 999
30%
$30 000–$99 999
34%
$100 000 и выше
40%
Как создать формулу для расчета налоговой ставки см. в файле Lookup.xlsx и на рис. 3.1.
Сначала в диапазон ячеек D6:E9 мы ввели соответствующие данные (налоговые ставки и точки прерывания). Таблице D6:E9 присвоено имя lookup. Рекомендуется всегда присваивать имена ячейкам, используемым в качестве диапазона таблицы.
Рис. 3.1. Функция поиска для расчета налоговой ставки. Числа в первом столбце таблицы отсортированы по возрастанию
Тогда нам не нужно помнить точное местонахождение таблицы, а при копировании любой формулы, включающей функцию поиска, диапазон поиска всегда будет правильным. Затем для демонстрации работы функции поиска я вввел в диапазон D13:D17 значения доходов. Налоговая ставка для уровней доходов, указанных в диапазоне D13:D17, была рассчитана путем копирования формулы =ВПР(D13;Lookup;2;ИСТИНА) из E13 в E14:E17.
Проверьте, как работает поиск в ячейках E13:E17. Обратите внимание, что ответ всегда берется из второго столбца таблицы, поскольку в формуле указан номер столбца 2.
• В D13 доход, равный -1000, вызвал ошибку #Н/Д, поскольку такая сумма меньше самого низкого уровня доходов в первом столбце таблицы. Если вам нужно связать налоговую ставку 15% с доходом –$1000, замените 0 в ячейке D6 числом, не большим -1000.
• В D14 доход в $30 000 точно совпадает со значением в первом столбце таблицы, поэтому функция возвращает значение ставки 34%.
• В D15 доход в $29 000 не имеет точного совпадения в первом столбце таблицы. Это означает, что функция поиска остановилась на самом большом числе в первом столбце таблицы, не превышающем $29 000; в данном случае это $10 000. Функция возвратила налоговую ставку из второго столбца таблицы, соответствующую $10 000, то есть 30%.
• В D16 доход в $98 000 не имеет точного совпадения в первом столбце таблицы. Функция поиска остановилась на самом большом числе в первом столбце таблицы, не превышающем $98 000. Она возвратила налоговую ставку из второго столбца таблицы, соответствующую $30 000, то есть 34%.
• В D17 доход в $104 000 не имеет точного совпадения в первом столбце таблицы. Функция поиска остановилась на самом большом числе в первом столбце таблицы, не превышающем $104 000, что означает возврат налоговой ставки из второго столбца таблицы, соответствующей $100 000, то есть 40%.
В F13:F17 я изменил значение аргумента интервальный_просмотр с ИСТИНА на ЛОЖЬ, и скопировал из ячейки F13 в F14:F17 формулу =ВПР(D13;Lookup;2;ЛОЖЬ). Ячейка F14 по-прежнему содержит налоговую ставку 34%, поскольку в первом столбце таблицы имеется точное совпадение — 30000. Во всех других ячейках F13:F17 Excel выдал ошибку #Н/Д, поскольку уровни дохода в этих ячейках не имеют точного совпадения в первом столбце таблицы.
Как посмотреть цену продукта по идентификатору продукта?
Нередко бывает так, что числа в первом столбце не отсортированы по возрастанию. Например, там могут быть перечислены идентификаторы продуктов или имена сотрудников. Из своей практики я знаю, что тысячи финансовых аналитиков не умеют пользоваться функцией поиска, если в первом столбце числа не указаны в возрастающем порядке. Здесь нужно помнить только одно простое правило: для аргумента интервальный_просмотр указывайте значение ЛОЖЬ.
Например, файл Lookup.xlsx (рис. 3.2) содержит цену для пяти продуктов, указанных по идентификаторам. Каким создать формулу, которая по идентификатору продукта выдавала бы цену продукта?
Рис. 3.2. Поиск цен по идентификаторам продуктов. Если таблица не отсортирована по возрастанию, укажите в формуле значение ЛОЖЬ для последнего аргумента функции поиска
Многие ввели бы в ячейку I18 формулу =ВПР(H18;Lookup2;2). Но, если вы опустите четвертый аргумент (интервальный_просмотр), предполагается, что его значение равно ИСТИНА. Тогда будет возвращена неправильная цена (3,50), поскольку идентификаторы продуктов в таблице Lookup2 (ячейки H11:I15) перечислены не в алфавитном порядке. Если ввести в ячейку I18 формулу =ВПР(H18;Lookup2;2;ЛОЖЬ), цена будет возвращена правильная (5,20).
Для поиска зарплаты сотрудника по его фамилии или идентификационному номеру укажите значение ЛОЖЬ в формуле аналогичным образом.
Кстати, на рис. 3.2 видно, что столбцы A–G скрыты. Чтобы скрыть нужные столбцы, сначала выделите их. Затем на вкладке Главная (Home) в группе Ячейки (Cells) раскройте список Формат (Format), в Видимость (Visibility) выберите Скрыть илиотобразить (Hide & Unhide) и затем Скрыть столбцы (Hide Columns).
Допустим, что цена продукта изменяется со временем. Известна дата продажи продукта. Как создать формулу для вычисления цены продукта?
Допустим, цена продукта зависит от даты его продажи. Каким образом можно написать для формулы функцию поиска правильной цены продукта? Например, для следующей таблицы?
Дата продажи
Цена
Январь–апрель 2005 г.
$98
Май–июль 2005 г.
$105
Август–декабрь 2005 г.
$112
Напишите формулу для определения правильной цены продукта для любой из дат продажи продукта в 2005 г. Для разнообразия воспользуйтесь функцией ГПР. Даты изменения цены приведены в первой строке таблицы (см. файл Datelookup.xlsx и рис. 3.3).
Рис. 3.3. Функция ГПР для определения цены, изменяющейся в зависимости от даты продажи
Я скопировал формулу =ГПР(B8,lookup,2,ИСТИНА) из C8 в C9:C11. Формула пытается сравнить даты из столбца B с первой строкой диапазона B2:D3. Для любой даты между 01.01.05 и 30.04.05 функция поиска выбирает 01.01.05 и возвращает значение цены из B3; для любой даты между 01.05.05 и 31.07.05 функция поиска выбирает 1 мая и возвращает значение цены из C3; и для любой даты после 01.08.05 выбирается 1 августа и возвращается значение цены из D3.
Задания
1. В файле Hr.xlsx указаны идентификационные номера, оклады и стаж работников. Создайте формулу для поиска зарплаты работника по идентификационному коду. Создайте еще одну формулу, находящую стаж работника по его идентификационному коду.
2. В файле Assign.xlsx работники распределены по четырем группам. Также там указано, насколько данный работник пригоден для работы в каждой группе (по шкале от 0 до 10). Напишите формулу, которая вычисляет, насколько пригоден каждый работник для группы, в которую его назначили.
3. Вы подумываете о рекламе продуктов Microsoft на спортивном канале. Чем больше рекламных объявлений вы покупаете, тем ниже цена одного объявления (см. таблицу ниже).
Количество рекламных объявлений
Цена одного рекламного объявления
1–5
$12 000
6–10
$11 000
11–20
$10 000
21 и более
$9000
Например, при покупке 8 рекламных объявлений за одно объявление придется выложить $11 000, а при покупке 14 объявлений — $10 000. Напишите формулу, рассчитывающую общие затраты для любого количества рекламных объявлений.
4. Вы подумываете о рекламе продуктов Microsoft в популярной музыкальной телепрограмме. За первую группу рекламных объявлений вы платите фиксированную сумму, но если вы покупаете больше рекламных объявлений, цена за каждое объявление снижается (см. таблицу ниже).
Количество рекламных объявлений
Цена одного рекламного объявления
1–5
$12 000
6–10
$11 000
11–20
$10 000
21 и более
$9000
Например, при покупке 8 рекламных объявлений первые 5 объявлений обойдутся в $12 000 каждое, а оставшиеся — по $11 000. При покупке 14 объявлений первые 5 стоят $12 000, следующие 5 по $11 000 и оставшиеся 4 по $10 000. Напишите формулу расчета общих затрат на покупку любого количества объявлений. Подсказка: вам потребуются минимум три столбца в таблице и две функции поиска в формуле.
5. В следующей таблице приведены ежегодные процентные ставки по предоставляемым банком кредитам на 1, 5, 10 и 30 лет.
Срок кредитования, лет
Процентная ставка, %
1
6
5
7
10
9
30
10
Если кредит в банке взят на любой срок от 1 до 30 лет, не указанный в таблице, процентная ставка рассчитывается путем интерполяции между ставками, указанными в таблице. Например, если кредит взят на 15 лет, процентная ставка определяется по формуле:
.
Напишите формулу расчета процентной ставки по кредиту для любого срока от 1 года до 30 лет.
6. Расстояние между двумя городами США (Аляску и Гавайи не учитываем) можно вычислить приблизительно по следующей формуле:
В файле Citydata.xlsx приведен список городов США с шириной и долготой. Создайте таблицу, вычисляющую расстояние между любыми двумя городами из данного списка.
7. На первом листе книги Pinevalley.xlsx указаны зарплаты сотрудников Университета Pine Valley, на втором листе указан их возраст, на третьем — стаж работы. Создайте четвертый лист, содержащий зарплату, возраст и стаж каждого сотрудника.
8. В файле Lookupmultiplecolumns.xlsx приведены данные продаж в магазине электроники. Имя продавца будет введено в B17. Создайте формулу Excel, которую можно скопировать из C17 в D17:F17 и которая извлекает данные о том, сколько этот продавец продал радиотоваров, в C17, телевизоров — в D17, принтеров — в E17 и CD — в F17.
9. В файле Grades.xlsx приведены экзаменационные оценки студентов. Допустим, соотношение баллов и оценок следующее (см. табл.).
Баллы
Оценка
Ниже 60
F
60–69
D
70–79
C
80–89
B
90 и выше
A
При помощи формулы Excel определите оценку каждого студента по баллам.
10. В файле Employees.xlsx вы найдете рейтинг (по шкале от 0 до 10), присвоенный каждым из 35 сотрудников трем заданиям. В файле также указаны задания, выданные каждому сотруднику. С помощью формулы определите рейтинг, который каждый сотрудник присвоил назначенному ему заданию.
11. Допустим, что за один доллар дают 1000 иен, 5 песо или 0,7 евро. Создайте электронную таблицу, в которую можно ввести сумму в долларах США, указать валюту и затем перевести введенную сумму в указанную валюту.
12. В файле Qb2013.xlsx содержится статистика по квотербэкам (QB) национальной футбольной лиги за сезон 2013 г. Напишите в ячейках J2 и K2 формулы, возвращающие количество забитых мячей и перехватов мяча конкретного нападающего, когда вы вводите его имя в ячейке I2.
13. Файл NBAplayers.xlsx содержит данные о возрасте и заработной плате нескольких игроков НБА. Введите формулы в ячейках J5:K50, которые возвращают возраст и зарплату каждого игрока.
14. В столбце F файла Hardware.xlsx содержатся коды хозтоваров, а в столбце G — цены этих товаров. В столбцах M–O
