автордың кітабын онлайн тегін оқу Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью Microsoft Excel
Переводчик А. Коцюба
Уэйн Винстон
Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью 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» на электронный адрес microsoftpresscs@pearson.com.
Пожалуйста, обратите внимание, что по указанным выше адресам не предлагается поддержка программных и аппаратных продуктов Microsoft, поэтому за таковой вам следует обращаться сюда: http://support.microsoft.com.
От издательства
Ваши замечания, предложения, вопросы отправляйте по адресу comp@piter.com (издательство «Питер», компьютерная редакция).
Мы будем рады узнать ваше мнение!
На веб-сайте издательства www.piter.com вы найдете подробную информацию о наших книгах. Когда мы только начинали перевод книги, перед нами встала сложная задача: подготовить книгу таким образом, чтобы ей могли пользоваться владельцы как английской, так и русской версии Excel. У большинства пользователей в России и других странах СНГ установлена русская версия Excel, однако многие интернациональные компании используют только английские версии Excel, поэтому все команды, формулы, функции и элементы интерфейса даются на двух языках.
В книге сохранены авторские скриншоты интерфейса, так как большая их часть показывает процесс решения авторских задач. Перевод элементов интерфейса дается в тексте, в скобках сохранены оригинальные названия, английские названия функций приводятся в начале глав, далее по тексту идет только русское название.
Обратите внимание: в английской версии Excel десятичные дроби отделяются от целого числа точкой, в русской версии — запятой. Числа с точкой не воспринимаются в русскоязычных версиях Excel, как числовой формат. В файлах для самостоятельной работы используются числа с точкой.
Задачи для самостоятельной работы можно скачать по адресу: MicrosoftPressStore.com/Excel2019DataAnalysis/downloads.
Глава 1. Основы моделирования в электронных таблицах
Обсуждаемые вопросы
• Как эффективно определить понедельные выплаты всех моих сотрудников?
• Как эффективно определить, сколько пекарня должна каждому из своих поставщиков?
• Как оценить, какое количество посетителей будет иметь новый фитнес-клуб через 10 лет?
• Как правильно задавать порядок выполнения операций в Excel?
• Как определить влияние на прибыль изменения в цене и себестоимости единицы товара для кофейни по соседству?
Мой друг Деннис Фуллер сказал как-то: «Электронная таблица для бизнес-аналитика — это то же, что холст для художника». Никто из нас не может написать шедевр, подобный «Звездной ночи» Ван Гога, но, зная Excel, мы можем начать с чистого листа электронной таблицы и создать свой собственный шедевр, моделирующий виртуально любую ситуацию. Для многих барьером к овладению Excel является понимание того, как в Excel работают формулы. В этой главе мы разберем несколько различных простых моделей таблиц, которые помогут вам перейти к созданию сложных моделей в Excel.
Ответы на вопросы
В файле Wagestemp.xlsx (в папке Templates), показанном на рис. 1.1, заданы количество отработанных часов и почасовая оплата нескольких сотрудников. Необходимо определить выплату каждому сотруднику, а также общую сумму отработанных часов и среднюю почасовую оплату.
Для вычисления недельной зарплаты сотрудника Luka необходимо перемножить значения в ячейках C4 и D4. Для этого в ячейку E4 вы помещаете формулу =C4*D4.
Рис. 1.1. Расчет недельной платежной ведомости
Для вычисления зарплаты Terry можно было бы перейти в ячейку E5 и ввести формулу =C5*D5, но можно воспользоваться командой Excel Копировать (Copy), что позволит легко вычислить недельную зарплату каждого сотрудника, будь их хоть миллион. (Excel 2007 и более поздние версии поддерживают 1 048 576 строк!) Просто перейдите в ячейку E4 и нажмите Ctrl+C для копирования формулы. Затем выделите диапазон E5:E11, или примените комбинацию Ctrl+V, или нажмите Enter, чтобы применить эту формулу к диапазону E5:E11. Кроме того, вы можете скопировать формулу из E4 в диапазон E5:E11, наведя курсор на маленький квадратик в нижнем правом углу ячейки E4, и, после того как курсор изменит свой вид на тонкое черное перекрестье, нажать левую кнопку мыши и перетащить нужную формулу в диапазон E5:E11. В каждой ячейке, куда скопирована формула, Excel перемножит два значения слева от этой ячейки в столбце E. Отметьте, что мы будем часто использовать функцию Excel Ф.ТЕКСТ (FORMULATEXT) (появившуюся в Excel 2013), чтобы текст формул был виден в электронной таблице. Например, если ввести в ячейке F4 формулу =FORMULATEXT(E4), то в ней отобразится формула из E4.
Кроме того, вы можете вычислить в ячейке C12 (вы можете это увидеть в файле Wagesfinal.xlsx) общую сумму отработанных часов за неделю с помощью формулы =SUM(C4:C11). В ячейке E14 я вычислил среднюю зарплату работника по формуле =СРЗНАЧ(E4:E11) .
В файле Bakery1temp.xlsx задана цена за фунт, которую пекарня платит каждому из шести поставщиков сахара, масла и муки. Нам нужно узнать сумму, которая выплачивается каждому поставщику сахара, муки и масла. Также нам нужно определить суммарную выплату.
Как показано на рис. 1.2, в ячейке E23 мы вычисляем сумму, выплаченную за сахар поставщику Supplier 1, умножая стоимость фунта сахара, запрошенную Supplier 1, на количество сахара, закупленного у этого поставщика. Для этого мы используем формулу =E5*E14.
Чтобы вычислить сумму, выплаченную каждому поставщику за каждый продукт, мы можем воспользоваться любым из приведенных далее способов:
• Выделите ячейку E23 и, нажав Ctrl+C, выберите диапазон E23:G28, а затем нажмите Ctrl+V.
• Выделите ячейку E23 и, нажав Ctrl+C, выберите диапазон E23:G28, а затем нажмите Enter.
• Выделите ячейку E23 и, после того как вид курсора изменится на перекрестье, перетащите формулу в F23:G23. Затем перетащите диапазон E23:G23 в E24:G28.
Рис. 1.2. Вычисление выплат пекарни: цены у поставщиков различаются
Чтобы найти сумму, выплаченную каждому из поставщиков, введите в H23 формулу =СУММ(E23:G23) и скопируйте эту формулу в H24:H28. Введя формулу =СУММ(E23:E28) в ячейку E29 и скопировав эту формулу в F29:H29, вы получите общую сумму, выплаченную за каждый продукт.
Более быстрый способ задать эти суммы — это выбрать диапазон H23:H28 и (с нажатой клавишей Ctrl) выбрать диапазон E30:G30. Затем просто нажмите кнопку Сумма (AutoSum, значок
Рис. 1.3. Использование кнопки AutoSum для суммирования группы чисел
Команда Сумма выбирает (не всегда верно, так что будьте внимательны!) диапазон ячеек, которые вы хотите просуммировать, чтобы заполнить выделенные ячейки. Сумма может сэкономить вам пять секунд времени!
В файле Bakery2temp.xlsx, изображенном на рис. 1.4, мы исходим из предположения, что все поставщики запрашивают одинаковую цену на каждый из продуктов. Снова вычислите сумму, выплаченную каждому из поставщиков за каждый продукт, и общую сумму, которую пекарня выплатит каждому поставщику.
Рис. 1.4. Вычисление выплат пекарни: цены у поставщиков одинаковые
Действуя, как предлагалось ранее, неискушенный читатель может ввести в ячейку E23 формулу =E12*E14 и скопировать эту формулу в диапазон E23:G28. К сожалению, в строках от 24 до 28 ссылки на строки и 12, и 14 будут изменяться. При копировании этой формулы мы хотели бы, чтобы ссылка на строку 14 менялась, а на 12 — нет, потому что нам нужно брать цену каждого продукта из строки 12. Чтобы этого добиться, мы помещаем перед 12 знак $. Это называется абсолютной адресацией или блокированием строки. Когда в формуле перед номером строки стоит знак доллара и эта формула копируется, номер строки останется неизменным. Поэтому мы вводим в ячейку E23 формулу =E$12*E14.
Простой способ добавить знак доллара — использовать клавишу F4. Если вы выделите часть формулы и будете несколько раз нажимать F4, Excel циклически добавит сначала знак доллара к строке и к столбцу, затем только к строке, затем только к столбцу, а затем совсем уберет знак доллара.
Ответ вы найдете в файле Chapter1customer.xlsx, приведенном на рис. 1.5. Теперь нам требуется создать модель с чистого листа. Модели в электронных таблицах содержат исходные данные или предположения, которые мы используем, чтобы вычислить требуемые выходные значения. В базовой исходной модели оценки посетителей нам необходимы три исходных значения:
• Число посетителей, посещающих клуб, на начало года 1 (Year 1).
• Коэффициент оттока клиентов: доля посетителей на начало года (не считая новых посетителей), которые перестают посещать клуб, каждый год.
• Число новых посетителей, приходящих каждый год.
Рис. 1.5. Прогнозирование числа посетителей с помощью построения модели с входными и выходными значениями
Мы ввели значения этих исходных данных в ячейки C2:C4. Важно следить, чтобы исходные данные в электронной таблице были отделены от выходных значений и никогда не программировались формулами Excel. Отделение в электронной таблице исходных данных от выходных значений позволяет легко определять, как изменение в исходных данных влияет на выходные значения.
В строках 8–17 рассчитывается количество посетителей на конец года путем сложения количества новых посетителей с количеством посетителей в начале года за вычетом посетителей, которые перестают посещать клуб. В ячейках С2:С4 содержатся входные значения этой таблицы. Ключевыми отношениями нашей модели оценки числа посетителей являются следующие:
• (Посетители на конец года t (End Year t Customers)) = (Посетители на начало года t (Start Year t Customers)) + (Новые посетители года t (New Year t Customers)) – (Переставшие ходить (Year Customer quits)).
• (Посетители на начало года 1 (Start Year 1 Customers)) = значение в ячейке C2.
Другим ключом к решению этой задачи является понимание того, что нам нужно отслеживать в течение каждого года:
• число посетителей на начало года;
• новые посетители, приходящие каждый год;
• посетители, перестающие ходить;
• число посетителей на конец года.
В ячейке C8 мы вычисляем начальное число посетителей в первый год (Year 1) по формуле =C2. Затем в столбце D мы повторно вводим число новых посетителей для каждого года, копируя значение из D8 в D9:D17 с помощью формулы =$C$3 или C$3.
Обратите внимание, что цифре 3 должен предшествовать знак доллара; в противном случае при копировании формулы из ячейки D8 обращение к 3 изменится, что приведет к неверным результатам. Перед символом C знак доллара можно как вставлять, так и не вставлять, так как мы не копируем эту формулу в другой столбец.
Число посетителей, перестающих посещать клуб каждый год, — это число посетителей в начале года, умноженное на коэффициент оттока клиентов. Поэтому в столбце E мы вычисляем количество посетителей, отсеивающихся каждый год, копируя из E8 в E9:E18 формулу =$C$4*C8 или C$4*C8. Отметьте, что здесь перед цифрой 8 мы не используем знак доллара, потому что при копировании мы хотим, чтобы 8 изменялась на 9, 10 и т.д.
Количество посетителей на конец каждого года вычисляется как сумма посетителей на начало этого года плюс новые клиенты, из которой вычитается количество покинувших клуб клиентов. Скопировав из F8 в F9:F18 формулу =C8+D8-F8, мы вычисляем число посетителей на конец каждого года.
Для годов 2–10 число клиентов на начало года равно числу клиентов в конце прошлого года, поэтому копируем из C9 в C10:C17 формулу =F8. Мы получим (не переживайте по поводу дробных чисел), что через 10 лет в нашем фитнес-клубе будет около 127 посетителей.
Проницательный читатель может возразить, что на самом деле мы не знаем коэффициента оттока клиентов и количества новых посетителей каждый год. Это действительно так. Мы должны выполнить анализ чувствительности, чтобы выяснить, как изменения числа новых клиентов и коэффициента ежегодного оттока клиентов меняют конечное число посетителей 10-го года. В главе 17 мы изучим, как использовать таблицы данных для выполнения такого анализа чувствительности.
Сложные формулы в Excel часто содержат множество сложных математических операций, таких как возведение в степень, умножение и деление. При вычислении формул Excel следует правилам выполнения математических операций:
• Сначала выполняются операции в скобках.
• Затем выполняются все возведения в степень, следуя слева направо.
• Затем выполняются все умножения и деления, следуя слева направо.
• После этого выполняются все сложения и вычитания, следуя слева направо.
Например, Excel будет вычислять формулу =3+6*(5+4)/3–7 в следующем порядке.
• 3+6 × 9/3 – 7 (скобки сняты).
• 3 + 54/3 – 7 (умножение).
• 3 + 18 – 7 (деление).
• 21 – 7 (суммирование).
• 14 (вычитание).
Или, например, предположим, нам нужно извлечь квадратный корень из значения ежегодного процентного роста продаж наших продуктов (см. файл PEMDAStemp .xlsx и рис. 1.6).
Рис. 1.6. Пример применения порядка выполнения операций
В F4 мы ввели правильную формулу, =((E4-D4)/D4)^0.5, и скопировали ее в F5:F6. Эта формула заставляет Excel вычислить процентную долю (выраженную в виде дроби) роста продаж (.5) и затем извлечь квадратный корень. Окончательное значение .707 (квадратный корень из .5) правильно. Обратите внимание: знак ^ (находится на клавиатуре на цифре 6) — это знак возведения в степень.
В G4 мы ввели неправильную формулу, =(E4-D4)/D4^.5.
По этой формуле сначала вычисляется E4-D4 = 50, а затем извлекается квадратный корень из D4 (10). После этого конечный (неверный) результат такой: 50/10 = 5.
Ключом к пониманию того, как изменение цены влияет на прибыль, является правильная оценка кривой спроса. Кривая спроса показывает, как изменения в цене меняют спрос на продукт. Давайте предположим, что дневной спрос на кофе в кофейне составляет 100-15*Цена в долларах. (См. главы 87–89, где подробнее рассматривается оценка кривой спроса.) В файле Coffee.xlsx (рис. 1.7) показано, как дневная прибыль зависит от того, как меняются себестоимость и цена чашки кофе.
Рис. 1.7. Зависимость спроса от цены и себестоимости
Мы исходим из предположения, что себестоимость чашки кофе варьируется между $0,50 и $2,00, а цена чашки кофе варьируется между $2,00 и $5,00. Чтобы определить прибыль для каждой комбинации цена/себестоимость, нам потребуется ввести в F10 формулу =($F$5-$F$4*F$9)*(F$9-$E10) и скопировать эту формулу из F10 в F10:L13.
• Ссылки на F5 и F4 — абсолютные, так как нам нужно, чтобы ни строка, ни столбец не менялись при копировании формулы.
• Ссылка на цену (ячейка F9) требует использования знака доллара (или блокирования строки), так как нам всегда требуется, чтобы цена бралась из строки 9.
• Ссылка на себестоимость (E10) требует, чтобы перед буквой столбца стоял знак доллара, так как нам всегда требуется, чтобы себестоимость бралась из столбца E.
Мы получим, например, что если себестоимость равна $1,50, а мы продаем чашку за $4,00, наша прибыль составит $100: (100 – 4*15)*(4 – 1,5) = $100.
Заметьте, что для каждого значения себестоимости цена, при которой прибыль максимальна, выделяется желтым фоном. В главе 24 вы узнаете, как использовать условное форматирование для создания такого стильного выделения.
Задания
1. Проводя занятия, я дал пять домашних заданий, каждое оценив в 25 баллов, и три теста, оценив каждый в 100 баллов. Я вычисляю итоговую оценку студента, присваивая весовой коэффициент в 75% для тестов и 25% для домашних заданий. Создайте таблицу для вычисления итоговой оценки студента, которая позволит вам изменять весовой коэффициент, присвоенный тестам.
2. Индекс массы тела человека (ИМТ, BMI) вычисляется так: BMI=703*Weight/Height2. Создайте таблицу вычисления ИМТ человека.
3. Последовательность Фибоначчи определяется следующим образом: F0 = 0, Fx = 1, а для n больше 1 — FN+X = FN + Fn–1. Создайте таблицу для вычисления последовательности Фибоначчи. Покажите, что для больших значений N отношение последовательных чисел Фибоначчи стремится к золотому сечению (1,62).
4. Знаменитый эффект бабочки утверждает, что если бабочка взмахнет крыльями на Таити, это маленькое событие может вызвать ураган в Техасе. Предположим, что погода во время t всегда имеет значение между 0 и 1 и управляется выражением xt+1 = 4 × xt × (1–xt). Для xt = 0,3 и xt = 0,3000001 вычислите x1, x2, ... x50. Как ваши вычисления иллюстрируют эффект бабочки?
5. Зарыбление озера оценивается в настоящее время в 12 230 особей. Каждый год коэффициент рождений у рыб равен 1,2, а коэффициент смертей равен 0,7. Покажите, что если улов каждый год будет составлять 6115 особей, количество рыбы в озере будет оставаться постоянным.
6. Коэффициент Джини обычно используется для измерения расслоения общества по уровню годового дохода. Если доходы n человек перечислены по возрастанию (x1= наименьший доход, xn = наибольший доход), то коэффициент Джини вычисляется по формуле:
Создайте таблицу, определяющую коэффициент Джини для группы из пяти человек.
Глава 2. Имена диапазонов
Обсуждаемые вопросы
• Необходимо вычислить общий объем продаж в штатах Аризона, Калифорния, Монтана, Нью-Йорк и Нью-Джерси. Можно ли для вычисления общего объема продаж воспользоваться формулой AZ+CA+MT+NY+NJ вместо формулы СУММ(A21:A25) и получить правильный ответ?
• Для чего нужна формула СРЗНАЧ(A:A)?
• Чем различаются имена с областью действия «книга» и «лист»?
• Мне начинают нравиться имена диапазонов. Я стал использовать имена диапазонов во многих книгах, которые я создаю в офисе. Однако эти имена не появляются в формулах. Как добиться отображения недавно созданных имен диапазонов в ранее созданных формулах?
• Каким образом можно вставить список имен всех диапазонов (и представляемых ими ячеек) в лист?
• Предполагаемый годовой доход вычисляется как кратный прошлогоднему доходу. Может ли формула выглядеть как (1+прирост)*предыдущий_год?
• Для каждого дня недели даны почасовая оплата и количество отработанных часов. Можно ли вычислить итоговую сумму оплаты за каждый день по формуле почасовая_оплата*часы?
Возможно, вам доводилось работать с листами, в которых использовалась, например, формула =СУММ(A5000:A5049). В этом случае вам приходилось догадываться, что находится в ячейках A5000:A5049. Если в ячейках A5000:A5049 содержатся объемы продаж по всем штатам США, не кажется ли вам формула =СУММ(USSales) более понятной? В данной главе рассказывается, как присваивать имена отдельным ячейкам и диапазонам ячеек, а также вставлять имена диапазонов в формулы.
Как создать именованный диапазон?
Создать именованный диапазон можно тремя способами:
• ввести имя диапазона в поле Имя (Name);
• выбрать на вкладке Формулы (Formulas) в группе Определенные имена (Defined Names) инструмент Создать из выделенного (Create from Selection);
• выбрать на вкладке Формулы (Formulas) в группе Определенные имена (Defined Names) инструментов Присвоить имя (Define Name) или Диспетчер имен (Name Manager).
Создание имени диапазона в поле Имя
Поле Имя (Name) (рис. 2.1) находится прямо над меткой столбца A, слева от поля Строка формул (Formula bar). Для создания имени диапазона выделите ячейку или диапазон ячеек, которым требуется присвоить имя, затем перейдите в поле Имя (Name) и введите имя диапазона. Имя диапазона создастся, когда вы нажмете Enter. При нажатии в поле Имя (Name) на стрелку появятся имена диапазонов, которые есть в текущей книге. Клавиша F3 открывает диалоговое окно Вставка имени (Paste Name) с именами всех диапазонов. Если выбрать в поле Имя (Name) имя диапазона, то все ячейки, соответствующие этому диапазону, отметятся автоматически. Так вы можете убедиться, что правильно выбрали ячейку или диапазон ячеек для указанного имени. Имена диапазонов не чувствительны к регистру букв.
Скажем, нам нужно присвоить ячейке F3 имя east, а ячейке F4 — имя west (см. рис. 2.2 и файл Eastwestempt.xlsx). Выделите ячейку F3, введите east в поле Имя (Name) и нажмите Enter. Выделите ячейку F4, введите west в поле Имя (Name) и нажмите Enter. Теперь в какой-нибудь другой ячейке для ссылки на ячейку F3 можно указывать =east, а не =F3. То есть вместо любой ссылки east в формуле будет автоматически подставлено значение из ячейки F3.
|
|
| Рис. 2.1. Можно создать имя диапазона, выбрав диапазон ячеек, который вы хотите назвать, и введя имя в поле Имя |
Рис. 2.2. Присвоение ячейкам F3 и F4 имен east и west |
Предположим, необходимо присвоить имя data прямоугольному диапазону ячеек (например, A1:B4). Выделите диапазон ячеек A1:B4, введите data в поле Имя (Name) и нажмите Enter. Теперь мы можем вычислить среднее значение содержимого ячеек A1:B4 с помощью функции СРЗНАЧ(AVERAGE) по формуле =СРЗНАЧ(data) (см. файл Data.xlsx и рис. 2.3).
Иногда требуется присвоить имя диапазону ячеек, состоящему из нескольких несмежных прямоугольных диапазонов. Например, на рис. 2.4 и в файле Noncontigtemp.xlsx показан диапазон с именем noncontig, состоящий из ячеек B3:C4, E6:G7 и B10:C10. Выделите любой из трех прямоугольников (здесь B3:C4). С нажатым Ctrl выделите оставшиеся два диапазона (E6:G7 и B10:C10). Отпустите Ctrl, введите noncontig в поле Имя (Name) и нажмите Enter. Теперь в любой формуле имя noncontig указывает на содержимое ячеек B3:C4, E6:G7 и B10:C10. Например, введя формулу =СРЗНАЧ(noncontig) в ячейку E11, мы получим значение 4,75 (сумма 12 чисел в заданном диапазоне равна 57 и 57/12 = 4,75).
Рис. 2.3. Присвоение диапазону A1:B4 имени data
Рис. 2.4. Присвоение имени несмежному диапазону ячеек
Создание имен с помощью инструмента Создать из выделенного
На листе Statestemp.xlsx указаны мартовские продажи для каждого из 50 штатов США. На рис. 2.5 вы видите фрагмент этих данных. Требуется присвоить каждой ячейке в диапазоне B6:B55 правильную двухбуквенную аббревиатуру — сокращенное название штата. Сначала выделите диапазон A6:B55 и на вкладке Формулы (Formulas) в группе Определенные имена (Defined Names) выберите Создать из выделенного (Create from Selection), как показано на рис. 2.6, а затем в открывшемся диалоговом окне установите флажок В столбце слева (Left column) (рис. 2.7) и нажмите OK.
Теперь имена в первом столбце выделенного диапазона связаны с ячейками во втором столбце выделенного диапазона. Таким образом, ячейке B6 присвоено имя диапазона AL, ячейке B7 — AK и т.д. Мы бы устали создавать имена таких диапазонов с помощью поля Имя (Name)! Нажмите на стрелку в поле Имя (Name) и убедитесь, что все имена диапазонов созданы.
Рис. 2.5. После присвоения имен ячейкам, содержащим продажи и сокращенные названия штатов, при ссылке на ячейку вместо буквы столбца и номера строки можно использовать соответствующее сокращенное название
|
|
| Рис. 2.6. Выберите Создать из выделенного |
Рис. 2.7. Установите флажок в столбце слева |
Создание имен диапазонов с помощью инструмента Присвоить имя
Откроем диалоговое окно Создание имени (New Name), показанное на рис. 2.8. Для этого на вкладке Формулы (Formulas) в группе Определенные имена (Defined Names) нажмите Присвоить имя (Define Name) в меню, показанном на рис. 2.6.
Предположим, нам нужно присвоить имя range1 (в именах диапазонов регистр букв не учитывается) диапазону ячеек A2:B7. Введите range1 в поле Имя (Name) и выделите диапазон или введите =A2:B7 в поле Диапазон (Refers To). Диалоговое окно Создание имени (New Name) должно выглядеть так, как на рис. 2.9. Нажмите OK.
Нажав на стрелку раскрывающегося списка в поле Область (Scope), вы можете выбрать строку Книга (Workbook) или любой лист в книге, указав тем самым область действия имени. Более подробно этот вопрос обсуждается далее в этой главе, а пока выберите область действия по умолчанию — Книга (Workbook). Кроме того, к любым именам диапазонов можно добавить комментарии.
|
|
| Рис. 2.8. Диалоговое окно Создание имени до задания каких-либо имен |
Рис. 2.9. Диалоговое окно Создание имени при создании имени диапазона |
Диспетчер имен
Если теперь вы нажмете на стрелку в поле Имя (Name), то увидите в раскрывающемся списке имя range1 (и все остальные созданные ранее диапазоны). В Microsoft Excel 2019 есть простой способ изменения или удаления имен диапазонов. На вкладке Формулы (Formulas) выберите группу Определенные имена (Defined Names) и откройте Диспетчер имен (Name Manager), показанный на рис. 2.6. Появится список имен всех диапазонов. Например, так выглядит диалоговое окно Диспетчер имен (Name Manager) для файла States.xlsx (рис. 2.10).
Для изменения имени диапазона дважды щелкните кнопкой мыши на имени этого диапазона или выделите его и нажмите кнопку Изменить (Edit). После этого можно изменить не только имя диапазона, но и его область действия, а также поменять ячейки в диапазоне.
Чтобы удалить некоторое подмножество имен диапазонов, выделите имена диапазонов: если имена диапазонов идут последовательно, выделите первое имя в группе имен, которую требуется удалить, затем, удерживая Shift, выделите последнее имя в группе. Если требуемые имена не следуют друг за другом, можно выделить любое из имен, которое необходимо удалить, а далее, удерживая Ctrl, выделить остальные требуемые имена диапазонов. Затем нажмите кнопку Удалить (Delete).
Теперь рассмотрим несколько конкретных примеров использования имен диапазонов.
Рис. 2.10. Диалоговое окно Диспетчер имен для файла States.xlsx
Ответы на вопросы
Вернемся к файлу States.xlsx, в котором двухбуквенные аббревиатуры были присвоены как имена диапазонов соответствующим объемам продаж. Для вычисления общего объема продаж в Алабаме, на Аляске, в Аризоне и в Арканзасе можно воспользоваться формулой =СУММ(B6:B9). Однако если указать ячейки B6, B7, B8 и B9, формула будет выглядеть так: =AL+AK+AZ+AR. Последняя запись, конечно, гораздо нагляднее.
В качестве другого примера использования имен диапазонов рассмотрим файл Historicalinvesttemp.xlsx (рис. 2.11), в котором содержится годовая процентная доходность акций, казначейских векселей и облигаций. (На этом рисунке часть строк скрыта; данные заканчиваются в строке 89.)
Выделите диапазон ячеек B1:D89 и на вкладке Формулы (Formulas) в группе Определенные имена (Defined Names) выберите Создать из выделенного (Create from Selection). В этом примере имена диапазона указаны В строке выше (Top row). Диапазон B2:B89 получает имя Акции (Stocks), диапазон C2:C89 — имя Векселя (Tbills) и диапазон D2:D89 — имя Облигации (Bonds10). Затем введите в ячейке =СРЗНАЧ(, но перед тем как вводить диапазон, можно нажать F3, и откроется диалоговое окно Вставка имени (Paste Name), показанное на рис. 2.12.
|
|
| Рис. 2.11. Ретроспективные данные по инвестициям |
Рис. 2.12. Добавление имени диапазона в формулу в диалоговом окне Вставка имени |
Теперь в окне Вставка имени (Paste Name) выберите из списка имя Акции и нажмите OK. После ввода закрывающей скобки в формуле =СРЗНАЧ(Акции) автоматически будет рассчитано среднее значение доходности акций (11,41%). Прелесть этого подхода состоит в в том, что даже не помня точно, где находятся данные, можно работать с данными о доходности акций в любом месте книги!
Было бы упущением с моей стороны не упомянуть о такой интересной возможности Microsoft Excel 2019, как автозавершение формул (AutoComplete). После ввода =СРЗНАЧ(В автоматически появится список диапазонов и функций, имена которых начинаются с В. Для завершения ввода имени диапазона дважды щелкните на имени Векселя, и затем вам останется только ввести закрывающую скобку.
При использовании в формуле имени столбца (в формате A:A, C:C и т.д.) весь столбец обрабатывается в Excel как именованный диапазон. Например, по формуле =СРЗНАЧ(A:A) вычисляется среднее значение всех чисел в столбце A. Использование имени диапазона для целого столбца очень эффективно при частом вводе новых данных в столбец. Например, если столбец A содержит данные о ежемесячных продажах продукта, то новые данные добавляются каждый месяц, и по такой формуле вычисляется актуальное среднее значение ежемесячных продаж. Однако будьте осторожны: если ввести формулу =СРЗНАЧ(A:A) в столбец A, то появится сообщение о циклической ссылке, так как значение в ячейке, содержащей формулу расчета среднего, будет зависеть от ячейки, содержащей среднее значение. Способ разрешения циклических ссылок приведен в главе 11. Аналогично по формуле =СРЗНАЧ(1:1) рассчитывается среднее значение всех чисел в строке 1.
Понять различие вам поможет файл Sheetnames.xlsx. При создании имен с помощью поля Имя (Name) областью действия имен по умолчанию становится Книга. Предположим, что с помощью поля Имя (Name) имя sales присвоено диапазону ячеек E4:E6 на листе Лист3, и эти ячейки содержат числа 1, 2 и 4 соответственно.
Если мы введем формулу =СУММ(sales) на любом листе, то получим 7, так как областью действия созданных в поле Имя (Name) имен является книга. То есть если в любом месте книги указано имя sales (областью действия которого является вся книга), то оно указывает на ячейки E4:E6 на листе Лист3.
Теперь введите числа 4, 5, 6 в ячейки E4:E6 на листе Лист1 и 3, 4, 5 в ячейки E4:E6 на листе Лист2. Откройте окно Диспетчер имен (Name Manager), присвойте имя jam ячейкам E4:E6 на Лист1 и определите область действия этого имени как Лист1. Перейдите на Лист2, откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 и определите область действия этого имени как Лист2. Диалоговое окно Диспетчер имен теперь будет выглядеть как на рис. 2.13.
Рис. 2.13. Имена на уровне листа и на уровне книги в диалоговом окне Диспетчер имен
Что произойдет, если ввести формулу =СУММ(jam) на каждом из трех листов? На листе Лист1 будут просуммированы значения ячеек E4:E6. Так как в этих ячейках содержатся числа 4, 5 и 6, получится 15. На листе Лист2 также будут просуммированы значения ячеек E4:E6, что в сумме даст 3 + 4 + 5 = 12. Однако на листе Лист3 вычисление по формуле =СУММ(jam) вызовет ошибку #ИМЯ?, поскольку на этом листе отсутствует диапазон с именем jam. Если где-либо на листе Лист3 ввести формулу =СУММ(Лист2!jam), Excel распознает имя на уровне листа, которое представляет диапазон ячеек E4:E6 листа Лист2, и в результате получится 3 + 4 + 5 = 12. То есть если вы указываете перед именем диапазона нужное имя листа с восклицательным знаком (!), Excel обращается к диапазону на другом листе.
Рассмотрим файл Applynames.xlsx и рис. 2.14.
Рис. 2.14. Новые имена диапазонов в старых формулах
В ячейке F3 на листе Лист1 указана цена продукта, а в ячейке F4 — потребность в продукте =10000–300*F3. Ячейки F5 и F6 содержат себестоимость единицы продукции и постоянные затраты соответственно. Прибыль вычисляется в ячейке F7 по формуле =F4*(F3–F5)–F6. В диапазон E3:E7 введены новые имена. Выделите диапазон E3:F7, затем присвойте ячейкам следующие имена: ячейке F3 — цена, ячейке F4 — потребность, ячейке F5 — себестоимость, ячейке F6 — затраты и ячейке F7 — прибыль. Для этого на вкладке Формулы (Formulas) используйте Создать из выделенного (Createfrom Selection) и флажок в столбце слева (Left column). Теперь имена созданных диапазонов необходимо отобразить в формулах ячеек F4 и F7. Для этого выделите диапазон, для которого они создаются (в данном случае F4:F7). Затем на вкладке Формулы (Formulas) в группе Определенные имена (Defined Names) нажмите стрелку Присвоить имя (Define Name) и выберите инструмент Применить имена (Apply Names). Выделите в окне имена, которые требуется применить, и нажмите OK. Обратите внимание, что в ячейке F4 теперь находится формула =10000–300*цена, а в ячейке F7 формула =потребность*(цена–себестоимость)–затраты, что и требовалось.
Если вам нужно применить имена диапазонов ко всему листу, выделите его кнопкой Выделить все (Select All) на пересечении заголовков столбцов и строк.
Нажмите F3. Откроется окно Вставка имени (Paste Name). Теперь нажмите кнопку Все имена (Paste List) — см. рис. 2.12. На листе, начиная с текущей ячейки, появится список имен диапазонов и соответствующих им ячеек.
Решение этой проблемы ищите в файле Lastyear.xlsx. Как показано на рис. 2.15, требуется вычислить доходы за 2014–2021 гг. с приростом 10% в год, начиная с базового уровня $300 млн в 2014 г.
В поле Имя (Name) присвойте ячейке B3 имя прирост. А теперь самое интересное! Переместите курсор в ячейку B7 и на вкладке Формулы (Formulas) в группе Определенныеимена (Defined Names) выберите Присвоить имя (Define Name) — откроется диалоговое окно Редактировать имя (Edit Name). Введите в него данные, как показано на рис. 2.16; на рисунке видно, как, при положении курсора в ячейке B7, мы можем создать имя предыдущий_год, которое всегда будет ссылаться на ячейку ровно на одну строку выше текущей.
|
|
| Рис. 2.15. Создание имени диапазона для предыдущего года |
Рис. 2.16. Создание имени диапазона для подсчета продаж предыдущего года |
Поскольку мы сейчас на ячейке B7, Excel интерпретирует имя диапазона как указывающее на ячейку, находящуюся над текущей ячейкой. Конечно, это не сработает, если в ссылке на ячейку B6 останется знак доллара. Теперь если мы введем в ячейку B7 формулу =предыдущий_год*(1+прирост) и скопируем ее в диапазон B8:B13, каждая ячейка будет содержать нужную нам формулу, по которой содержимое ячейки, находящейся над активной, будет умножаться на 1,1.
Как показано на рис. 2.17 (см. файл Namedrows.xlsx), строка 12 содержит данные о почасовой оплате по дням недели, а строка 13 — количество отработанных часов за каждый день.
Рис. 2.17. Использование имен диапазонов для ссылки на различные строки
Выберите строку 12 (щелкнув на 12) и в поле Имя (Name) введите имя почасовая_оплата. Выберите строку 13 и введите в поле Имя (Name) имя часы. Если теперь в ячейку F14 ввести формулу =почасовая_оплата*часы и скопировать эту формулу в диапазон G14:L14, то в каждом столбце появится произведение двух множителей — почасовой оплаты и отработанных часов.
Замечания
• В Excel нельзя использовать буквы r и c в качестве имен диапазонов.
• Если вы используете Создать из выделенного (Create from Selection), пробелы в созданном имени автоматически будут заменены на подчеркивание (_). Например, имя Product1 будет создано как Product_1.
• Имена диапазонов не могут начинаться с цифр или выглядеть как ссылка на ячейку. Например, нельзя использовать имена 3Q и A4. Поскольку Excel поддерживает более 16 000 столбцов, такие имена, как cat1, являются недопустимыми, поскольку существует ячейка CAT1. Если вы попробуете присвоить ячейке имя CAT1, появится сообщение о том, что вы ввели недопустимое имя. Возможно, самый наилучший вариант — назвать ячейку cat1_.
• Единственные символы, которые можно использовать в именах диапазонов, это точка (.) и подчеркивание (_).
Задания
1. Файл Stock.xlsx содержит данные о ежемесячной доходности акций General Motors и Microsoft. Присвойте имена диапазонам, содержащим ежемесячную доходность для каждой акции, и вычислите среднемесячную доходность каждой акции.
2. Создайте новую пустую книгу и присвойте имя Red диапазону, содержащему ячейки A1:B3 и A6:B8.
3. Если задать широту и долготу любых двух городов, в файле Citydistances.xlsx будет вычислено расстояние между этими двумя городами. Определите имена диапазонов для широты и долготы каждого города и проверьте, чтобы эти имена отображались в формуле для расчета расстояния.
4. Файл Sharedata.xlsx содержит количество акций (shares) для каждого вида акций и цену одной акции (price). Вычислите стоимость акций каждого вида по формуле =shares*price.
5. Создайте имя диапазона для расчета среднего значения продаж за последние пять лет. Предполагается, что годовые продажи перечислены в единственном столбце.
Глава 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 перечислены количество и цена, по которой магазин хозтоваров закупил различные товары. Определите общую стоимость закупок магазина хозтоваров.
Глава 4. Функция ИНДЕКС
Обсуждаемые вопросы
• У меня есть список расстояний между различными городами США. Как должна выглядеть функция, возвращающая расстояние между Сиэтлом и Майами?
• Можно ли создать формулу со ссылкой на весь столбец, содержащий сведения о расстоянии между Сиэтлом и другими городами?
Синтаксис функции ИНДЕКС
Функция ИНДЕКС(INDEX) возвращает содержимое ячейки в указанной строке и указанном столбце внутри массива чисел. Наиболее часто для функции ИНДЕКС используется следующий синтаксис:
ИНДЕКС(массив;номер_строки;номер_столбца)
Например, формула =ИНДЕКС(A1:D12;2;3) возвращает содержимое ячейки во второй строке третьего столбца массива A1:D12. Это ячейка C2.
Ответы на вопросы
В файле Index.xlsx (рис. 4.1) содержатся данные о расстоянии между восемью городами США. Диапазону C10:J17 с этими данными присвоено имя Distances.
Допустим, вы хотите ввести в ячейку расстояние между Бостоном и Денвером. Поскольку расстояния от Бостона до других городов перечислены в первой строке массива Distances, а расстояния до Денвера указаны в четвертом столбце массива, формула выглядит так: =ИНДЕКС(distances;1;4). В результате Бостон и Денвер разделяет 1991 миля. Аналогично расстояние (гораздо более значительное) между Сиэтлом и Майами можно узнать по формуле =ИНДЕКС(distances;6;8). Сиэтл и Майами разделяют 3389 миль.
Рис. 4.1. Использование функции ИНДЕКС для определения расстояния между городами
Представьте, что футбольная команда из Сиэтла отправляется в турне, в котором они проводят игры в Финиксе, Лос-Анджелесе, Денвере, Далласе и Чикаго. В конце выездной серии игр команда возвращается в Сиэтл. Легко ли подсчитать, сколько миль они проедут в течение турне? Как показано на рис. 4.2, следует просто перечислить города в порядке посещения (8-7-5-4-3-2-8), начиная и заканчивая Сиэтлом, и скопировать формулу =ИНДЕКС(distances;C21;C22) в ячейки с D21 по D26. Формула в ячейке D21 определяет расстояние между Сиэтлом и Финиксом (город номер 7), формула в ячейке D22 — расстояние между Финиксом и Лос-Анджелесом и т.д. Всего команда проедет 7112 миль. И просто забавы ради покажите с помощью функции ИНДЕКС, что команда «Майами Хит» путешествует за сезон больше любой другой команды НБА.
Рис. 4.2. Расстояния для турне футбольной команды из Сиэтла
Функция ИНДЕКС позволяет сослаться на всю строку или весь столбец какого-либо массива. Если установить значение 0 для номера строки, то функция ИНДЕКС будет ссылаться на указанный столбец. А если установить 0 для номера столбца, то функция ИНДЕКС ссылается на указанную строку. Предположим, необходимо определить суммарное расстояние между всеми перечисленными городами и Сиэтлом. Можно ввести любую из следующих формул:
=СУММ(ИНДЕКС(distances;8;0))
=СУММ(ИНДЕКС(distances;0;8))
В первой формуле суммируются числа в восьмой строке (строка 17) массива Distances; во второй формуле суммируются числа в восьмом столбце (столбец J) массива Distances. И в том, и в другом случае суммарное расстояние от Сиэтла до других городов и от этих городов до Сиэтла составляет 15 221 миль (см. рис. 4.1).
Задания
1. С помощью функции ИНДЕКС определите расстояние между Лос-Анджелесом и Финиксом, а также расстояние между Денвером и Майами.
2. С помощью функции ИНДЕКС вычислите суммарное расстояние от Далласа до других городов, перечисленных на рис. 4.1.
3. Джерри Джонс с командой «Далласские Ковбои» отправляются в путешествие на машине в Чикаго, Денвер, Лос-Анджелес, Финикс и Сиэтл. Сколько миль им предстоит проехать?
4. В файле Product.xlsx содержатся данные о продаже шести продуктов по месяцам. С помощью функции ИНДЕКС покажите продажи продукта 2 за март. Используйте функцию ИНДЕКС для подсчета общего объема продаж за апрель.
5. В файле Nbadistances.xlsx показаны расстояния между любыми двумя спортивными аренами НБА. Допустим, вы находитесь в Атланте и вам нужно посетить арены в указанном порядке и затем вернуться в Атланту. Какое расстояние вы проедете?
6. Используйте функцию ИНДЕКС для решения задания 10 из главы 3. Вернемся к этому заданию: в файле Employees.xlsx вы найдете рейтинг (по шкале от 0 до 10), присвоенный каждым из 35 сотрудников трем заданиям. В файле также указаны задания, выданные каждому сотруднику. С помощью формулы определите рейтинг, который каждый сотрудник присвоил назначенному ему заданию.
