Система и Обновление 

Вычисление формулы в excel. Расчет NPV в Excel (пример). Относительные и абсолютные ссылки

Часть 18: Intel HD Graphics 4000 в разном окружении и влияние последнего на производительность первого

Процессоры на базе микроархитектуры Ivy Bridge появились еще год назад, так что все, кто хоть немного следит за этой темой, знают: как называется старшее видеоядро, встроенное в настольные Core i7. Правильно - Intel HD Graphics 4000. А если спуститься чуть ниже в табели о рангах где-то так до уровня Core i3, то что мы там найдем? В большинстве моделей Intel HD Graphics 2500, но вот в i3-3225 и недавно анонсированном 3245 - все тот же HDG 4000. В ноутбучных же моделях - тоже он, причем во всех поголовно (за исключением Celeron и Pentium, считающихся отдельно от категорий Core): от экстремального i7-3940XM (четыре ядра с частотой до 3,9 ГГц, TDP 55 Вт), до планшетного i3-3229Y (два ядра с частотой 1,4 ГГц, TDP 13 Вт). Но одинаковое ли это видеоядро? В случае дискретных видеокарт вопрос бы лишен смысла: таковую можно установить в компьютер с любым процессором (по крайней мере, теоретически). С интегрированным же решением все сложнее. Во-первых, даже при беглом взгляде заметна разница в максимальной частоте работы GPU, причем диапазон крайне широк - от 850 МГц (как раз i3-3229Y) до 1,35 ГГц (i7-3940XM), т. е. различается более чем в полтора раза. Во-вторых, речь идет не о каких-то фиксированных частотах - еще в первом поколении Core GPU мобильных процессоров начали использовать технологию Turbo Boost, причем она же применяется и для процессорных ядер. К чему это приводит? Частота и тех, и других меняется динамически, причем зависит как от нагрузки на CPU и GPU, так и от того, в какой теплопакет в конечном итоге нужно «уложиться». В общем, заранее все непредсказуемо, но бытует предположение, что мобильная графика, пусть и называется так же, как настольная, но работает медленнее.

Одной лишь частотой GPU разнобой в конечных системах не ограничивается. Даже на рынке дискретных видеокарт начального уровня их конечные характеристики отданы на откуп производителям, и разработчиком самого видеопроцессора никак не контролируются. Расхождение с официальными ТТХ может быть приличным, что мы недавно наблюдали : четыре(!) из пяти видеокарт Palit несколько (это мягко говоря) отличались от того, что было задумано NVIDIA. Причем несложно заметить, что основные отличия касались даже не частот чипа, а системы памяти. Однако такое вполне возможно и в случае интегрированной графики, тем более что в данном случае память редко бывает распаянной на плате. Соответственно, возможны варианты. Например, «официальная» DDR3-1600 или более медленная DDR-1333 - какие модули производитель (или пользователь) решит использовать, такие и будут. Но это, по крайней мере, как-то поддается ручной настройке, а вот если производитель решит установить всего один слот SO-DIMM (чаще всего таким «грешат» недорогие модели ультрабуков, однако не только они), получим совсем другой уровень производительности графического ядра, несмотря на то, что в характеристиках компьютера все равно будет указано «Intel HD Graphics 4000».

Можно ли протестировать все варианты и дать однозначный ответ: что каждый из них собой представляет? Можно, но сложно - количество возможных конфигураций конечно, но велико. Да и не слишком интересно этим заниматься: давно известно, что HDG 4000 даже в «лучшем виде» не является полноценным игровым решением, а вот для решения большинства прочих задач, как правило, достаточно и более старых и слабых GPU - вплоть до HD Graphics процессоров Celeron на ядре Sandy Bridge. C другой стороны, можно попробовать оценить примерный диапазон, куда должно попадать большинство решений - это уже не так сложно. Да и в процессе самых разных тестирований нами некоторый набор полезной информации накоплен. Во всяком случае, получилось так, что за последнее время с использованием одной и той же версии драйверов (что в этом случае актуально) нами было протестировано для разных целей пять различных конфигураций компьютеров, имеющих как раз искомую графическую подсистему. Таким образом, в данной статье мы просто соберем результаты вместе и попробуем оценить влияние разных факторов на производительность графического ядра Intel HD Graphics 4000.

Конфигурация тестовых стендов

Диапазон потенциально-возможных тактовых частот мы уже указали выше - от 850 МГц в процессорах Y-серии до 1350 МГц в Core i7 Extreme Mobile. Таким образом, наиболее правильным с точки зрения теории подходом было бы взять две системы: на Core i3-3229Y (ниже некуда) и Core i7-3940XM (выше не бывает) и протестировать их с разными конфигурациями памяти - как минимум, один и два канала, а как максимум еще и с разными частотами. Что на практике неосуществимо. Во-первых, найти что-нибудь на Y-процессоре все еще сложно: такие модели появились совсем недавно, так что большинство планшетов в торговых сетях комплектуются более привычными U или даже М Core. Во-вторых, в поисках большого смысла все равно нет: конструкция планшета не предполагает под собой гибкого конфигурирования системы памяти - здесь как раз можно и «нарваться» на распаянные на плате модули памяти и/или неустранимую одноканальность. В-третьих, и на верхнем крае не все гладко - топовые ноутбуки описанных выше проблем лишены, однако процессоры семейств что ХМ, что QM (где максимальная частота графики составляет 1,3 ГГц) как правило, встречаются в продаже исключительно в паре с дискретными видеокартами, которые еще и не всегда можно отключить. C другой стороны, это приводит и к тому, что крайние варианты тестировать просто не нужно - раз вероятность встречи с ними на практике нулевая или (в случае Y) все равно никаких возможностей выбора нет.

Процессор Core i3-3217U Core i5-3317U Core i7-3517U Core i7-3770S Core i7-3770K Core i5-3570S
Название ядра Ivy Bridge DC Ivy Bridge DC Ivy Bridge DC Ivy Bridge QC Ivy Bridge QC Ivy Bridge QC
Кол-во ядер/потоков вычисления 2/4 2/4 2/4 4/8 4/8 4/4
Частота ядра (std/max), ГГц 1,8 1,7/2,6 1,9/3,0 3,1/3,9 3,5/3,9 3,1/3,8
Кэш L3, МиБ 3 3 4 8 8 6
Оперативная память 2×DDR3-1333 1×DDR3-1333 2×DDR3-1600 2×DDR3-1333 2×DDR3-1600 2×DDR3-1333
Частота видео (std/max), МГц 350/1050 350/1050 350/1150 650/1150 650/1150 650/1150
TDP, Вт 17 17 17 65 77 65

А вот диапазон 1,05-1,15 ГГц, напротив, крайне интересен тем, что в него укладывается большинство возможных вариантов. Несложно заметить, что три из пяти конфигураций нами уже тестировались - сегодня просто относящиеся к видео результаты будут «развернуты». И дополнены еще двумя реализациями - в процессорах Core i7-3770S и i7-3770K. Тактовая частота видеоядра - типичные для многих Core i7 1,15 ГГц, но две разные частоты памяти. Плюс огромный разброс в плане процессорной производительности - посмотрим: как может на результаты графики повлиять она. И для сравнения мы добавили результаты одного процессора с HDG 2500, но мощной процессорной частью - вдруг окажется, что ультрамобильные решения, несмотря на топовую (формально) графику все равно существенно медленнее. При равенстве-то процессорной части такого, разумеется, не наблюдается , а вот при такой разнице - все может быть.

И немаловажным моментом является разный уровень TDP тестируемых процессоров, благо пять из шести поддерживают технологию Turbo Boost для процессорных ядер и все - для GPU. Почему это важно? Можно вспомнить, что в наших тестах энергопотребления подача нагрузки на GPU увеличивала его для Core i7-3770K на 17 Вт. Естественно, многое зависит от конкретного экземпляра процессора, тем более, что разные серии подвергаются по этому параметру отбору разной степени жесткости - там же мы видели и 20 Вт от HDG 2500 в бюджетном i5-3450. Но сам по себе порядок величины понятен и, в общем-то, не мал - двухъядерные процессоры U-серии ограничены теми же 17 Вт на весь процессор . Да и 12 Вт официальной разницы между 3770S и 3770К тоже обязаны сказаться на работе Turbo Boost при использовании процессора «целиком», а, значит, и на производительности.

Aliens vs. Predator

Как мы уже не раз писали, никакая интегрированная графика эту игру в таком режиме не вытягивает, так что получаем чистый стресс-тест видеоядра, работающего на пределе своих возможностей. Причем ограничителем этих возможностей может оказаться все, что угодно: очень показательно равенство результатов Core i3-3217U и i7-3517U - несмотря на потенциальные отличия, обе модели «уперлись» в равное TDP. Но хорошо прослеживаются два качественных эффекта - во-первых, одноканальная память даже для процессоров U-семейства смерти подобна (что это верно для топовых моделей, мы уже убеждались ранее), а во-вторых, даже в таком режиме HDG 4000 все равно быстрее, чем 2500.

В низкокачественном режиме можно уже даже попробовать и поиграть, причем на любом из испытуемых. Но по-разному: низкочастотный двухъядерный процессор с одноканальной DDR3-1333, но с HDG 4000, как оказалось, подходит для этого практически в той же степени, что и одна из старших настольных моделей с HDG 2500! Несмотря на то, что в таком режиме и процессору находится работа - недаром на первых местах два четырехъядерных Core i7. Вот между ними разница уже относительно невелика, несмотря на то, что одна модель - вообще топовая и работающая с более быстрой памятью, а вторая - энергоэффективная. 3217U и 3517U намного медленнее, хотя и в их случае некоторый запас производительности, способный позволить чуть-чуть улучшить качество картинки, есть.

Batman: Arkham Asylum GOTY Edition

Относительно старый и «легкий» графический движок «нагружает» GPU в меньшей степени, зато имеет повышенные требования к процессорной составляющей благодаря хорошей многопоточной оптимизации. В результате настольные Core i7 уже «вытягивают» качественный режим, а ультрамобильные процессоры лишь близки к этому уровню. Но очень близки, так что при небольшом снижении качества могут выйти и на «играбельный» уровень. Если, конечно, не «зажимать» систему памяти - в одноканальном режиме HDG 4000 низводится почти до уровня 2500. Но, кстати, не ниже - i5-3570S обогнал i5-3317U только за счет «полноценных» четырех ядер на более высокой тактовой частоте и вдвое большего объема кэш-памяти третьего уровня.

При минимальном качестве все превращается в соревнование процессоров. Что тут стоит отметить, так это то, что подобные настройки, как видим, до сих пор нельзя назвать совсем уж неактуальными - это для топовых процессоров с интегрированной графикой частота кадров начинает «зашкаливать» за порог достаточности, но ведь тестировать приходится не только их. На моделях для неттопов и ультрабуков же FPS высокий, однако не сказать, чтоб «избыточный».

Crysis: Warhead x64

Очередной стресс-тест, где хорошо заметна, во-первых, полная некомпетентность что систем с одноканальной памятью, что HDG 2500, а во-вторых, что процессорная составляющая даже в таких условиях все равно имеет значение, сказываясь на итоговой производительности. С другой стороны, в первую очередь, все же, GPU, а потом все остальное.

В том числе - и в потенциально пригодных для практического использования (если, конечно, кому-то составляет удовольствие любоваться на такую картинку) видеорежимах. Во всяком случае, Core i7-3517U таки сумел обогнать Core i5-3570S благодаря преимуществу в графической составляющей, несмотря на принципиально-разную процессорную.

F1 2010

Как мы уже не раз писали, одинаковая частота кадров в этой игре не значит ничего, если она равна 12,5 FPS - особенности игрового движка, пытающегося удерживать ее на таком уровне, отбрасыванием несущественного (по его мнению).

В низком качестве уже иногда можно поиграть на HDG 4000, однако, как видим, для этого нужен как минимум Core i7-3517U (в своем классе далеко не худший, мягко выражаясь, и не дешевый), причем снабженный двухканальной памятью с частотой 1600 МГц. Невыполнение любого из этих условий чревато последствиями. Превышение - изменит картину в меньшей степени, чем размер превышения:)

Far Cry 2

Быстродействия HDG 4000 по-прежнему не хватает и на эту старую игру (что давно не новость), но в меньшей степени, нежели для Crysis или AvP, конечно. Немудрено, что производительность старшего и младшего из протестированных процессоров различается в полтора раза. С другой стороны, с точки зрения житейской мудрости мы бы не удивились и большей разнице - все-таки слишком сильно различаются CPU-части. Можно даже сказать, принципиально и по всем параметрам.

А в режиме минимального качества как раз она выходит на передний план. И самым любопытным результатом является то, что Core i3-3217U даже в этом случае не смог дотянуться до порога комфортности. Т. е. эта игра почти пятилетней давности до сих пор ни в коем виде не поддается не только Atom или Brazos, но и вообще очень многим платформам повышенной экономичности. И неважно - с интегрированным ли видео или с любой дискреткой: недостаточно производительности и самой процессорной части. Так что прогресс - прогрессом, а определенный минимум системных требований обеспечивать нужно. С чем, как видим, и старшие CULV-процессоры справляются без особого запаса прочности, а младшие - не справляются вообще (интересно будет посмотреть, как с этим дела обстоят у Kabini и младших Haswell). В общем, «свеженький» планшет или бюджетный ультрабук вовсе не обязательно позволит играть даже в очень старые игры и даже «на минималке».

Metro 2033

Возвращаемся к истокам в виде первой диаграммы - понятно, что ни одного из испытуемых не достаточно для качественного режима этой игры, причем принципиально недостаточно. Но влияние ТТХ на производительность видно очень хорошо, так что мы не будем расписывать все подробно - несложно сделать все выводы и самостоятельно.

Метро 2033 появилась на полтора года позже, чем FC2, так что и минимальные требования к оборудованию у игры выше. Справедливости ради - и сам по себе режим «плинтусного» качества имеет куда более высокое качество:) Минимум для него - Core i3-3225, т. е. чтобы хоть как-то поиграть в эту игру, нам нужен процессор с частотой выше 3 ГГц и HDG 4000, причем оба условия являются существенными. HDG 2500 игру «не вытянет» даже с такими настройками, независимо от процессора. А слабенькие модели с любой графикой с ней не справятся именно потому, что слабенькие.

О последнем советуем задуматься многим покупателям ноутбуков;) Во-первых, в свете данных тенденций несколько странно начинают выглядеть попытки некоторых производителей комплектовать свою продукцию на CULV-процессорах дискретными видеокартами. В частности, нам встречались модели на Core i3-3217U в паре с GeForce GT 740M. Последняя видеокарта - очередной пример переименований и оптимизаций, поскольку представляет собой практически все тот же давно знакомый многим 640М, но с немного увеличенными частотами. Не бог весть что, конечно, но потенциально в пару раз быстрее, нежели тот же HDG 4000. Однако, как видим, «процессоронезависимость» игр имеет свой предел, особенно когда речь идет о более-менее современных проектах, т. е. вот для Metro 2033 уже мало низковольтных двухъядерных моделей. Таким образом, конфигурация, подобная указанной, позволит пользователю, разве что, увеличить качество картинки в старых играх, но не поиграть (хоть как-то) в новые - согласитесь, это не то достижение, ради которого есть смысл платить за дискретную графику.

Вторая проблема - из той же области: компания AMD не устает повторять что, хоть у ее APU и ниже производительность процессорной части, зато графика мощнее, чем у Intel. Как видим, всему есть пределы - в том числе, и слабой зависимости результатов от процессора. А тут еще и партнеры масла в огонь подливают, добавляя к какому-нибудь A8-4555M (которому хотя бы встроенный GPU прокормить) дискретную видеокарту на чем-нибудь типа Radeon HD 7550M/8550M. Спору нет - Dual Graphics иногда является единственным способом повысить производительность графической подсистемы, однако это актуально только тогда, когда именно ее не хватает. Как видим, в низкопотребляющем сегменте возможно не только такое.

Сводные результаты

Попробуем оценить ситуацию в общем и целом, а также посмотреть не только на игры, для чего воспользуемся диаграммами со средними результатами по группе тестов/приложений (детально с полной методикой тестирования вы можете ознакомиться в отдельной статье). Результаты на диаграммах приведены в баллах, за 100 баллов в данной статье принята производительность Core i3-3217U как самого медленного из протестированной четверки процессоров. Тем, кто интересуется более подробной информацией, опять-таки традиционно предлагается скачать таблицу в формате Microsoft Excel , в которой все результаты приведены как в преобразованном в баллы, так и в «натуральном» виде.

Итак, начнем с игр. Сразу видно, что одноканальный режим работы памяти мгновенно отбрасывает HDG 4000 на уровень 2500 и прочих подобных решений, так что для практического использования не слишком актуален. В нормальных же условиях разница результатов составляет 33%. С одной стороны - много, с другой - тут все разное. Даже TDP в 4,5 раза отличается. А вот если такой свободы не давать, и память типа DDR3-1333 одинаковую использовать, то и 15% не набирается. Что легко объяснимо - все-таки само по себе видеоядро одинаковое (с поправкой на влияние теплопакета на его реальную тактовую частоту), а с учетом его мощности, тяжелые игровые приложения являются стресс-тестом именно его в первую очередь.

Но на практике, как мы уже видели, в таковых условиях частота кадров практически повсеместно слишком низка для использования, так что более актуальными являются режимы с сниженным качеством графики. Для очень многих решений - сниженным до минимума: этот режим слишком легкий для топовых решений, но CULV-процессоры даже в нем, как видим, не всегда справляются. И вот тут-то уже зависимость результатов от процессорной части видна невооруженным глазом, так что 33% превращаются в 128% - комментарии излишни. Причем, отметим, «нормальный настольный» процессор с HDG 2500 обходит даже CULV Core i7 (3517U, конечно, младшая модель, однако и старший 3687U отличается только повышенной на 10% максимальной тактовой частотой, чего вполне может и не хватить), однако в полтора раза отстает от «нормального настольного» процессора с HDG 4000.

Если бы эта нагрузка была многопоточной, скорее всего, получили бы мы разброс результатов как в предыдущем случае, а так «всего» 1,87 раза. Но расклад внутри - иной: разницы между HDG 2500 и 4000 практически нет. Немудрено и что режим работы памяти сказывается, но слабо - более высокая тактовая частота процессора эту разницу с лихвой перекрывает.

Во времена GMA и первых версий HDG эти результаты тоже зависели от видеоядра, однако теперь, как видим, перестали. Что ж - мы учтем это при разработке следующих версий тестовых методик:)

Итого

Итак, как и следовало ожидать, мы подтвердили зависимость производительности интегрированных графических решений от процессоров, в которые они интегрированы. Впрочем, заметим, не всегда такую уж сильную. Как и следовало ожидать, когда нагрузка ложится именно на GPU, большой разброс результатов можно обнаружить лишь при сравнении процессоров с принципиально разным теплопакетом, поскольку он сказывается и на частотах графического ядра. Но подобные режимы гарантированно оказываются слишком «тяжелыми» не только для IGP, но и для младших моделей дискретных видеокарт, так что для того, чтобы поиграть на них на практике (а не просто посмотреть слайд-шоу), приходится снижать качество картинки, т. е. уменьшать нагрузку на GPU и увеличивать - на CPU. Пока последние относятся к одному классу, определяющей продолжает оставаться мощность собственно графического ядра (что мы уже наблюдали на примере настольных решений , где пара высокочастотных ядер и запас по TDP позволяли развернуться тому же HDG 4000 в полную меру своих слабых сил и в паре с отличающимися процессорами), но вот ожидать одного и того же уровня производительности от ультрабучного и настольного процессоров уже не стоит. В принципе, сложно было бы предполагать обратное, однако в точности убедиться в том, что положение дел именно таково, никогда не лишне. Любовь одинаково именовать похожие архитектурно, но разные по производительности решения началась, конечно, не с Intel, однако в большинстве случаев производители все же хоть как-то намекают на наличие разницы. Да вот и сама компания такой же практики придерживается в системе наименования процессоров - давая им непересекающиеся номера и не забывая в конце дописать буковку «М» или «U», иногда резко сказывающуюся и на номере семейства (избитый пример: подавляющее большинство настольных Core i5 относится к четырехъядерным процессорам, но все Core i5-M - лишь двухъядерные). А с графикой даже такой ясности нет: можно судить лишь по косвенным признакам - типа названия процессора, куда она встроена.

Есть ли надежды на прекращение получающегося бардака в будущем? Может быть, в отдаленном, но точно не в ближайшем поколении процессоров. То есть мы, конечно, не сомневаемся, что Iris 5100 - более производительный GPU, нежели HDG 4600. Однако позволит ли это играть на Core i7-4558U (двухъядерный SoC с TDP 15 Вт) с бо́льшим комфортом, нежели на Core i7-4700HQ, не говоря уже о старшем настольном Core i7-4770K (четырехъядерные процессоры, еще и обгоняющие 4558U по тактовой частоте и менее «зажатые» теплопакетом) - вопрос открытый. А уж полное равенство процессоров с называющимся одинаково интегрированным GPU - тем более сомнительно. Однако в точности разобраться с этими вопросами без непосредственного тестирования невозможно, а это уже тема совсем других тестирований.

Формулы в Excel – одно из самых главных достоинств этого редактора. Благодаря им ваши возможности при работе с таблицами увеличиваются в несколько раз и ограничиваются только имеющимися знаниями. Вы сможете сделать всё что угодно. При этом Эксель будет помогать на каждом шагу – практически в любом окне существуют специальные подсказки.

Для создания простой формулы достаточно следовать следующей инструкции:

  1. Сделайте активной любую клетку. Кликните на строку ввода формул. Поставьте знак равенства.

  1. Введите любое выражение. Использовать можно как цифры,

При этом затронутые ячейки всегда подсвечиваются. Это делается для того, чтобы вы не ошиблись с выбором. Визуально увидеть ошибку проще, чем в текстовом виде.

Из чего состоит формула

В качестве примера приведём следующее выражение.

Оно состоит из:

  • символ «=» – с него начинается любая формула;
  • функция «СУММ»;
  • аргумента функции «A1:C1» (в данном случае это массив ячеек с «A1» по «C1»);
  • оператора «+» (сложение);
  • ссылки на ячейку «C1»;
  • оператора «^» (возведение в степень);
  • константы «2».

Использование операторов

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

  • скобки;
  • экспоненты;
  • умножение и деление (в зависимости от последовательности);
  • сложение и вычитание (также в зависимости от последовательности).

Арифметические

К ним относятся:

  • сложение – «+» (плюс);
=2+2
  • отрицание или вычитание – «-» (минус);
=2-2 =-2

Если перед числом поставить «минус», то оно примет отрицательное значение, но по модулю останется точно таким же.

  • умножение – «*»;
=2*2
  • деление «/»;
=2/2
  • процент «%»;
=20%
  • возведение в степень – «^».
=2^2

Операторы сравнения

Данные операторы применяются для сравнения значений. В результате операции возвращается ИСТИНА или ЛОЖЬ. К ним относятся:

  • знак «равенства» – «=»;
=C1=D1
  • знак «больше» – «>»;
=C1>D1
  • знак «меньше» — «<»;
=C1
  • знак «больше или равно» — «>=»;
    • =C1>=D1
    • знак «меньше или равно» — «<=»;
    =C1<=D1
    • знак «не равно» — «<>».
    =C1<>D1

    Оператор объединения текста

    Для этой цели используется специальный символ «&» (амперсанд). При помощи его можно соединить различные фрагменты в одно целое – тот же принцип, что и с функцией «СЦЕПИТЬ». Приведем несколько примеров:

    1. Если вы хотите объединить текст в ячейках, то нужно использовать следующий код.
    =A1&A2&A3
    1. Для того чтобы вставить между ними какой-нибудь символ или букву, нужно использовать следующую конструкцию.
    =A1&»,»&A2&»,»&A3
    1. Объединять можно не только ячейки, но и обычные символы.
    =»Авто»&»мобиль»

    Любой текст, кроме ссылок, необходимо указывать в кавычках. Иначе формула выдаст ошибку.

    Обратите внимание, что кавычки используют именно такие, как на скриншоте.

    Для определения ссылок можно использовать следующие операторы:

    • для того чтобы создать простую ссылку на нужный диапазон ячеек, достаточно указать первую и последнюю клетку этой области, а между ними символ «:»;
    • для объединения ссылок используется знак «;»;
    • если необходимо определить клетки, которые находятся на пересечении нескольких диапазонов, то между ссылками ставится «пробел». В данном случае выведется значение клетки «C7».

    Поскольку только она попадает под определение «пересечения множеств». Именно такое название носит данный оператор (пробел).

    Использование ссылок

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

    Простые ссылки A1

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

    • столбцов – от A до XFD (не больше 16384);
    • строк – от 1 до 1048576.

    Приведем несколько примеров:

    • ячейка на пересечении строки 5 и столбца B – «B5»;
    • диапазон ячеек в столбце B начиная с 5 по 25 строку – «B5:B25»;
    • диапазон ячеек в строке 5 начиная со столбца B до F – «B5:F5»;
    • все ячейки в строке 10 – «10:10»;
    • все ячейки в строках с 10 по 15 – «10:15»;
    • все клетки в столбце B – «B:B»;
    • все клетки в столбцах с B по K – «B:K»;
    • диапазон ячеек с B2 по F5 – «B2-F5».

    Иногда в формулах используется информация с других листов. Работает это следующим образом.

    =СУММ(Лист2!A5:C5)

    На втором листе указаны следующие данные.

    Если в названии листа есть пробел, то в формуле его нужно указывать в одинарных кавычках (апострофы).

    =СУММ("Лист номер 2"!A5:C5)

    Абсолютные и относительные ссылки

    Редактор Эксель работает с тремя видами ссылок:

    • абсолютные;
    • относительные;
    • смешанные.

    Рассмотрим их более внимательно.

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

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

    1. Введём формулу для расчета суммы первой колонки.
    =СУММ(B4:B9)

    1. Нажмите на горячие клавиши Ctrl +C . Для того чтобы перенести формулу на соседнюю клетку, необходимо перейти туда и нажать на Ctrl +V .

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

    1. Теперь посмотрите на новые формулы. Изменение индекса столбца произошло автоматически.

    Если вы хотите, чтобы при переносе формул все ссылки сохранялись (то есть чтобы они не менялись в автоматическом режиме), нужно использовать абсолютные адреса. Они указываются в виде «$B$2».

    =СУММ($B$4:$B$9)

    В итоге мы видим, что изменений никаких не произошло. Во всех столбцах у нас отображается одно и то же число.

    Данный тип адресов используется тогда, когда необходимо зафиксировать только столбец или строку, а не всё одновременно. Использовать можно следующие конструкции:

    • $D1, $F5, $G3 – для фиксации столбцов;
    • D$1, F$5, G$3 – для фиксации строк.

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

    Дело в том, что когда вы скопируете формулу на другую строку, то в ссылках цифры автоматически изменятся на количество клеток от исходного значения. Если использовать смешанные адреса, то всё останется на месте. Делается это следующим образом.

    1. В качестве примера используем следующее выражение.
    =B$4

    1. Перенесем эту формулу в другую ячейку. Желательно не на следующую и на другой строке. Теперь вы видим, что новое выражение содержит ту же строчку (4), но другую букву, поскольку только она была относительной.

    Трёхмерные ссылки

    Под понятие «трёхмерные» попадают те адреса, в которых указывается диапазон листов. Пример формулы выглядит следующим образом.

    =СУММ(Лист1:Лист4!A5)

    В данном случае результат будет соответствовать сумме всех ячеек «A5» на всех листах, начиная с 1 по 4. При составлении таких выражений необходимо придерживаться следующих условий:

    • в массивах нельзя использовать подобные ссылки;
    • трехмерные выражения запрещается использовать там, где есть пересечение ячеек (например, оператор «пробел»);
    • при создании формул с трехмерными адресами можно использовать следующие функции: СРЗНАЧ, СТАНДОТКЛОНА, СТАНДОТКЛОН.В, СРЗНАЧА, СТАНДОТКЛОНПА, СТАНДОТКЛОН.Г, СУММ, СЧЁТЗ, СЧЁТ, МИН, МАКС, МИНА, МАКСА, ДИСПР, ПРОИЗВЕД, ДИСППА, ДИСП.В и ДИСПА.

    Если нарушить эти правила, то вы увидите какую-нибудь ошибку.

    Ссылки формата R1C1

    Данный тип ссылок от «A1» отличается тем, что номер задается не только строкам, но и столбцам. Разработчики решили заменить обычный вид на этот вариант для удобства в макросах, но их можно использовать где угодно. Приведем несколько примеров таких адресов:

    • R10C10 – абсолютная ссылка на клетку, которая расположена на десятой строке десятого столбца;
    • R – абсолютная ссылка на текущую (в которой указывается формула) ссылку;
    • R[-2] – относительная ссылка на строчку, которая расположена на две позиции выше этой;
    • R[-3]C – относительная ссылка на клетку, которая расположена на три позиции выше в текущем столбце (где вы решили прописать формулу);
    • RC – относительная ссылка на клетку, которая распложена на пять клеток правее и пять строк ниже текущей.

    Использование имён

    Программа Excel для обозначения диапазонов ячеек, одиночных ячеек, таблиц (обычные и сводные), констант и выражений позволяет создавать свои уникальные имена. При этом для редактора никакой разницы при работе с формулами нет – он понимает всё.

    Имена вы можете использовать для умножения, деления, сложения, вычитания, расчета процентов, коэффициентов, отклонения, округления, НДС, ипотеки, кредита, сметы, табелей, различных бланков, скидки, зарплаты, стажа, аннуитетного платежа, работы с формулами «ВПР», «ВСД», «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» и так далее. То есть можете делать, что угодно.

    Главным условием можно назвать только одно – вы должны заранее определить это имя. Иначе Эксель о нём ничего знать не будет. Делается это следующим образом.

    1. Выделите какой-нибудь столбец.
    2. Вызовите контекстное меню.
    3. Выберите пункт «Присвоить имя».

    1. Укажите желаемое имя этого объекта. При этом нужно придерживаться следующих правил.

    1. Для сохранения нажмите на кнопку «OK».

    Точно так же можно присвоить имя какой-нибудь ячейке, тексту или числу.

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

    А если попробовать вместо адреса «D4:D9» вставить наше имя, то вы увидите подсказку. Достаточно написать несколько знаков, и вы увидите, что подходит (из базы имён) больше всего.

    В нашем случае всё просто – «столбец_3». А представьте, что у вас таких имён будет большое множество. Все наизусть вы запомнить не сможете.

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

    В редакторе Excel вставить функцию можно несколькими способами:

    • вручную;
    • при помощи панели инструментов;
    • при помощи окна «Вставка функции».

    Рассмотрим каждый метод более внимательно.

    В этом случае всё просто – вы при помощи рук, собственных знаний и умений вводите формулы в специальной строке или прямо в ячейке.

    Если же у вас нет рабочего опыта в этой области, то лучше поначалу использовать более облегченные методы.

    В этом случае необходимо:

    1. Перейти на вкладку «Формулы».
    2. Кликнуть на какую-нибудь библиотеку.
    3. Выбрать нужную функцию.

    1. Сразу после этого появится окно «Аргументы и функции» с уже выбранной функцией. Вам остается только проставить аргументы и сохранить формулу при помощи кнопки «OK».

    Мастер подстановки

    Применить его можно следующим образом:

    1. Сделайте активной любую ячейку.
    2. Нажмите на иконку «Fx» или выполните сочетание клавиш SHIFT +F3 .

    1. Сразу после этого откроется окно «Вставка функции».
    2. Здесь вы увидите большой список различных функций, отсортированных по категориям. Кроме этого, можно воспользоваться поиском, если вы не можете найти нужный пункт.

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

    1. Выберите какую-нибудь функцию из предложенного списка.
    2. Чтобы продолжить, нужно кликнуть на кнопку «OK».

    1. Затем вас попросят указать «Аргументы и функции». Сделать это можно вручную либо просто выделить нужный диапазон ячеек.
    2. Для того чтобы применить все настройки, нужно нажать на кнопку «OK».

    1. В результате этого мы увидим цифру 6, хотя это было и так понятно, поскольку в окне «Аргументы и функции» выводится предварительный результат. Данные пересчитываются моментально при изменении любого из аргументов.

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

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

    Затем придерживайтесь следующей инструкции:

    1. Кликните на первую ячейку. Вызовите окно «Вставка функции». Выберите функцию «Если». Для вставки нажмите на «OK».

    1. Затем нужно будет составить какое-нибудь логическое выражение. Его необходимо записать в первое поле. Например, можно сложить значения трех ячеек в одной строке и проверить, будет ли сумма больше 10. В случае «истины» указываем текст «Больше 10». Для ложного результата – «Меньше 10». Затем для возврата в рабочее пространство нажимаем на «OK».

    1. В итоге мы видим следующее – редактор выдал, что сумма ячеек в третьей строке меньше 10. И это правильно. Значит, наш код работает.
    =ЕСЛИ(СУММ(B3:D3)>10;»Больше 10";»Меньше 10")

    1. Теперь нужно настроить и следующие клетки. В этом случае наша формула просто протягивается дальше. Для этого сначала необходимо навести курсор на правый нижний угол ячейки. После того как изменится курсор, нужно сделать левый клик и скопировать её до самого низа.

    1. В итоге редактор пересчитывает наше выражение для каждой строки.

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

    Сделать это можно несколькими способами: использовать строку формул или специальный мастер. В первом случае всё просто – кликаете в специальное поле и вручную вводите нужные изменения. Но писать там не совсем удобно.

    Единственное, что вы можете сделать, это увеличить поле для ввода. Для этого достаточно кликнуть на указанную иконку или нажать на сочетание клавиш Ctrl +Shift +U .

    Стоит отметить, что это единственный способ, если вы не используете в формуле функции.

    В случае использования функций всё становится намного проще. Для редактирования необходимо следовать следующей инструкции:

    1. Сделайте активной клетку с формулой. Нажмите на иконку «Fx».

    1. После этого появится окно, в котором вы сможете в очень удобном виде изменить нужные вам аргументы функции. Кроме этого, здесь можно узнать, каким именно будет результат пересчета нового выражения.

    1. Для сохранения внесенных изменений нужно использовать кнопку «OK».

    Для того чтобы удалить какое-нибудь выражение, достаточно сделать следующее:

    1. Кликните на любую ячейку.

    1. Нажмите на кнопку Delete или Backspace . В результате этого клетка окажется пустой.

    Добиться точно такого же результата можно и при помощи инструмента «Очистить всё».

    Возможные ошибки при составлении формул в редакторе Excel

    Ниже перечислены самые популярные ошибки, которые допускаются пользователями:

    • в выражении используется огромное количество вложенностей. Их должно быть не более 64;
    • в формулах указываются пути к внешним книгам без полного пути;
    • неправильно расставлены открывающиеся и закрывающиеся скобки. Именно поэтому в редакторе в строке формул все скобки подсвечиваются другим цветом;

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

    • не указываются обязательные аргументы функций. Обратите внимание на то, что необязательные аргументы указываются в квадратных скобках. Всё что без них – необходимо для полноценной работы формулы;

    • неправильно указываются диапазоны ячеек. Для этого необходимо использовать оператор «:» (двоеточие).

    Коды ошибок при работе с формулами

    При работе с формулой вы можете увидеть следующие варианты ошибок:

    • #ЗНАЧ! – данная ошибка показывает, что вы используете неправильный тип данных. Например, вместо числового значения пытаетесь использовать текст. Разумеется, Эксель не сможет вычислить сумму между двумя фразами;
    • #ИМЯ? – подобная ошибка означает, что вы допустили опечатку в написании названия функции. Или же пытаетесь ввести что-то несуществующее. Так делать нельзя. Кроме этого, проблема может быть и в другом. Если вы уверены в имени функции, то попробуйте посмотреть на формулу более внимательно. Возможно, вы забыли какую-нибудь скобку. Кроме этого, нужно учитывать, что текстовые фрагменты указываются в кавычках. Если ничего не помогает, попробуйте составить выражение заново;
    • #ЧИСЛО! – отображение подобного сообщения означает, что у вас какая-то проблема с аргументами или с результатом выполнения формулы. Например, число получилось слишком огромным или наоборот – маленьким;
    • #ДЕЛ/0!– данная ошибка означает, что вы пытаетесь написать выражение, в котором происходит деление на ноль. Excel не может отменить правила математики. Поэтому такие действия здесь также запрещены;
    • #Н/Д! – редактор может показать это сообщение, если какое-нибудь значение недоступно. Например, если вы используете функции ПОИСК, ПОИСКА, ПОИСКПОЗ, и Excel не нашел искомый фрагмент. Или же данных вообще нет и формуле не с чем работать;
    • Если вы пытаетесь что-то посчитать, и программа Excel пишет слово #ССЫЛКА!, значит, в аргументе функции используется неправильный диапазон ячеек;
    • #ПУСТО! – эта ошибка появляется в том случае, если у вас используется несогласующаяся формула с пересекающимися диапазонами. Точнее – если в действительности подобные ячейки отсутствуют (которые оказываются на пересечении двух диапазонов). Довольно часто такая ошибка возникает случайно. Достаточно оставить один пробел в аргументе, и редактор воспримет его как специальный оператор (о нём мы рассказывали ранее).

    При редактировании формулы (ячейки подсвечиваются) вы увидите, что они на самом деле не пересекаются.

    Иногда можно увидеть много символов #, которые полностью заполняют ячейку по ширине. На самом деле тут ошибки нет. Это означает, что вы работаете с числами, которые не помещаются в данную клетку.

    Для того чтобы увидеть содержащееся там значение, достаточно изменить размер столбца.

    Кроме этого, можно использовать форматирование ячеек. Для этого необходимо выполнить несколько простых шагов:

    1. Вызовите контекстное меню. Выберите пункт «Формат ячеек».

    1. Укажите тип «Общий». Для продолжения используйте кнопку «OK».

    Благодаря этому редактор Эксель сможет перевести это число в другой формат, который умещается в данном столбце.

    Примеры использования формул

    Редактор Microsoft Excel позволяет обрабатывать информацию любым удобным для вас способом. Для этого есть все необходимые условия и возможности. Рассмотрим несколько примеров формул по категориям. Так вам будет проще разобраться.

    Для того чтобы оценить математические возможности Экселя, нужно выполнить следующие действия.

    1. Создайте таблицу с какими-нибудь условными данными.

    1. Для того чтобы высчитать сумму, введите следующую формулу. Если хотите прибавить только одно значение, можно использовать оператор сложения («+»).
    =СУММ(B3:C3)
    1. Как ни странно, в редакторе Excel нельзя отнять при помощи функций. Для вычета используется обычный оператор «-». В этом случае код получится следующий.
    =B3-C3
    1. Для того чтобы определить, сколько первое число составляет от второго в процентах, нужно использовать вот такую простую конструкцию. Если вы захотите вычесть несколько значений, то придется прописывать «минус» для каждой ячейки.
    =B3/C3%

    Обратите внимание, что символ процента ставится в конце, а не в начале. Кроме этого, при работе с процентами не нужно дополнительно умножать на 100. Это происходит автоматически.

    1. Для определения среднего значения используйте следующую формулу.
    =СРЗНАЧ(B3:C3)
    1. В результате описанных выше выражений, вы увидите следующий итог.

    1. Для этого увеличим нашу таблицу.

    1. Например, сложим те ячейки, у которых значение больше трёх.
    =СУММЕСЛИ(B3;»>3";B3:C3)
    1. Excel может складывать с учетом сразу нескольких условий. Можно посчитать сумму клеток первого столбца, значение которых больше 2 и меньше 6. И ту же самую формулу можно установить для второй колонки.
    =СУММЕСЛИМН(B3:B9;B3:B9;»>2";B3:B9;»<6") =СУММЕСЛИМН(C3:C9;C3:C9;»>2";C3:C9;»<6")
    1. Также можно посчитать количество элементов, которые удовлетворяют какому-то условию. Например, пусть Эксель посчитает, сколько у нас чисел больше 3.
    =СЧЁТЕСЛИ(B3:B9;»>3") =СЧЁТЕСЛИ(C3:C9;»>3")
    1. Результат всех формул получится следующим.

    Математические функции и графики

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

    В качестве примера попробуем построить графики для экспоненты и какого-нибудь уравнения. Инструкция будет следующей:

    1. Создадим таблицу. В первой графе у нас будет исходное число «X», во второй – функция «EXP», в третьей – указанное соотношение. Можно было бы сделать квадратичное выражение, но тогда бы результирующее значение на фоне экспоненты на графике практически пропало бы.

    1. Для того чтобы преобразовать значение «X», нужно указать следующие формулы.
    =EXP(B4) =B4+5*B4^3/2
    1. Дублируем эти выражения до самого конца. В итоге получаем следующий результат.

    1. Выделяем всю таблицу. Переходим на вкладку «Вставка». Кликаем на инструмент «Рекомендуемые диаграммы».

    1. Выбираем тип «Линия». Для продолжения кликаем на «OK».

    1. Результат получился довольно-таки красивый и аккуратный.

    Как мы и говорили ранее, прирост экспоненты происходит намного быстрее, чем у обычного кубического уравнения.

    Подобным образом можно представить графически любую функцию или математическое выражение.

    Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

    Во всём остальном всё выглядит практически точно так же. В качестве примера, посчитаем сумму нескольких ячеек. Для этого необходимо:

    1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».

    1. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».

    1. Указываем данные в нужном диапазоне. Для того чтобы отобразить результат, нужно нажать на «OK».

    1. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.

    Заключение

    В данном самоучителе мы рассказали обо всем, что связано с формулами в редакторе Excel, – от самого простого до очень сложного. Каждый раздел сопровождался подробными примерами и пояснениями. Это сделано для того, чтобы информация была доступной даже для полных чайников.

    Если у вас что-то не получается, значит, вы допускаете где-то ошибку. Возможно, у вас есть опечатки в выражениях или же указаны неправильные ссылки на ячейки. Главное понять, что всё нужно вбивать очень аккуратно и внимательно. Тем более все функции не на английском, а на русском языке.

    Кроме этого, важно помнить, что формулы должны начинаться с символа «=» (равно). Многие начинающие пользователи забывают про это.

    Файл примеров

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

    Видеоинструкция

    Если наше описание вам не помогло, попробуйте посмотреть приложенное ниже видео, в котором рассказываются основные моменты более детально. Возможно, вы делаете всё правильно, но что-то упускаете из виду. С помощью этого ролика вы должны разобраться со всеми проблемами. Надеемся, что подобные уроки вам помогли. Заглядывайте к нам чаще.

    Строкой формул называется специальная строка, расположенная над заголовками столбцов и предназначенная для ввода и редактирования формул и иной информации. Фрагмент строки формул представлен на рисунке 3.4.

    Рисунок 3.4 - Строка формул

    Строка формул состоит из двух основных частей: адресной строки, которая расположена слева, и строки ввода и отображения информации. На рисунке 3.4 в адресной строке отображается имя последней использованной функции (в данном случае функции вычисления суммы), а в строке ввода и отображения информации - формула «=А1+5».

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

    В табличном редакторе Excel 2007 можно полностью автоматизировать выполнение расчетов, используя для этого тип данных «Формула». Формула - это специальный инструмент Excel 2007, предназначенный для расчетов, вычислений и анализа данных.

    Формула начинается со знака «=», после чего следуют операнды и операторы. Список арифметических операторов приведен в таблице 3.1. Старшинство операций при вычислении формул Excel следующее:

    − операторы связи (выполняется в первую очередь);

    − оператор процент;

    − унарный минус;

    − оператор возведение в степень;

    − операторы умножение и деление;

    − операторы сложение и вычитание (в последнюю очередь). Таблица 3.1 – Символы для обозначения операторов в Excel

    взять процент

    возведение в степень

    Операторы связи

    задание диапазона

    СУММ(А1:В10)

    объединение

    СУММ(А1;А3)

    Скобки в формулах Excel выполняют привычную, с точки зрения алгебры, роль указания приоритета вычисления той или иной части выражения. Например:

    = 10*4+4^2 дает результат 56

    = 10*(4+4^2) дает результат 200

    Особенно внимательно надо расставлять скобки при задании унарного минуса. Например: = -10^2 дает результат 100, а =-(10^2) даст результат -100; - 1^2+1^2 дает результат 2, а 1^2-1^2 даст результат 0.

    Если формула не может быть правильно вычислена, вместо ожидаемого результата Excel выводит в ячейку код ошибки (таблица 3.2).

    Таблица 3.2 – Сообщения об ошибках при вычислении формул

    Код ошибки

    Возможные причины

    В формуле делается попытка деления на нуль (пустые ячейки

    считаются нулями)

    Нет доступного значения

    Не распознается имя, использованное в формуле

    но пересечение двух областей, которые не имеют общих ячеек)

    В функции с числовым аргументом используется неприемле-

    мый аргумент

    Формула неправильно ссылается на ячейку

    Используется недопустимый тип аргумента

    Функции в Excel

    Многие виды расчетов можно также выполнять с помощью специальных встроенных в Excel 2007 функций. Функция - это изначально созданная и заложенная в программу Excel процедура, которая выполняет в определенном порядке вычисления по заданным аргументам.

    В состав каждой функции в обязательном порядке входят следующие элементы: имя или название (примеры имен - СУММ, СРЗНАЧ, СЧЕТ, МАКС и т. д.), а также аргумент (либо несколько аргументов), который задается в круглых скобках сразу после имени функции. Аргументами функций могут быть числа, ссылки, формулы, текст, логические величины и др.. Если аргумен-

    тов у функции несколько, то они задаются через запятую. Если аргументов у функции нет, например, у функции ПИ (), то внутри скобок ничего не задается. Скобки позволяют определить, где начинается и где заканчивается список аргументов. Между названием функции и скобками ничего вставлять нельзя. Поэтому символ возведения функции в степень задается после записи аргумента. Например, SIN(A1)^3. Если правила записи функции нарушены, то Excel выдает сообщение о том, что в формуле имеется ошибка.

    Вводить функции можно как в ручном, так и в автоматическом режиме. В последнем случае используют мастер функций, открываемый кнопкой Вставить функцию , которая расположена на ленте Excel 2007 на вкладкеФормулы .

    Все имеющиеся в программе функции для удобства работы сгруппированы в категории. Выбор категории осуществляется из раскрывающегося списка Категория , при этом в нижней части окна отображается перечень функций, входящих в эту категорию. Если выделить требуемую функцию и нажать кнопку ОК, то откроется о кно (его содержимое зависит от конкретной функции), в котором указываются аргументы функции.

    В инженерных расчетах часто используются тригонометрические функции. Следует иметь в виду, что аргумент тригонометрической функции должен быть задан в радианах. Поэтому, если аргумент задан в градусах, его необходимо перевести в радианы. Это можно реализовать либо через формулу пересчета «=А1*ПИ()/180» (предполагается, что аргумент записан в ячейку с адресом А1), либо с помощью функции РАДИАНЫ(А1).

    Пример . Записать формулу Excel= − для2 + вычисления3 1+e функцииtg 3 (5 2 ) b .

    Предполагая, что значение x задано в градусах и записано в ячейку А1, а значение b в ячейку B1, формула в ячейке Excel будет выглядеть следующим образом:

    =(- (B1^2) + (1+exp(B1))^(1/3)) /TAN(5*РАДИАНЫ(А1)^2) ^3

    Относительные и абсолютные адреса ячеек

    Для записи в формулы Excel констант следует использовать абсолютную адресацию ячеек. В этом случае при копировании формулы в другую ячейку адрес ячейки с константой не изменится. Чтобы изменить в формуле относительный адрес ячейки В2 на абсолютный $B$2, следует последовательно нажить клавишу F4, либо вручную добавить символы доллара. Существуют также смешанные адреса ячеек (B$2 и $B2). При копировании формулы содержащей

    смешанные адреса меняется только не зафиксированная (знаком $ слева) часть адреса.

    При копировании формулы в соседнюю ячейку по строке в относительном адресе ссылки меняется буквенная составляющая. Например, ссылка А3 заменится ссылкой В3, а смешанный адрес $А1 при копировании вдоль строки не изменится. Соответственно, при копировании формулы в соседнюю ячейку по столбцу в относительном адресе ссылки меняется цифровая составляющая. Например, ссылка А1 заменится ссылкой А2, а смешанный адрес А$1 при копировании вдоль столбца не изменится.

    Построение диаграмм

    В Excel под термином диаграмма понимается любое графическое представление числовых данных. Построение диаграмм производится на основе ряда данных – группы ячеек с данными в пределах одной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.

    Наиболее простой способ построения диаграмм следующий: выделить один или несколько рядов данных, в группе Диаграммы вкладкиВставка ленты Excel выбрать нужный тип диаграммы. Диаграмма будет помещена на текущий лист рабочей книги. При необходимости ее можно перенести на другой лист с помощью командыПереместить диаграмму вкладкиКонструктор Работы с диаграммами. С помощью вкладкиМакет Работы с диаграммами

    можно изменить внешний вид диаграммы: добавить название диаграммы, осей, изменить шрифты и т.п.. При необходимости можно изменить подписи на горизонтальной оси. Для этого в контекстном меню диаграммы следует выбрать ко-

    манду Выбрать данныеи в диалоговом окне Выбор источника данных(рису-

    нок 3.5) изменить подписи горизонтальной оси.

    Рисунок 3.5 – Окно для изменения данных на оси Х

    Добрый день.

    Когда-то, написать самостоятельно формулу в Excel - для меня было чем-то невероятным. И даже, несмотря на то, что часто приходилось работать в этой программе, ничего кроме текста не набивал…

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

    И так, начнем…

    1. Основные операции и азы. Обучение основам Excel.

    Все действия в статье будут показываться в Excel версии 2007г.

    После запуска программы Excel - появляется окно со множеством клеточек - наша таблица. Главная особенность программы в том, что она может считать (как калькулятор) ваши формулы, которые вы напишете. Кстати, добавить формулу можно в каждую ячейку!

    Формула должна начинаться со знака «=». Это обязательное условие. Дальше вы пишите то, что вам нужно посчитать: например, «=2+3» (без кавычек) и нажимаете по клавише Enter - в результате вы увидите, что в ячейке появился результат «5». См. скриншот ниже.

    Важно! Несмотря на то, что в ячейке А1 написано число «5» - оно считается по формуле («=2+3»). Если в соседней ячейке просто текстом написать «5» - то при наведении курсора на эту ячейку - в редакторе формулы (строка сверху, Fx ) - вы увидите простое число «5».

    А теперь представьте, что в ячейку вы можете писать не просто значение 2+3, а номера ячеек, значения которых нужно сложить. Допустим так «=B2+C2».

    Естественно, что в B2 и C2 должны быть какие-нибудь числа, иначе Excel покажет нам в ячейке A1 результат равный 0.

    И еще одно важное замечание…

    Когда вы копируете ячейку, в которой есть формула, например A1 - и вставляете ее в другую ячейку - то копируется не значение «5», а сама формула!

    Причем, формула изменится прямо-пропорционально: т.е. если A1 скопировать в A2 - то формула в ячейке A2 будет равна «=B3+C3». Excel сам меняет автоматически вашу формулу: если A1=B2+C2, то логично, что A2=B3+C3 (все цифры увеличились на 1).

    Результат, кстати, в A2=0, т.к. ячейки B3 и С3 не заданы, а значит равны 0.

    Таким образом можно написать формулу один раз, а затем ее скопировать во все ячейки нужного столбца - и Excel сам произведет расчет в каждой строчки вашей таблицы!

    Если вы не хотите, чтобы B2 и С2 изменялись при копировании и всегда были привязаны к этим ячейкам, то просто добавьте к ним значок «$». Пример ниже.

    Таким образом, куда бы вы ни скопировали ячейку A1 - она всегда будет ссылаться на привязанные ячейки.

    2. Сложение значений в строках (формула СУММ и СУММЕСЛИМН)

    Можно, конечно, каждую ячейку складывать, делая формулу A1+A2+A3 и т.п. Но чтобы так не мучатся, есть в Excel специальная формула, которая сложит все значения в ячейках, которые вы выделите!

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

    Для этого переходим в ячейку, в которой будет показываться результат и пишем формулу: «=СУММ(C2:C5)». См. скриншот ниже.

    В результате все ячейки в выделенном диапазоне будут просуммированы, а вы увидите результат.

    2.1. Сложение с условием (с условиями)

    А теперь представим, что у нас есть определенные условия, т.е. сложить надо не все значения в ячейках (Кг, на складе), а лишь определенные, скажем, с ценой (1 кг.) меньше 100.

    Для этого есть замечательная формула «СУММЕСЛИМН «. Сразу же пример, а затем пояснение каждого символа в формуле.

    =СУММЕСЛИМН(C2:C5 ;B2:B5 ;«<100» ) , где:

    C2:C5 - та колонка (те ячейки), которые будут суммироваться;

    B2:B5 - колонка, по которой будет проверяться условие (т.е. цена, например, менее 100);

    «<100» - само условие, обратите внимание, что условие пишется в кавычках.

    Ничего сложного в этой формуле нет, главное соблюдать соразмерность: C2:C5;B2:B5 - правильно; C2:C6;B2:B5 - неправильно. Т.е. диапазон суммирования и диапазон условий должны быть соразмерны, иначе формула вернет ошибку.

    Важно! Условий для суммы может быть много, т.е. можно проверять не по 1-й колонке, а сразу по 10, задав множество условий.

    3. Подсчет количества строк, удовлетворяющих условиям (формула СЧЁТЕСЛИМН)

    Довольно часто-встречающаяся задача: подсчитать не сумму значений в ячейках, а количество таких ячеек, удовлетворяющих определенным условиям. Иногда, условий очень много.

    И так… начнем.

    Для подсчета товаров в нужной ячейке написали следующую формулу (см. выше):

    =СЧЁТЕСЛИМН(B2:B5 ;«>90» ) , где:

    B2:B5 - диапазон, по которому будут проверять, по заданному нами условию;

    «>90» -само условие, заключается в кавычки.

    Теперь попробуем немного усложнить наш пример, и добавим счет еще по одному условию: с ценой больше 90 + количество на складе менее 20 кг.

    Формула приобретает вид:

    =СЧЁТЕСЛИМН(B2:B6;»>90″;C2:C6 ;«<20» )

    Здесь все осталось таким же, кроме еще одного условия (C2:C6;»<20″ ). Кстати, таких условий может быть очень много!

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

    4. Поиск и подстановка значений из одной таблицы в другую (формула ВПР)

    Представим, что к нам пришла новая таблица, с новыми ценниками для товара. Хорошо, если наименований 10-20 - можно и в ручную их все «перезабить». А если таких наименований сотни? Гораздо быстрее, если бы Excel самостоятельно нашел в совпадающие наименования из одной таблицы в другой, а затем скопировал новые ценники в старую нашу таблицу.

    Для такой задачи используется формула ВПР . В свое время сам «мудрил» с логическими формулами «ЕСЛИ» пока не встретил эту замечательную штуку!

    И так, начнем…

    Вот наш пример + новая таблица с ценниками. Сейчас нам нужно автоматически подставить новые ценники из новой таблицы в старую (новые ценники красные ).

    Ставим курсор в ячейку B2 - т.е. в первую ячейку, где нам нужно изменить ценник автоматически. Далее пишем формулу, как на скриншоте ниже (после скриншота будет подробное пояснение к ней).

    =ВПР(A2 ;$D$2:$E$5 ;2 ) , где

    A2 - то значение, которое мы будем искать, чтобы взять новый ценник. В нашем случае ищем в новой таблице слово «яблоки».

    $D$2:$E$5 - выделяем полностью нашу новую таблицу (D2:E5, выделение идет от верхнего левого угла к правому нижнему по диагонали), т.е. там, где будет производится поиск. Знак «$» в этой формуле необходим для того, чтобы при копировании этой формулы в другие ячейки - D2:E5 не менялись!

    Важно! Поиск слова «яблоки» будет вестись только в первой колонке вашей выделенной таблицы, в данном примере «яблоки» будет искаться в колонке D.

    2 - Когда слово «яблоки» будет найдено, функция должна знать, из какого столбика выделенной таблицы (D2:E5) скопировать нужное значение. В нашем примере копировать из колонки 2 (E), т.к. в первой колонке (D) мы производили поиск. Если ваша выделенная таблица для поиска будет состоять из 10 колонок, то в первой колонке производится поиск, а со 2 по 10 колонки - вы можете выбрать число для копирования.

    Чтобы формула =ВПР(A2;$D$2:$E$5;2) подставила новые значения и для других наименований товара - просто скопируйте ее в другие ячейки столбца с ценниками товара (в нашем примере копируйте в ячейки B3:B5). Формула автоматически произведет поиск и копирование значения из нужной вам колонки новой таблицы.

    5. Заключение

    В статье мы рассмотрели основы работы с Excel, с того как начать писать формулы. Привели примеры самых распространенных формул, с которыми очень часто приходится работать большинству, кто работает в Excel.

    Надеюсь что кому-то пригодятся разобранные примеры и помогут ускорить его работу. Удачных экспериментов!

    А какие формулы используете вы, можно ли как-то упростить формулы приведенные в статье? Например, на слабых компьютерах, при изменении каких-то значений в больших таблицах, где производятся автоматически расчеты - компьютер зависает на пару секунд, пересчитывая и показывая новые результаты…

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

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

    Разберем гипотетический пример, который решим посредством применения уже известной нам формулы расчета NPV, а затем повторим наши вычисления, используя возможности Excel.

    Задача на нахождение NPV

    Пример . Первоначальные в A составляют 10000 рублей. Ежегодная – 10 %. Динамика поступлений с 1-го по 10-ый годы представлена в нижеследующей таблице:

    Период Притоки Оттоки
    0 10000
    1 1100
    2 1200
    3 1300
    4 1450
    5 1600
    6 1720
    7 1860
    8 2200
    9 2500
    10 3600

    Для наглядности cответствующие данные можно представить графически:

    Рисунок 1. Графическое представление исходных данных для расчета NPV

    Стандартное решение. Для решения задачи будем использовать уже известную нам формулу NPV:

    Просто подставляем в нее известные значения, которые затем суммируем. Для этих вычислений нам пригодится калькулятор:

    NPV = -10000/1,1 0 + 1100/1,1 1 + 1200/1,1 2 + 1300/1,1 3 + 1450/1,1 4 + 1600/1,1 5 + 1720/1,1 6 + 1860/1,1 7 + 2200/1,1 8 + 2500/1,1 9 + 3600/1,1 10 = 352,1738 рублей .

    Расчет NPV в Excel (пример табличный)

    Этот же пример мы можем решить, организовав соответствующие данные в форме таблицы Excel.

    Выглядеть это должно примерно так:

    Рисунок 2. Расположение данных примера на листе Excel

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

    Ячейка Формула
    E4 =1/СТЕПЕНЬ(1+$F$2/100;B4)
    E5 =1/СТЕПЕНЬ(1+$F$2/100;B5)
    E6 =1/СТЕПЕНЬ(1+$F$2/100;B6)
    E7 =1/СТЕПЕНЬ(1+$F$2/100;B7)
    E8 =1/СТЕПЕНЬ(1+$F$2/100;B8)
    E9 =1/СТЕПЕНЬ(1+$F$2/100;B9)
    E10 =1/СТЕПЕНЬ(1+$F$2/100;B10)
    E11 =1/СТЕПЕНЬ(1+$F$2/100;B11)
    E12 =1/СТЕПЕНЬ(1+$F$2/100;B12)
    E13 =1/СТЕПЕНЬ(1+$F$2/100;B13)
    E14 =1/СТЕПЕНЬ(1+$F$2/100;B14)
    F4 =(C4-D4)*E4
    F5 =(C5-D5)*E5
    F6 =(C6-D6)*E6
    F7 =(C7-D7)*E7
    F8 =(C8-D8)*E8
    F9 =(C9-D9)*E9
    F10 =(C10-D10)*E10
    F11 =(C11-D11)*E11
    F12 =(C12-D12)*E12
    F13 =(C13-D13)*E13
    F14 =(C14-D14)*E14
    F15 =СУММ(F4:F14)

    В результате в ячейке F15 мы получим искомое значение NPV, равное 352,1738.

    Чтобы создать такую таблицу нужно 3-4 минуты. Excel позволяет найти нужное значение NPV быстрее.

    Расчет NPV в Excel (функция ЧПС)

    Поместим в ячейку B17 (или любую другую ячейку) формулу:

    ЧПС(F2/100;C5:C14)-D14

    Мы мгновенно получим точное значение NPV в рублях (352,1738р.).

    Рисунок 3. Вычисление NPV с помощью формулы Excel ЧПС

    Наша формула ссылается на ячейки F2 (у нас там указана процентная ставка – 10 %; для использования в функции ЧПС нужно разделить ее на 100), диапазон значений C5:C14, где размещены данные о притоках , и на ячейку D14, содержащую размер первоначальных



    Есть вопросы?

    Сообщить об опечатке

    Текст, который будет отправлен нашим редакторам:

    Отправить