OLAP е лесен! Практическа работа по olap технология в excel.

Аналитична аналитична обработка (OLAP) е технология, която се използва за организиране на големи бизнес бази данни и поддръжка на бизнес разузнаване. OLAP базите данни са разделени на един или повече кубове и всеки куб е организиран от администратора на куба, за да отговаря на начина, по който данните се извличат и анализират, за да улесни създаването и използването на отчетите с обобщена таблица и обобщена диаграма, които ще ви трябват.

В тази статия

Какво е бизнес разузнаване?

Бизнес анализаторът често иска да получи по-голяма картина на бизнеса, да види по-широки тенденции въз основа на обобщени данни и да види тенденциите, разбити на произволен брой променливи. Бизнес разузнаването е процес на извличане на данни от OLAP база данни и анализиране на тези данни, за да се предоставят прозрения, които могат да се използват за вземане на информирани бизнес решения и предприемане на действия. Например с OLAP и бизнес разузнаване можете да отговорите на следните въпроси относно бизнес данни.

Как общите продажби на всички продукти през 2007 г. се сравняват с продажбите от 2006 г. насам?

Как се сравнява това с датата и часа за периода на обезщетение през последните пет години?

Колко пари са похарчили клиентите за 35 миналата година и как това поведение се е променило с времето?

Колко продукта са продадени в две конкретни държави/региони този месец в сравнение със същия месец миналата година?

За всяка възрастова група на клиента Каква е разбивката на рентабилността (както процент на марж, така и обща) по продуктова категория?

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

Какво е онлайн аналитична обработка (OLAP)?

Базите данни OLAP (онлайн аналитична обработка) опростяват заявките за бизнес разузнаване. OLAP е технология за база данни, която е оптимизирана за заявки и отчети, а не за обработка на транзакции. Източникът на данни за OLAP са бази данни за онлайн обработка на транзакции (OLTP), които обикновено се съхраняват в хранилища за данни. OLAP данните се извличат от тези исторически данни и се комбинират в структури, които позволяват сложен анализ. OLAP данните също са организирани йерархично и се съхраняват в кубове, а не в таблици. Това е сложна технология, която използва многоизмерни структури, за да осигури бърз достъпкъм данните за анализ. В тази организация, за отчет с обобщена таблица или отчет с обобщена диаграма, можете лесно да показвате обобщени данни на високо ниво, като общи продажби за цяла държава или регион, както и да показвате подробности за сайтове, където продажбите са особено силни или слаби.

OLAP базите данни са предназначени да ускорят зареждането на данни. Тъй като OLAP сървърът, не Microsoft офис Excel изчислява обобщени стойности, по-малко данни трябва да бъдат изпратени до Excel, когато създавате или редактирате отчет. Този подход ви позволява да работите с по-голямо количество изходни данни, отколкото ако данните бяха организирани в традиционна база данни, където Excel извлича всички отделни записи и изчислява обобщени стойности.

OLAP базите данни съдържат два основни типа данни: мерки, които са цифрови данни, количества и средни стойности, които се използват за вземане на информирани бизнес решения, и измерения, които са категории, използвани за организиране на тези мерки. Базите данни OLAP ви помагат да организирате вашите данни с много нива на детайлност, като използвате същите категории, които познавате за анализ на данни.

Следващите раздели описват всеки компонент подробно по-долу.

КубиченСтруктура от данни, която комбинира мерки на нива и йерархии на всяко измерение, което искате да анализирате. Кубовете съчетават множество измерения като време, география и продуктови линии с обобщени данни като продажби и инвентар. Кубовете не са „кубове“ в строгия математически смисъл, тъй като не е задължително да имат еднакви страни. Те обаче представляват подходяща метафора за сложна концепция.

измерванияНабор от стойности в куб, които се основават на колона в таблицата с факти на куба и която обикновено е числова стойност. Мерките са централни стойности в куб, които са предварително обработени, обработени и анализирани. Най-често срещаните примери са продажби, приходи, приходи и разходи.

ЧленЕлемент в йерархия, който представлява едно или повече срещания на данни. Един елемент може да бъде уникален или неуникален. Например 2007 и 2008 представляват уникални членове на ниво година на времевото измерение, докато януари представлява неуникални членове на ниво месец, тъй като има повече от един януари във времевото измерение, защото съдържа данни за повече от една година.

Изчислен членЧлен на измерение, чиято стойност се оценява по време на изпълнение с помощта на израз. Стойностите на изчислените членове могат да бъдат извлечени от стойностите на други членове. Например, изчисленият артикул „печалба“ може да се определи чрез изваждане на стойността на артикула, както и разходите, от стойността на артикула, продажбите.

измерванеНабор от една или повече подредени йерархии на ниво куб, които потребителят разбира и използва като основа за анализ на данни. Например, географско измерение може да включва нива на държава/регион, щат/регион и град. Освен това измерението на времето може да включва йерархия с нива на година, тримесечие, месец и ден. В отчет с обобщена таблица или отчет с обобщена диаграма всяка йерархия се превръща в набор от полета, които могат да се разширяват и свиват, за да се показват по-ниско или по-високо високи нива.

ЙерархияЛогическа дървовидна структура, която подрежда членовете на измерение така, че всеки член има един родител и нула или повече деца. Дъщерен елемент е член на по-ранна група в йерархията, която е пряко свързана с текущия член. Например, във времева йерархия, съдържаща нива на тримесечие, месец и ден, януари е дете на Qtr1. Родителският елемент е членът от по-ниско ниво в йерархията, който е пряко свързан с текущия член. Стойността на родителя обикновено е консолидация на стойностите на всички дъщерни елементи. Например, във времева йерархия, съдържаща нива на тримесечие, месец и ден, Qtr1 е родителят на януари.

НивоВ йерархия данните могат да бъдат организирани в по-ниски и по-високи нива на детайлност, като години, тримесечия, месеци и нива на дни в йерархия на времето.

OLAP функции в Excel

Получаване на OLAP данниМожете да се свържете с OLAP източници на данни по същия начин, както можете да се свържете с други външни източници на данни. Можете да работите с бази данни, създадени с услуги на Microsoft SQL сървър OLAP версия 7.0, Microsoft SQL Server Analysis Services версия 2000 и Microsoft SQL Server Analysis Services версия 2005, Microsoft OLAP сървърни продукти. Excel може също да работи с OLAP продукти на трети страни, които са съвместими с OLE-DB за OLAP.

OLAP данните могат да се показват само като отчет с обобщена таблица или обобщена диаграма или във функция на работен лист, преобразувана от отчет с обобщена таблица, а не като външен диапазон от данни. Можете да запазвате OLAP отчети с обобщена таблица и обобщена диаграма в шаблони за отчети и можете да създавате файлове за връзка с данни на Office (ODC), за да се свързвате с OLAP бази данни за OLAP заявки. Когато отворите .odc файл в Excel, се показва празен отчет с обобщена таблица и е готов за поставяне.

Създавайте кубични файлове за използване офлайнМожете да създадете самостоятелен файл с куб (.cub) с поднабор от данни от база данни на OLAP сървър. Офлайн кубичните файлове се използват за работа с OLAP данни, когато не сте свързани към мрежа. С куб можете да работите с повече данни в отчет с обобщена таблица или отчет с обобщена диаграма, отколкото иначе, и да получите данните по-бързо. Можете да създавате кубични файлове само ако използвате OLAP доставчик като Microsoft SQL Analysis Services версия 2005, който поддържа тази функция.

Действия на сървъраСървърното действие е незадължителна функция, която администратор на OLAP куб може да дефинира на сървър, който използва елемент на куб или мярка като параметър в заявка за извличане на информация в куб или за стартиране на друго приложение, като например браузър. Excel поддържа URL адреси, отчети, набори от редове, разбивка надолу и разбивка на подробни сървърни действия, но не поддържа собствен собствен оператор и набор от данни.

KPI KPI е специфична, изчислена мярка, дефинирана на сървъра, която ви позволява да проследявате „Ключови показатели за ефективност“, включително статус (текущата стойност съответства на определен брой). и тенденция (стойности във времето). Когато се показват, сървърът може да изпрати подходящите икони, подобно на новата икона на Excel, за подреждане над или под нивата на състояние (например за икона за спиране), както и да превърта стойността нагоре или надолу (за например икона със стрелка за посока).

Форматиране на сървъраАдминистраторите на Cube могат да създават мерки и изчислени членове, като използват цветово форматиране, форматиране на шрифтове и правила за условно форматиране, които могат да бъдат присвоени като корпоративно стандартно бизнес правило. Например форматът от страна на сървъра за доход може да бъде формат на числова валута, цветът на клетката е зелен, ако стойността е по-голяма или равна на 30 000 и червен, ако стойността е по-малка от 30 000, а стилът на шрифта е удебелен, ако стойността е под 30 000, а при положителна стойност – обикновена. по-голямо или равно на 30 000. Допълнителна информацияможе да се намери.

Език на интерфейса на OfficeАдминистратор на куб може да дефинира преводи за данни и грешки на сървъра за потребители, които трябва да преглеждат информация от обобщена таблица на друг език. Тази функция се дефинира като свойство на файлова връзка и локалът и държавата на компютъра на потребителя трябва да съответстват на езика на интерфейса.

Софтуерни компоненти, необходими за достъп до OLAP източници на данни

OLAP доставчикЗа да настроите OLAP източници на данни за Excel, имате нужда от един от следните OLAP доставчици.

Microsoft OLAP доставчик Excel включва драйвер за източник на данни и клиент софтуерза достъп до бази данни, създадени с Microsoft SQL Server olap версия 7.0, Microsoft SQL Server olap версия 2000 (8.0) и Microsoft SQL Server Analysis services версия 2005 (9.0).

Доставчици на OLAP трети страниДруги OLAP продукти изискват инсталиране на допълнителни драйвери и клиентски софтуер. За да използвате възможностите на Excel за манипулиране на OLAP данни, продукт на трета страна трябва да отговаря на стандарта OLE-DB за OLAP и да е съвместим с Microsoft Office. За информация относно инсталирането и използването на OLAP доставчик на трета страна вижте системен администраторили доставчик на OLAP продукт.

Сървърни бази данни и Cube файлове OLAP клиентският софтуер на Excel поддържа връзки към два типа OLAP бази данни. Ако базата данни на OLAP сървъра е достъпна в мрежата, можете да получите изходни данни от нея директно от нея. Ако имате самостоятелен кубичен файл, който съдържа OLAP данни или файл с дефиниция на куб, можете да се свържете с този файл и да получите изходни данни от него.

Източници на данниИзточникът на данни осигурява достъп до всички данни в OLAP база данни или офлайн куб файл. След като създадете OLAP източник на данни, можете да базирате отчети върху него и да връщате OLAP данни на Excel като отчет с обобщена таблица или обобщена диаграма или като функция на лист, преобразувана от отчет с обобщена таблица.

Microsoft QueryС Query можете да получите данни от външна основаданни, като например Microsoft SQL или Microsoft Access. Не е необходимо да използвате заявка за извличане на данни от OLAP обобщена таблица, свързана с кубичен файл. Допълнителна информация .

Разлики в OLAP функциите и не-OLAP изходните данни

Ако работите с отчети с обобщени таблици и обобщени диаграми от изходни данни на OLAP и други типове изходни данни, ще видите някои разлики във функциите.

Извличане на данни OLAP сървърът връща нови данни на Excel при всяка промяна на оформлението на отчета. С други типове външни източници на данни вие правите заявка за всички изходни данни наведнъж или можете да зададете параметри за заявката само когато показвате различни елементи на полето за филтриране на отчета. Освен това имате още няколко опции за актуализиране на отчета.

В отчети, базирани на изходни данни на OLAP, опциите за полета за филтър на отчета не са налични, фоновата заявка не е налична и опцията за оптимизиране на паметта не е налична.

Забележка:Опцията за оптимизиране на паметта също не е налична за източници на данни OLEDB и отчети с обобщена таблица, базирани на диапазон от клетки.

Типове полетаИзточник на OLAP данни. полетата за размери могат да се използват само като редове (редове), колони (категория) или полета на страници. Полетата за измерване могат да се използват само като полета за стойност. За други типове изходни данни всички полета могат да се използват във всяка част на отчета.

Достъп до подробни данниЗа изходните OLAP данни сървърът определя наличните нива на детайлност и изчислява обобщени стойности, така че подробните записи, които съставляват обобщените стойности, може да не са налични. Сървърът обаче може да предостави полета за свойства, които можете да показвате. Други типове данни източник нямат полета за свойства, но можете да показвате основна информация за полета за данни и стойности на елементи и да показвате елементи без данни.

Полетата за филтриране на OLAP отчет може да нямат всичкоелементи и команда Показване на страниците с филтър за отчетиНе е наличен.

Първоначален ред на сортиранеЗа изходните OLAP данни елементите първо се показват в реда, в който са върнати от OLAP сървъра. Можете да сортирате или ръчно да пренаредите елементите. За други типове изходни данни елементите на новия отчет първо се сортират по име на елемент във възходящ ред.

Ними OLAP сървърите предоставят обобщени стойности директно в отчета, така че не е възможно да се променят обобщените функции за полетата със стойности. За други типове изходни данни можете да промените обобщената функция за полето за стойност и да използвате множество функции за обобщение за едно и също поле за стойност. Не можете да създавате изчисляеми полета и изчисляеми членове в отчети с OLAP изходни данни.

Междинни сумиВ отчети с OLAP изходни данни не можете да промените функцията за обобщение за междинни суми. С други типове изходни данни можете да промените обобщаващите функции за междинни суми и да показвате или скривате междинни суми за всички полета на редове и колони.

За изходни OLAP данни можете да включвате или изключвате скрити елементи при изчисляване на междинни суми и общи суми. За други типове изходни данни можете да включите скрити елементи в полетата за филтър на отчета в междинни суми, но скритите елементи в други полета ще бъдат изключени по подразбиране.

Проблемите на анализите, OLAP, хранилищата за данни са от все по-голям интерес за руските ИТ специалисти. Към днешна дата нашата компютърна преса и Интернет са публикували много добри, академични в начина на представяне на материали по тази тема, включително уводни. Предлагаме на вашето внимание статия, в която умишлено се опитваме да обясним OLAP „на пръсти“, използвайки конкретен пример. Практиката показва, че подобно обяснение е необходимо за някои ИТ специалисти и особено за крайните потребители.

И така, OLAP *1 като първо приближение, „на пръсти“, може да се определи като специален начин за анализиране на данни и генериране на отчети. Същността му е да предостави на потребителя многоизмерна таблица, която автоматично обобщава данните в различни раздели и ви позволява интерактивно да управлявате изчисленията и формата на отчета. Тази статия ще говори за технологията и основните операции на OLAP, като използва примера за анализ на фактури за предприятие, занимаващо се с търговия на едро с хранителни продукти.

*1. OLAP - Он-лайн аналитична обработка, оперативен анализ на данни.

Като инструмент ще се разглежда OLAP система от най-простия и най-евтин клас, OLAP клиент *1. Например, избран е най-простият продукт от броя на OLAP клиентите - „Contour Standard“ от Intersoft Lab. (За по-голяма яснота, по-нататък в статията, общоприетите термини на OLAP ще бъдат посочени с удебелен шрифт и придружени от еквивалент на английски.)

*1. За повече информация относно класификацията на OLAP системи вижте статията "OLAP Made in Russia" в PC Week/RE, No. 3/2001.

И така, нека започнем със системата. Първо трябва да опишете източника на данни (източник на данни) - пътя до таблицата и нейните полета. Това е задача на потребителя, който познава физическата реализация на базата данни. За крайните потребители той превежда името на таблицата и нейните полета в термини на домейн. Зад „източника на данни“ стои локална таблица, таблица или изглед на SQL сървър или съхранена процедура.

Най-вероятно в определена база данни фактурите се съхраняват не в една, а в няколко таблици. Освен това някои полета или записи може да не се използват за анализ. Следователно след това се създава образец (резултатен набор или заявка), в който се конфигурират: алгоритъмът за комбиниране на таблици по ключови полета, условията за филтриране и наборът от върнати полета. Нека наречем нашата селекция „Фактури“ и да поставим всички полета на източника на данни „Фактури“ в нея. По този начин ИТ специалист, създавайки семантичен слой, скрива физическото изпълнение на базата данни от крайния потребител.

След това OLAP отчетът се конфигурира. Това може да бъде направено от експерт по предмета. Първо, полетата на плоска извадка от данни са разделени на две групи - факти (факти или мерки) и измервания (измерения). Фактите са числа, а измерванията са "разрези", в които фактите ще бъдат обобщени. В нашия пример измеренията ще бъдат: “Регион”, “Град”, “Клиент”, “Продукт”, “Дата”, а фактът ще бъде един - полето “Сума” на фактурата. Всъщност трябва да бъдат избрани един или повече алгоритми за агрегиране. OLAP може не само да обобщава сумите, но и да извършва по-сложни изчисления, до Статистически анализ. Избирането на множество алгоритми за агрегиране ще създаде виртуални, изчислени факти. В примера е избран един алгоритъм за агрегиране - „Сума“.

Специално свойство на OLAP системите е генерирането на измерения и данни за по-стари периоди от време от дата и автоматичното изчисляване на суми за тези периоди. Нека изберем периодите „Година“, „Тримесечие“ и „Месец“, докато в отчета няма да има данни за всеки ден, но ще се появят генерираните измерения „Година“, „Тримесечие“ и „Месец“. Да кръстим отчета „Анализ на продажбите“ и да го запазим. Приключи работата по създаване на интерфейса на аналитичното приложение.

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

За да може манипулирането на данни да бъде интуитивно, инструменти за управление динамична таблицаса елементите на самата таблица – нейните колони и редове. Потребителят може да премества, изтрива, филтрира и извършва други OLAP операции. Таблицата автоматично изчислява нови междинни суми и крайни суми.

Например, като плъзнете (операция „преместване“) колоната „Продукт“ на първо място, ще получим отчет за сравнение - „Сравнение на обемите на продажбите на продукти за годината“. За да обобщите данни за една година, просто плъзнете колоните „Тримесечие“ и „Месец“ в горната част на таблицата - „зона за неактивни измервания“. Измеренията „Тримесечие“ и „Месец“, прехвърлени в тази област, ще бъдат затворени (операция „близко измерение“), т.е. изключени от отчета; фактите ще бъдат обобщени за годината. Въпреки факта, че размерите са затворени, можете да зададете конкретни години, тримесечия и месеци за филтриране на данни (операция „филтриране“).

За по-голяма яснота, нека променим типа на графиката, илюстрираща OLAP таблицата и нейното местоположение на екрана.

Задълбочаването на данните (операция „drill down“) ви позволява да получите по-подробна информация за продажбите на продукта, който ни интересува. Щраквайки върху знака „+“ срещу продукта „Кафе“, ще видим обемите на продажбите му по региони. След като отворихме Уралския регион, ще получим обеми на продажбите в контекста на градовете в Уралския регион, като се задълбочим в данните за Екатеринбург, ще можем да видим данни за купувачите на едро в този град.

Можете също да използвате отворени измервания, за да зададете филтри. За да сравним динамиката на продажбите на бонбони в Москва и Екатеринбург, нека зададем филтри за измеренията „Продукт“ и „Град“.

Затворете ненужните измервания и изберете типа на графиката "Линия". На получената графика можете да проследите динамиката на продажбите, да оцените сезонните колебания и връзката между спадовете и растежа на продажбите на стоки в различни градове.

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

Всъщност OLAP е естествено продължение и развитие на идеята за електронни таблици. По същество визуалният интерфейс на OLAP също е електронна таблица, но оборудвана с мощен изчислителен механизъм и специален стандарт за представяне и управление на данни. Освен това, някои OLAP клиенти са внедрени като добавки към MS Excel. Следователно милионната армия от „бели якички“, които са уверени в електронните таблици, също бързо овладяват OLAP инструментите. За тях това е „кадифена революция“, която предоставя нови възможности, но не включва необходимост от преквалификация.

Ако читателят, след като е прочел тази статия, не е загубил интерес към OLAP, той може да се обърне към материалите, споменати в началото. Колекции от такива материали са публикувани в редица сайтове в Интернет, включително сайта Intersoft lab - www.iso.ru. От него можете да изтеглите и демо версия на системата Kontur Standard с описания в статията пример.

Стандартен осева таблицаоригиналните данни се съхраняват на локалния твърд диск. По този начин винаги можете да ги управлявате и реорганизирате, дори и да нямате достъп до мрежата. Но това по никакъв начин не се отнася за OLAP PivotTables. В OLAP PivotTables кешът никога не се съхранява на локалния твърд диск. Следователно, веднага след прекъсване на връзката от локална мрежавашата обобщена таблица ще се провали. Няма да можете да местите нито едно от полетата в него.

Ако все още трябва да анализирате OLAP данни, след като сте офлайн, създайте офлайн куб с данни. Офлайн кубът с данни е отделен файл, който е кеш на PivotTable, който съхранява OLAP данни, които се преглеждат след прекъсване на връзката с локалната мрежа. OLAP данните, копирани в обобщена таблица, могат да бъдат отпечатани, сайтът http://everest.ua описва това подробно.

За да създадете самостоятелен куб с данни, първо създайте OLAP PivotTable. Поставете курсора в обобщената таблица и щракнете върху бутона OLAP инструменти в контекстния раздел Инструменти, който е част от групата контекстни раздели Инструменти за обобщена таблица. Изберете командата Offline OLAP (фиг. 9.8).

Ориз. 9.8. Създайте офлайн куб с данни

Появява се диалоговият прозорец Offline OLAP Data Cube Settings. Щракнете върху бутона Създаване на офлайн файл с данни. Стартирали сте съветника за създаване на файл с куб с данни. Щракнете върху бутона Напред, за да продължите процедурата.

Първо трябва да укажете размерите и нивата, които ще бъдат включени в куба с данни. В диалоговия прозорец трябва да изберете данните, които ще бъдат импортирани от OLAP базата данни. Идеята е да посочите само тези размери, които ще са необходими след изключване на компютъра от локалната мрежа. Колкото повече измерения посочите, толкова по-голям ще бъде офлайн кубът с данни.

Щракнете върху бутона Напред, за да продължите към следващия диалогов прозорец на съветника. Той ви дава възможност да посочите членове или елементи от данни, които няма да бъдат включени в куба. По-специално, няма да имате нужда от мярката за разширена сума за интернет продажби, така че няма да бъде маркирана в списъка. Изчистеното квадратче за отметка показва, че посоченият елемент няма да бъде импортиран и да заема допълнително място на локалния твърд диск.

В последната стъпка посочете местоположението и името на куба с данни. В нашия случай файлът с куб ще се казва MyOfflineCube.cub и ще се намира в папката Work.

Файловете с кубчета данни имат разширение .куб

След известно време Excel записва офлайн куба с данни в указаната папка. За да го тествате, щракнете два пъти върху файла, който автоматично ще генерира работна книга на Excel, която съдържа обобщена таблица, свързана с избрания куб с данни. Веднъж създаден, можете да разпространявате офлайн куба с данни на всички заинтересовани потребители, които работят в офлайн LAN режим.

След като се свържете с локалната мрежа, можете да отворите офлайн файла с куб с данни и да го актуализирате, както и съответната таблица с данни. Основният принцип е, че офлайн кубът с данни се използва само за работа, когато локалната мрежа е прекъсната, но е задължително да се актуализира след възстановяване на връзката. Опитът за актуализиране на офлайн куб с данни след прекъсване на връзката ще се провали.

Работа с офлайн Cube файлове

Офлайн кубичен файл (.cub) съхранява данни под формата на OLAP (онлайн аналитична обработка) куб. Тези данни може да са част от OLAP базата данни на OLAP сървъра или могат да бъдат генерирани независимо от OLAP базата данни. Използвайте офлайн кубичен файл, за да продължите да работите с обобщени таблици и отчети с обобщени диаграми, когато сървърът не е наличен или когато сте офлайн.

Бележка за безопасност:Бъдете внимателни, когато използвате или разпространявате офлайн кубичен файл, който съдържа чувствителна или лична информация. Вместо кубичен файл се препоръчва да съхранявате данните в работна книга, за да можете да контролирате достъпа до данните чрез управление на правата. За повече информация вижте Управление на правата за информация в Office.

Когато работите с отчет с обобщена таблица или обобщена диаграма, който се основава на изходни данни на OLAP сървър, можете да използвате съветника за офлайн куб, за да копирате изходните данни в отделен офлайн файл с куб на вашия компютър. Създаването на тези офлайн файлове изисква доставчик на OLAP данни, който поддържа тази възможност, като MSOLAP от Microsoft SQL Server Analysis Services, инсталиран на компютъра.

Забележка:Създаването и използването на офлайн кубични файлове от Microsoft SQL Server Analysis Services е предмет на срок и лицензиране Microsoft инсталации SQL сървър. Прегледайте съответната информация за лицензиране на изданието на SQL Server.

Работа с Offline Cube Wizard

За да създадете офлайн файл с куб, можете да изберете подмножество от данни в OLAP база данни с помощта на съветника за офлайн куб и след това да запишете това подмножество. Не е необходимо отчетът да включва всички полета, включени във файла, нито е необходимо да избира някое от тях и полетата с данни, налични в OLAP базата данни. За да запазите файла минимален, можете да включите само данните, които искате да се показват в отчета. Можете да пропуснете всички измерения, а за повечето типове измерения можете също да изключите подробности и членове от по-ниско ниво. Най-високо нивокоито не е необходимо да се показват. За всички елементи, които включвате, полетата за свойства, налични в базата данни за тези елементи, също се съхраняват в офлайн файла.

Вземане на данни офлайн и повторно свързване

За да направите това, първо трябва да създадете отчет с обобщена таблица или обобщена диаграма въз основа на базата данни на сървъра и след това да създадете самостоятелен кубичен файл от отчета. След това можете да превключвате отчета между сървърната база данни и офлайн файла по всяко време. Например, ако използвате лаптопза домашно и видео пътуване и след това свържете отново компютъра към мрежата.

По-долу са основните стъпки, които трябва да следвате живот на батериятас данните и след това прехвърлете данните обратно в интернет.

Създайте или отворете отчет с обобщена таблица или обобщена диаграма въз основа на OLAP данните, до които искате да имате достъп офлайн.

Създайте офлайн файл с куб на вашия компютър. В глава Създайте офлайн файл с куб от база данни на OLAP сървър(по-долу в тази статия).

Прекъсване на връзката с мрежата и работа с офлайн куб файл.

Отидете онлайн и прикачете отново кубичния файл офлайн. Разгледайте секцията Повторно свързване на офлайн кубичния файл към базата данни на OLAP сървъра(по-долу в тази статия).

Актуализирайте офлайн кубичния файл с нови данни и регенерирайте офлайн кубичния файл. Разгледайте секцията актуализирайте и създайте отново офлайн кубичния файл(по-долу в тази статия).

БЛОГ

Само качествени публикации

Какво представляват обобщените таблици на Excel и OLAP кубовете

Вижте видеото към статията:

OLAP- това е английски. онлайн аналитична обработка, аналитична технологияобработка на данни в реално време. на разбираем език- хранилище с многоизмерни данни (Cube), още по-просто - просто база данни, от която можете да получите данни в Excel и да ги анализирате с помощта на инструмента на Excel - PivotTables.

Пивотни таблици- Това потребителски интерфейсза показване на многоизмерни данни. С други думи - специален вид таблици, с които можете да направите почти всеки отчет.

За да стане по-ясно, нека сравним „Редовната таблица“ с „Осевата таблица“

Редовна маса:

Пивотна таблица:

Основна разлика Пивотни таблицие наличието на прозорец Списък с полета на обобщена таблица”, от който можете да изберете желаните полета и да получите всяка таблица автоматично!

Как да използвам

Отворете excel файл, който е свързан към OLAP куба, например "BIWEB":

Сега какво означава това и как да го използваме?

Плъзнете и пуснете задължителните полета, за да получите например следната таблица:

« Плюсове» ви позволяват да разгледате доклада. В този пример „Марка“ е разширена до „Съкратени имена“, а „Тримесечие“ до „Месец“, т.е. Така:

Аналитични функции в Excel (кубични функции)

Microsoft непрекъснато добавя нови функции към Excel по отношение на анализ на данни и визуализация. Работата с информация в Excel може да бъде представена като три относително независими слоя:

• "правилно" организирани изходни данни
• математика (логика) на обработка на данни
• представяне на данни

Ориз. 1. Анализ на данни в Excel: a) необработени данни, b) измерване c Power Pivot, в) арматурно табло; За да увеличите изображение, щракнете с десния бутон върху него и изберете Отворете изображението в нов раздел

Изтеглете бележка във формат Word или pdf, примери във формат Excel

Кубични функции и осеви таблици

Най-простото и в същото време много мощно средство за представяне на данни са обобщените таблици. Те могат да бъдат изградени от данни, съдържащи се в: a) работен лист на Excel, b) OLAP куб или c) модел на данни Power Pivot. В последните два случая, в допълнение към обобщената таблица, можете да използвате аналитични функции (кубни функции), за да генерирате отчет на Excel лист. Пивотните таблици са по-лесни. Функциите на Cube са по-сложни, но осигуряват повече гъвкавост, особено при отчитане, така че се използват широко в таблата за управление.

Следващата дискусия се отнася за формули за кубове и обобщени таблици, базирани на модела Power Pivot и, в няколко случая, базирани на OLAP кубове.

Лесен начин да получите функциите на кубчета

Когато (ако) сте започнали да изучавате VBA код, сте научили, че най-лесният начин да получите кода е като запишете макрос. Освен това кодът може да се редактира, могат да се добавят цикли, проверки и т. н. По подобен начин най-лесният начин да получите набор от функции на куба е чрез преобразуване на обобщената таблица (фиг. 2). Застанете на която и да е клетка от обобщената таблица, отидете на раздела Анализ, щракнете върху бутона съоръжения OLAPи натиснете Преобразуване във формули.

Ориз. 2. Преобразуване на обобщена таблица в набор от функции на куб

Числата ще бъдат запазени и това няма да са стойности, а формули, които извличат данни от модела на данни на Power Pivot (фиг. 3). Можете да форматирате получената таблица. По-специално можете да изтривате и вмъквате редове и колони в таблицата. Срезът остава и засяга данните в таблицата. Когато оригиналните данни се актуализират, числата в таблицата също ще бъдат актуализирани.

Ориз. 3. Таблица на базата на кубични формули

Функция CUBEVALUE().

Това е може би основната функция на кубовете. Тя е еквивалентна на площта Стойностиосева таблица. CUBEVALUE извлича данни от Power Pivot куб или модел и ги показва извън обобщена таблица. Това означава, че не сте ограничени от границите на обобщената таблица и можете да създавате отчети с безброй възможности.

Писане на формула от нулата

Не е необходимо да конвертирате готовата обобщена таблица. Можете да напишете всяка формула за куб от нулата. Например в клетка C10 (фиг. 4) се въвежда следната формула:

Ориз. 4. Функцията CUBEVALUE() в клетка C10 връща продажбите на велосипеди за всички години, както в обобщената таблица

Малък трик. За по-лесно четене на куб формули е желателно на всеки ред да се поставя само един аргумент. Можете да намалите прозореца на Excel. За да направите това, щракнете върху иконата Превъртете се в прозорецаразположен в горния десен ъгъл на екрана. След това коригирайте хоризонтално размера на прозореца. Алтернативен вариант– принудително обвиване на текста на формулата нова линия. За да направите това, в лентата с формули поставете курсора на мястото, където искате да направите превод, и натиснете Alt + Enter.

Ориз. 5. Минимизиране на прозореца

Синтаксис на функцията CUBEVALUE().

Помощта за Excel е напълно точна и напълно безполезна за начинаещи:

CUBEVALUE(връзка, [елемент_израз1], [елемент_израз2], ...)

Връзкае задължителен аргумент; текстов низ, представляващ името на връзката към куба.

елемент_израз– незадължителен аргумент; текстов низ, представляващ MDX, който връща елемент или кортеж в куба. В допълнение, "element_expression" може да бъде набор, дефиниран с помощта на функцията CUBESET. Използвайте „member_expression“ като срез, за ​​да определите частта от куба, за която искате да върнете обобщена стойност. Ако в element_expression не е указана мярка, ще се използва мярката по подразбиране за този куб.

Преди да продължите с обяснението на синтаксиса на функцията CUBEVALUE, няколко думи за кубовете, моделите на данни и загадъчния кортеж.

Малка информация за OLAP кубовете и моделите на данни Power Pivot

OLAP кубчета данни ( Он лине Аналитичен П rocessing - оперативен анализ на данни) са разработени специално за аналитична обработка и бързо извличанеот тях данни. Представете си триизмерно пространство, където осите са периоди от време, градове и стоки (фиг. 5а). Възлите на такава координатна мрежа съдържат стойностите на различни мерки: обем на продажбите, печалба, разходи, брой продадени единици и т.н. Сега си представете, че има десетки или дори стотици измервания ... и има също много мерки. Това ще бъде многомерният OLAP куб. Създаването, конфигурирането и поддържането в крак с OLAP кубовете е бизнес на ИТ специалистите.

Ориз. 5а. 3D OLAP куб

Аналитични формули на Excel (кубови формули) извличат заглавия на оси (например, време), имената на елементите на тези оси (август, септември), стойностите на мерките в пресечната точка на координатите. Това е тази структура, която позволява базираните на куб обобщени таблици и формули на куба да бъдат толкова гъвкави и да се адаптират към нуждите на потребителите. Обобщените таблици, базирани на работни листове на Excel, не използват мерки, така че не са толкова гъвкави за целите на анализа на данни.

Power Pivot е сравнително нова функция на Microsoft. Това е вградена в Excel и донякъде независима среда с познат интерфейс. Power Pivot е много по-добър от стандартните обобщени таблици. В същото време разработването на кубове в Power Pivot е сравнително просто и най-важното е, че не изисква участието на ИТ специалист. Microsoft реализира своя слоган: „Бизнес анализите – към масите!“. Въпреки че моделите на Power Pivot не са 100% кубове, те също могат да бъдат наричани кубове (вижте уводния курс на Power Pivot на Mark Moore и по-дългото издание на Rob Colley, Power Pivot DAX Formulas за повече подробности).

Основните компоненти на куба са измерения, йерархии, нива, елементи (или членове; на английски членове) и мерки (мерки). Измерване -основната характеристика на анализираните данни. Например продуктова категория, период от време, география на продажбите. Измерението е нещо, което можем да поставим върху една от осите на обобщената таблица. Всяко измерение, в допълнение към уникалните стойности, включва елемент, който агрегира всички елементи на това измерение.

Измерванията се основават на йерархия. Например, продуктова категория може да бъде разделена на подкатегории, след това на модели и накрая на имена на продукти (фиг. 5b).Йерархията ви позволява да създавате обобщени данни и да ги анализирате на различни нива на структура. В нашия пример йерархията Категориявключва 4 ниво.

Елементи(индивидуални членове) присъстват на всички нива. Например нивото на категория има четири елемента: аксесоари, велосипеди, дрехи, компоненти. Другите нива имат свои собствени елементи.

меркиса изчислени стойности, като обем на продажбите. Мерките в кубовете се съхраняват в тяхното собствено измерение, наречено (вижте Фигура 9 по-долу). Мерките нямат йерархии. Всяка мярка изчислява и съхранява стойност за всички измерения и всички членове и разделя въз основа на това кои членове на измерение поставяме на оста. Те също така казват кои координати ще зададем или кой филтърен контекст ще зададем. Например на фиг. 5а във всяко малко кубче се изчислява една и съща мярка - Печалба. И стойността, върната от мярката, зависи от координатите. Вдясно на фигура 5а е показано, че печалбата (в три координати) за Москва през октомври от ябълки = 63 000 рубли. Мярката може да се тълкува и като едно от измерванията. Например на фиг. 5а вместо ос Стоки, поставете ос Меркис елементи Обем на продажбите, печалба, Продадени единици. Тогава всяка клетка ще бъде някаква стойност, например Москва, септември, обем на продажбите.

Кортеж- няколко елемента с различни размери, задаващи координатите по осите на куба, в който изчисляваме мярката. Например на фиг. 5а Кортеж= Москва, октомври, ябълки. Също валиден кортеж е Перм, ябълки. Друг е ябълки, август. Измеренията, които не са включени в кортежа, имплицитно присъстват в кортежа и са представени от члена по подразбиране. По този начин клетка в многомерно пространство винаги се определя от пълен набор от координати, дори ако някои от тях са пропуснати от кортежа. Не можете да включите два елемента от едно и също измерение в кортеж, синтаксисът не го позволява. Например невалидният кортеж Москва и Перм, ябълки. За да приложите такъв многоизмерен израз, имате нужда от набор от два кортежа: Москва и ябълки + Perm и ябълки.

Набор от елементи– няколко елемента с едно и също измерение. Например ябълки и круши. Набор от кортежи- няколко кортежа, всеки от които се състои от едни и същи измерения в една и съща последователност. Например набор от два кортежа: Москва, ябълки и Перм, банани.

Автоматично довършване на помощ

Нека се върнем към синтаксиса на функцията CUBEVALUE. Нека използваме автоматично довършване. Започнете да въвеждате формула в клетка:

Excel ще предложи всички връзки, налични в работната книга на Excel:

Ориз. 6. Връзката към модела на данни на Power Pivot винаги се нарича ThisWorkbookDataModel

Ориз. 7. Връзки с кубчета

Нека продължим с въвеждането на формулата (в нашия случай за модела на данни):

Автоматичното довършване ще предложи всички налични таблици и мерки за модел на данни:

Ориз. 8. Налични елементи от първо ниво - имена на таблици и набор от мерки (маркирани)

Изберете икона Мерки. Посочете към:

CUBEVALUE(» ThisWorkbookDataModel » ; » .

Автоматичното довършване ще предложи всички налични мерки:

Ориз. 9. Налични елементи от второ ниво в набора от мерки

Изберете мярка. Добавете кавички, затваряща скоба, натиснете Enter.

CUBEVALUE(" ThisWorkbookDataModel " ; " . ")

Ориз. 10. Формула CUBEVALUE в клетка на Excel

По същия начин можете да добавите трети аргумент към формулата:

VBA в Excel Обект на Excel. PivotTable и работа с обобщени таблици и OLAP кубове в Excel

10.8 Работа с обобщени таблици (обект PivotTable)

Обект Excel.PivotTable, програмна работас обобщени таблици и OLAP кубове в Excel с помощта на VBA, обектът PivotCache, създаване на оформление на обобщена таблица

По време на дейността на повечето предприятия се натрупват така наречените необработени данни за дейността. Например за търговско предприятие могат да се натрупват данни за продажби на стоки - за всяка покупка поотделно, за предприятия клетъчна комуникация- статистика за зареждане на базови станциии така нататък. Много често мениджмънтът на едно предприятие се нуждае от аналитична информация, която се генерира на базата на необработена информация - например, за да изчисли приноса на всеки вид продукт към приходите на предприятието или качеството на обслужване в областта на дадена станция. Много е трудно да се извлече такава информация от необработена информация: трябва да изпълните много сложни SQL заявки, които отнемат много време и често пречат на текущата работа. Поради това все повече и повече необработени данни се събират първо в Data Warehouse, а след това в OLAP кубове, които са много удобни за интерактивен анализ. Най-лесният начин да мислите за OLAP кубовете е като за многоизмерни таблици, в които вместо стандартните две измерения (колони и редове, както в обикновените таблици), може да има много измерения. Терминът "секционен" обикновено се използва за описание на размерите в куб. Например маркетинговият отдел може да се нуждае от информация по време, по регион, по тип продукт, по канал за продажба и т.н. Използвайки кубове (за разлика от стандартните SQL заявки), е много лесно да получите отговори на въпроси като „колко продукта от този тип са били продадени през четвъртото тримесечие на миналата година в Северозападния регион чрез регионални дистрибутори.

Разбира се, не можете да създавате такива кубове в обикновени бази данни. OLAP кубовете изискват специализирани софтуерни продукти. SQL Server идва с OLAP база данни от Microsoft, наречена Analysis Services. Има OLAP решения от Oracle, IBM, Sybase и др.

За да работите с такива кубчета, в Excel е вграден специален клиент. На руски се казва осева таблица(На графичен екрандостъпна е през менюто Данни -> осева таблица), а на английски - осева таблица. Съответно обектът, който този клиент представлява, се нарича PivotTable. Трябва да се отбележи, че може да работи не само с OLAP кубове, но и с обикновени данни в таблици или бази данни на Excel, но много функции се губят.

PivotTable и обектът PivotTable са софтуерни продукти от Panorama Software, които са придобити от Microsoft и интегрирани в Excel. Следователно работата с обекта PivotTable е малко по-различна от работата с други обекти на Excel. Често е трудно да разберете какво да правите. Затова се препоръчва активно да използвате записващото устройство за макроси, за да получавате подсказки. В същото време, когато работят с обобщени таблици, потребителите често трябва да извършват едни и същи повтарящи се операции, така че автоматизацията е необходима в много ситуации.

Как изглежда програмната работа с обобщена таблица?

Първото нещо, което трябва да направим, е да създадем PivotCache обект, който ще представлява набора от записи, извлечени от OLAP източника. Много условно този PivotCache обект може да се сравни с QueryTable. Само един обект на PivotCache може да се използва за обект на PivotTable. Обектът PivotCache се създава с помощта на метода Add() на колекцията PivotCaches:

Dim PC1 като PivotCache

Задайте PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches е стандартна колекция и от методите, които заслужават подробно разглеждане, само методът Add() може да бъде посочен в нея. Този метод приема два параметъра:

• Тип на източника- задължително, дефинира типа източник на данни за обобщената таблица. Можете да изберете да създадете обобщена таблица въз основа на диапазон в Excel, данни от база данни, външен източник на данни, друга обобщена таблица и т.н. На практика обикновено има смисъл да се използва OLAP само когато има много данни - съответно е необходимо специализирано външно хранилище (например Microsoft Analysis Services). В тази ситуация е избран xlExternal.
• Изходни данни- изисква се във всички случаи, освен когато стойността на първия параметър е xlExternal. Строго погледнато, той определя диапазона от данни, въз основа на който ще бъде създадена PivotTable. Обикновено приема обект Range.

Следващата задача е да конфигурирате параметрите на обекта PivotCache. Както вече споменахме, този обект е много подобен на QueryTable и неговият набор от свойства и методи е много подобен. Някои от най-важните свойства и методи са:

• ADOConnection- възможност за връщане на обект ADO Connection, който се създава автоматично за свързване към външен източник на данни. Използва се за допълнителни настройкисвойства на връзката.
• Връзка- работи точно по същия начин като свойството на обект QueryTable със същото име. Може да приеме низ за свързване, готов обект Recordset, текстов файл, Уеб заявка. Microsoft файлзаявка. Най-често, когато работите с OLAP, низът за свързване се записва директно (тъй като няма смисъл да получавате обект Recordset, например, за промяна на данни - източниците на данни OLAP почти винаги са само за четене). Например, настройването на това свойство за свързване към базата данни Foodmart (примерна база данни на Analysis Services) на сървъра LONDON може да изглежда така:

PC1.Connection = "OLEDB; Доставчик = MSOLAP.2; Източник на данни = LONDON1; Първоначален каталог = FoodMart 2000"

• Имоти Тип командаИ команден текстописват вида на командата, която се изпраща към сървъра на базата данни и текста на самата команда по същия начин. Например, за да получите достъп до куба Sales и да го кеширате изцяло на клиента, можете да използвате код като

• Имот LocalConnectionви позволява да се свържете с локален куб (*.cub файл), създаден от Excel. Разбира се, силно не се препоръчва използването на такива файлове за работа с "производствени" обеми от данни - само за целите на създаване на оформления и т.н.
• Имот Използвана паметвръща номера оперативна памет, използван от PivotCache. Ако обобщената таблица, базирана на този PivotCache, все още не е създадена и отворена, връща 0. Може да се използва за проверки дали вашето приложение ще работи на слаби клиенти.
• Имот OLAPвръща True, ако PivotCache е свързан към OLAP сървъра.
• OptimizeCache- възможност за оптимизиране на структурата на кеша. Първоначалното зареждане на данни ще отнеме повече време, но след това скоростта на работа може да се увеличи. За OLE DB източници не работи.

Останалите свойства на обекта PivotCache са същите като тези на обекта QueryTable и следователно няма да бъдат обсъждани тук.

Основният метод на обекта PivotCache е методът CreatePivotTable(). С помощта на този метод се извършва следващият етап - създаването на обобщена таблица (обект PivotTable). Този метод приема четири параметъра:

• TableDestinationе единственият задължителен параметър. Приема обект Range, в чийто горен ляв ъгъл ще бъде поставена обобщената таблица.
• име на таблица- Име на обобщена таблица. Ако не е посочено, името на формуляра "PivotTable1" ще се генерира автоматично.
• четете данни- ако е зададено True, тогава цялото съдържание на куба ще бъде автоматично кеширано. Трябва да сте много внимателни с този параметър, тъй като неправилното му използване може драстично да увеличи натоварването на клиента.
• Версия по подразбиране- Това свойство обикновено не е посочено. Позволява ви да посочите версията на обобщената таблица, която се създава. По подразбиране се използва най-новата версия.

Създаването на обобщена таблица в първата клетка на първия лист на работната книга може да изглежда така:

PC1.CreatePivotTable Range("A1")

Пивот таблицата е създадена, но веднага след създаването е празна. Той предоставя четири области, в които можете да поставите полета от източника (на графичния екран всичко това може да бъде конфигурирано или чрез прозореца Списък с полета на обобщена таблица- отваря се автоматично или с бутон Оформлениена последния екран на съветника за обобщена таблица):

• площ на колоната- съдържа онези измерения („раздел“, в който ще се анализират данните), членовете на които са по-малко;
• зона на линията- онези измерения, чиито членове са повече;
• област на страницата- онези измервания, чрез които е необходимо само да се филтрират (например да се покажат данни само за такъв и такъв регион или само за такава и такава година);
• област за данни- всъщност централната част на масата. Тези числени данни (например количеството продажби), които анализираме.

Трудно е да се разчита на потребителя да постави правилно елементи във всичките четири области. Освен това може да отнеме определено време. Поради това често е необходимо данните в обобщена таблица да се подреждат програмно. Тази операция се извършва с помощта на обекта CubeField. Основното свойство на този обект е Orientation, то определя къде ще бъде разположено това или онова поле. Например, нека поставим измерението Клиенти в областта на колоната:

PT1.CubeFields("").Ориентация = xlColumnField

След това - измерението Време към областта на низовете:

PT1.CubeFields(""). Ориентация = xlRowField

След това - измерението на продукта в областта на страницата:

PT1.CubeFields(""). Ориентация = xlPageField

И накрая, индикаторът (числови данни за анализ) Единични продажби:

PT1.CubeFields(".").Ориентация = xlDataField

Изберете документ от архива за преглед:

18,5 KBавтомобили.xls

14 KBстрани.xls

Excel pr.r. 1.docx

Библиотека
материали

Практическа работа 1

„Целта и интерфейсът на MS Excel“

Като завършите тази тема, вие ще:

1. Научете как да стартирате електронни таблици;

2. Фиксирайте основните понятия: клетка, ред, колона, адрес на клетка;

3. Научете как да въвеждате данни в клетка и да редактирате лентата с формули;

5. Как да изберете цели редове, колона, няколко клетки, разположени една до друга и цялата таблица.

Упражнение: Запознайте се с основните елементи на прозореца на MS Excel.

Стартирайте програмата Microsoft Excel. Погледнете внимателно прозореца на програмата.

Документи, създадени сEXCEL , са наречениработни тетрадки и имат разширението. XLS. Новата работна книга има три работни листа, наречени SHEET1, SHEET2 и SHEET3. Тези имена са изброени в разделите на листа в долната част на екрана. За да превключите към друг лист, щракнете върху името на този лист.

Действия в работния лист:

Преименуване на работен лист. Поставете показалеца на мишката върху гърба на работния лист и щракнете двукратно върху левия бутон или повикване контекстно менюи изберете Преименуване.Име на листа „ТРЕНИРОВКА“

Вмъкване на работен лист . Изберете раздела на листа „Лист 2“, пред който искате да вмъкнете нов лист, и като използвате контекстното менюпоставете нов лист и го наименувайте „Проба“ .

Изтриване на работен лист. Изберете раздела на листа "Лист 2" и с помощта на контекстното менюИзтрий .

Клетки и диапазони от клетки.

Работното пространство се състои от редове и колони. Редовете са номерирани от 1 до 65536. Колоните са обозначени с латински букви: A, B, C, ..., AA, AB, ..., IV, общо - 256. В пресечната точка на ред има клетка и колона. Всяка клетка има свой собствен адрес: името на колоната и номера на реда, в пресечната точка на който се намира. Например A1, CB234, P55.

За да работите с няколко клетки, е удобно да ги комбинирате в "диапазони".

Диапазон представлява клетки, подредени в правоъгълник. Например A3, A4, A5, B3, B4, B5. За да напишете диапазон, използвайте ": »: A3:B5

8:20 - всички клетки в редове от 8 до 20.

A:A - всички клетки в колона A.

N:R - всички клетки в колони от H до R.

Адресът на клетката може да включва името на работния лист: Sheet8!A3:B6.

2. Изберете клетки в Excel

Какво подчертаваме

Действия

една клетка

Кликнете върху него или преместете селекцията с клавишите със стрелки.

низ

Щракване върху номер на ред.

Колона

Щракване върху име на колона.

Обхват на клетката

Плъзнете показалеца на мишката от горния ляв ъгъл на диапазона в долния десен ъгъл.

Множество диапазони

Изберете първия, натиснете SCHIFT + F 8, изберете следващия.

Цяла маса

Щракнете върху бутона „Избор на всички“ (празен бутон вляво от имената на колоните)

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

Използвайте плъзгачите, за да определите колко реда има таблицата и какво е името на последната колона.
Внимание!!! За да стигнете бързо до края на таблицата хоризонтално или вертикално, трябва да натиснете клавишните комбинации: Ctrl+→ - край на колони или Ctrl+↓ - край на редове. Бързо връщане в началото на таблицата - Ctrl+Home.

В клетка A3 въведете адреса на последната колона на таблицата.

Колко реда има в таблицата? Въведете адреса на последния ред в клетка B3.

3. В EXCEL можете да въвеждате следните типове данни:

Числа.

Текст (като заглавия и обяснителен материал).

Функции (например сума, синус, корен).

Формули.

Данните се въвеждат в клетки. За да въведете данни, трябва да изберете желаната клетка. Има два начина за въвеждане на данни:

Просто щракнете върху клетка и въведете данните, които искате.

Щракнете върху клетката и в лентата за формули и въведете данни в лентата за формули.

Натиснете ENTER.

Въведете името си в клетка N35, центрирайте го в клетката и приложете удебелен шрифт.
Въведете текущата година в клетка C5, като използвате лентата за формули.

4. Променете данните.

Изберете клетка и натиснете F 2 и променете данните.

Изберете клетката и щракнете върху лентата с формули и променете данните там.

За да промените формулите, можете да използвате само втория метод.

Промяна на данните в клетка N35, добавете вашето фамилно име. използвайки някой от методите.

5. Въвеждане на формули.

Формулата е аритметичен или логически израз, чрез който се правят изчисления в таблица. Формулите се състоят от препратки към клетки, знаци за операции и функции. Ms EXCEL има много голям набор от вградени функции. С тяхна помощ можете да изчислите сумата или средната аритметична стойност от определен диапазон от клетки, да изчислите лихвите по депозитите и т.н.

Формулите винаги започват със знак за равенство. След въвеждане на формулата в съответната клетка се появява резултатът от изчислението, а самата формула се вижда в лентата с формули.

Действие

Примери

+

Допълнение

A1+B1

-

Изваждане

A1 - B2

*

Умножение

B3*C12

/

дивизия

A1 / B5

степенуване

A4 ^3

=, <,>,<=,>=,<>

знаци за връзка

A2

Можете да използвате скоби във формули, за да промените реда на действията.

Автоматично довършване.

Много удобен инструмент, който се използва само в MS EXCEL, е автоматичното довършване на съседни клетки. Например, трябва да въведете имената на месеците от годината в колона или ред. Това може да стане ръчно. Но има много по-удобен начин:

Въведете желания месец в първата клетка, например януари.

Изберете тази клетка. В долния десен ъгъл на рамката за избор има малък квадрат - дръжката за запълване.

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

Ако трябва да попълните някаква числова серия, тогава трябва да въведете първите две числа в съседните две клетки (например въведете 1 в A4 и 2 в B4), да изберете тези две клетки и да разтегнете областта за избор с маркер до желания размер.

Избран документ за преглед Excel pr.r. 2.docx

Библиотека
материали

Практическа работа 2

„Въвеждане на данни и формули в клетки на електронна таблица на MS Excel“

· Въведете данни в клетките различен тип: текстови, цифрови, формули.

Упражнение: Извършете необходимото въвеждане на данни и прости изчисления в таблицата.

Технология за изпълнение на задачата:

1. Стартирайте програматаMicrosoft Excel.

2. Към клеткатаА1 лист 2 въвежда се текст: "Година на основаване на училището". Коригирайте данните в клетката по начин, който знаете.

3. Към клеткатаВ 1 въведете числото - годината на основаване на училището (1971).

4. Към клеткатаC1 въведете число - текущата година (2016).

внимание! Моля, имайте предвид, че в MS Текст на Excelданните са подравнени вляво, а числата и датите са подравнени вдясно.

5. Маркирайте клеткаD1 , използвайте клавиатурата, за да въведете формулата за изчисляване на възрастта на училището:=C1-B1

внимание! Формулите винаги започват със знак за равенство«=». Адресите на клетките трябва да се въвеждат с латински букви без интервали. Адресите на клетките могат да се въвеждат във формули без използване на клавиатурата, а просто чрез кликване върху съответните клетки с мишката.

6. Изтриване на съдържанието на клеткаD1 и въведете отново формулата с помощта на мишката. В клеткаD1 знак за инсталиране«=» , след което щракнете върху клеткатаC1, забележете, че адресът на тази клетка се появи вD1, постави табела«–» и щракнете върху клеткатаB1 , щракнете(Влиза).

7. Към клеткатаA2 въведете текст"Моята възраст".

8. Към клеткатаB2 въведете годината си на раждане.

9. Към клеткатаC2 въведете текущата година.

10. Въведете клеткаD2 формула за изчисляване на вашата възраст през текущата година(=C2-B2).

11. Маркирайте клеткаC2. Въведете номер на следващата година. Забележете преизчисляването в клеткатаD2 стана автоматично.

12. Определете възрастта си през 2025 г. За да направите това, сменете годината в клеткатаC2 На2025.

Самостоятелна работа

Упражнение: Изчислете, като използвате ET, ще ви бъдат достатъчни 130 рубли, за да купите всички продукти, които майка ви е поръчала за вас, и ще бъде ли достатъчно да купите чипове за 25 рубли?

Технология на упражненията:
o В клетка A1 въведете „Не“.
o В клетки A2, A3 въведете „1“, „2“, изберете клетки A2, A3, посочете долния десен ъгъл (трябва да се появи черен кръст), разтегнете до клетка A6
o В клетка B1 въведете „Име“
o В клетка C1 въведете „Цена в рубли“
o В клетка D1 въведете „Количество“
o В клетка E1 въведете „Цена“ и т.н.
o В колоната „Цена“ се записват всички формули английски език!
o Във формулите вместо променливи се изписват имена на клетки.
o След натискане на Enter вместо формулата веднага се появява число - резултатът от изчислението

o Изчислете общата сума сами.

Покажете резултата на учителя!

Избран документ за преглед Excel pr.r. 3.docx

Библиотека
материали

Практическа работа 3

MS Excel. Създавайте и редактирайте документ с електронна таблица»

Като завършите тази тема, ще научите:

 Създаване и попълване на таблица с данни;

 Форматиране и редактиране на данни в клетка;

 Използвайте прости формули в таблицата;

 Копиране на формули.

Упражнение:

1. Създайте таблица, съдържаща разписанието на влаковете от гара Саратов до гара Самара. Обща форматаблица "График" е показана на фигурата.

2. Изберете клеткаA3 , променете думата "Златен" на "Страхотен" и натиснете клавишаВъведете .

3. Изберете клеткаA6 , кликнете два пъти с левия бутон върху него и заменете "Мрачно" с "Веселково"

4. Изберете клеткаA5 отидете на лентата с формули и заменете "Sennaya" със "Sennaya 1".

5. Допълнете таблицата "Разписание" с изчисления на времето на спиране на влаковете във всяко населено място. (вмъкнете колони) Изчислете общото време за спиране, общото време за пътуване, времето, необходимо на влака за пътуване от едно местностна друг.

Технология за изпълнение на задачата:

1. Преместете колоната Час на заминаване от колона C в колона D. За да направите това, изпълнете следните стъпки:

Изберете блок C1:C7; изберете отборРазрез .
Поставете курсора в клетка D1;
Изпълнете командатаПоставете ;
Подравнете ширината на колоната, за да съответства на размера на заглавката.;

2. Въведете текста "Паркиране" в клетка C1. Подравнете ширината на колоната, за да съответства на размера на заглавката.

3. Създайте формула, която изчислява времето за паркиране в населено място.

4. Трябва да копирате формулата, за да блокирате C4:C7, като използвате манипулатора за попълване. За да направите това, изпълнете следните стъпки:
Около активната клетка има рамка, в ъгъла на която има малък правоъгълник, хващайки го, разширявате формулата надолу до клетка C7.

5. Въведете в клетка E1 текста „Време за пътуване“. Подравнете ширината на колоната, за да съответства на размера на заглавката.

6. Създайте формула, която изчислява времето, необходимо на влак за пътуване от едно място до друго.

7. Промяна на числовия формат за блокове C2:C9 и E2:E9. За да направите това, изпълнете следните стъпки:

Изберете блока от клетки C2:C9;
Начало - Формат - Други числови формати - Време и зададени параметри (часове:минути) .

Натиснете клавишДобре .

8. Изчислете общото време за паркиране.
Изберете клетка C9;
Щракнете върху бутонаАвтосумиране на лентата с инструменти;
Потвърдете избора на клетъчен блок C3:C8 и натиснете клавишаВъведете .

9. Въведете текст в клетка B9. За да направите това, изпълнете следните стъпки:

Изберете клетка B9;
Въведете текста "Общо време за паркиране". Подравнете ширината на колоната, за да съответства на размера на заглавката.

10. Изтрийте съдържанието на клетка C3.

Изберете клетка C3;
Изпълнете командата от главното меню Редактиране - Изчистване или щракнетеИзтрий на клавиатура;
внимание! Компютърът автоматично преизчислява сумата в клетка C9!!!

Изпълнете командата Отказ или щракнете върху съответния бутон в лентата с инструменти.

11. Въведете текста „Общо време за пътуване“ в клетка D9.

12. Изчислете общото време за пътуване.

13. Оцветете таблицата и маркирайте границите на таблицата.

Самостоятелна работа

Изчислете с електронна таблицаExcelразходи за ученици, които ще отидат на екскурзия в друг град.

Избран документ за преглед Excel pr.r. 4.docx

Библиотека
материали

Практическа работа 4

"Връзки. Вградени функции на MS Excel".

Като завършите тази тема, ще научите:

Извършвайте операции по копиране, преместване и автоматично попълване на отделни клетки и диапазони.

Разграничете видовете връзки (абсолютни, относителни, смесени)

Използвайте вградените математически и статистически Функции на Excel.

MS Excel съдържа 320 вградени функции. Най-простият начинполучаване пълна информацияза всеки от тях е да използвате менютосправка . За удобство функциите в Excel са разделени на категории (математически, финансови, статистически и др.).
Извикването на всяка функция се състои от две части: името на функцията и аргументите в скоби.

Таблица. Вградени функции в Excel

* Написано без аргументи.

Таблица . Видове връзки

Упражнение.

1. Определена е цената на 1 kWh. ток и показания на електромера за предходни и текущи месеци. Необходимо е да се изчисли потреблението на електроенергия за изминалия период и цената на консумираната електроенергия.

Технология на работа:

1. Подравнете текста в клетките. Изберете клетки A3:E3. Начало - Форматиране - Формат на клетки - Подравняване: хоризонтално - центрирано, вертикално - центрирано, дисплей - пренасяне на думи.

2. В клетка A4 въведете: Кв. 1, в клетка А5 въведете: Кв. 2. Изберете клетки A4:A5 и използвайте маркера за автоматично попълване, за да попълните номерацията на апартаментите до 7 включително.

5. Попълнете клетки B4:C10 според картинката.

6. В клетка D4 въведете формулата за намиране на консумацията на електроенергия / енергия. И попълнете редовете по-долу с токена за автоматично довършване.

7. В клетка E4 въведете формулата за намиране на цената на електроенергията=D4*$B$1. И попълнете редовете по-долу с токена за автоматично довършване.

Забележка!
При автоматично попълване адресът на клетка B1 не се променя,
защото зададена е абсолютна референция.

8. В клетка A11 въведете текста „Статистически данни“, изберете клетки A11:B11 и щракнете върху бутона „Обединяване и центриране“ в лентата с инструменти.

9. В клетки A12:A15 въведете текста, показан на фигурата.

10. Кликнете върху клетка B12 и въведете математическа функцияSUM , за да направите това, щракнете в лентата с формулипо знакfx и изберете функция и потвърдете диапазона от клетки.

11. По същия начин функциите се задават в клетки B13:B15.

12. Извършихте изчисленията на лист 1, преименувайте го на електричество.

Самостоятелна работа

Упражнение 1:

Изчислете възрастта си от текущата година до 2030 г., като използвате токена за автоматично попълване. Годината на вашето раждане е абсолютна референция. Извършете изчисления върху лист 2. Преименувайте лист 2 на възраст.

Упражнение 2: Създайте таблица според примера.В клеткиаз5: Л12 ид13: Л14 трябва да бъдат формули: AVERAGE, COUNTIF, MAX, MIN. клеткиб3: з12 са попълнени с информация от вас.

Избран документ за преглед Excel pr.r. 5.docx

Библиотека
материали

Практическа работа 5

Като завършите тази тема, ще научите:

 Технологии за създаване на табличен документ;

 Задайте тип на използваните данни;

 Създаване на формули и правила за промяна на връзките в тях;

 Използвайте вградените статистически функции на Excel за изчисления.

Упражнение 1. Изчислете броя на живите дни.

Технология на работа:

1. Стартирайте приложението Excel.

2. В клетка A1 въведете датата си на раждане (ден, месец, година - 20.12.97). Коригирайте въвеждането на данни.

3. Вижте различни формати за дата(Начало - Формат на клетка - Други числови формати - Дата) . Преобразуване на дата във видЧЧ.ММ.ГГГГ. Пример, 14.03.2001 г

4. Разгледайте няколко вида формати на дата в клетка A1.

5. Въведете днешната дата в клетка A2.

6. В клетка A3 изчислете броя на изживените дни, като използвате формулата. Резултатът може да бъде представен като дата, в който случай трябва да се преобразува в числов тип.

Задача 2. Възрастта на учениците. По даден списък на учениците и тяхната рождена дата. Определете кой е роден по-рано (по-късно), определете кой е най-старият (най-младият).

Технология на работа:

1. Вземете файла Age. Чрез LAN: Отворете папката My Network Places -Шефе-Общи документи - 9 клас, намерете файла Възраст. Копирайте го по начин, който знаете или изтеглете от тази страница в долната част на приложението.

2. Изчислете възрастта на учениците. За да изчислите възрастта, трябва да използвате функциятаДНЕС изберете днешната текуща дата, датата на раждане на ученика се изважда от нея, след което от получената дата с помощта на функцията YEAR се извлича само годината от датата. От полученото число извадете 1900 - век и вземете възрастта на ученика. В клетка D3 напишете формулата=ГОДИНА(ДНЕС()-S3)-1900 . Резултатът може да бъде представен като дата, в който случай трябва да бъде преобразуванчислов тип.

3. Определете най-ранния рожден ден. В клетка C22 напишете формулата=MIN(C3:C21) ;

4. Определете най-малкия ученик. В клетка D22 напишете формулата=MIN(D3:D21) ;

5. Определете последния рожден ден. В клетка C23 напишете формулата=МАКС(C3:C21) ;

6. Определете най-възрастния ученик. В клетка D23 напишете формулата=МАКС(D3:D21) .

Самостоятелна работа:
Задача. Направете необходимите изчисления на растежа на учениците в различни мерни единици.

Избран документ за преглед Excel pr.r. 6.docx

Библиотека
материали

Практическа работа 6

MS Excel. Статистически функции” Част II.

Задача 3. Използвайки електронна таблица, обработете данните с помощта на статистически функции. Дава се информация за учениците от класа, включително среден успех за тримесечието, възраст (година на раждане) и пол. Определете средния резултат на момчетата, дела на отличните ученици сред момичетата и разликата в средния резултат на учениците от различни възрасти.

Решение:
Попълнете таблицата с първоначалните данни и извършете необходимите изчисления.Обърнете внимание на формата на стойностите в клетките "GPA" (числово) и "Дата на раждане" (дата)

Таблицата използва допълнителни колони, които са необходими за отговор на въпросите, поставени в задачата -ученическа възраст и е ученикътотличничка и момиче едновременно.
За изчисляване на възрастта е използвана следната формула (използвайки примера на клетка G4):

=ЦЯЛО ЧИСЛО((ДНЕС()-E4)/365,25)

Нека го коментираме. Датата на раждане на ученика се изважда от днешната дата. Така получаваме общия брой дни, изминали от раждането на ученика. Разделяйки това число на 365,25 (реалният брой дни в годината, 0,25 дни за нормална година се компенсира от високосна година), получаваме общия брой години на ученика; накрая, подчертавайки цялата част, - възрастта на ученика.

Дали едно момиче е отличен ученик се определя от формулата (използвайки примера на клетка H4):

=АКО(И(D4=5,F4="w");1,0)

Да започнем с основните изчисления.
На първо място е необходимо да се определи средният резултат на момичетата. Според дефиницията е необходимо общият резултат на момичетата да се раздели на техния брой. За тези цели можете да използвате съответните функции на процесора за електронни таблици.

=SUMIF(F4:F15;"W";D4:D15)/COUNTIF(F4:F15;"W")

Функцията SUMIF ви позволява да сумирате стойностите само в тези клетки от диапазона, които отговарят на зададения критерий (в нашия случай детето е момче). Функцията COUNTIF отчита броя на стойностите, които отговарят на посочените критерии. Така получаваме това, от което се нуждаем.
За да изчислим дела на отличните ученици сред всички момичета, ще припишем броя на отличните момичета към общия брой момичета (тук ще използваме набора от стойности от една от помощните колони):

=SUM(H4:H15)/COUNTIF(F4:F15;"W")

Накрая определяме разликата в средните резултати на деца от различни възрасти (ще използваме спомагателната колона в изчислениятаВъзраст ):

=ABS(SUMIF(G4:G15,15,D4:D15)/COUNTIF(G4:G15,15)-
SUMIF(G4:G15,16,D4:D15)/COUNTIF(G4:G15,16))

Моля, обърнете внимание, че форматът на данните в клетките G18:G20 е числов, два знака след десетичната запетая. Така проблемът е напълно решен. Фигурата показва резултатите от решението за даден набор от данни.

Избран документ за преглед Excel pr.r. 7.docx

Библиотека
материали

Практическа работа 7

„Създаване на диаграми с помощта на MS Excel“

Като завършите тази тема, ще научите:

 Извършване на операции за създаване на диаграми въз основа на данните, въведени в таблицата;

 Редактиране на данни от диаграма, техния тип и външен вид.

Какво е диаграма. Диаграмата е предназначена за графично представяне на данните. Линии, ленти, колони, сектори и други визуални елементи се използват за показване на числови данни, въведени в клетките на таблицата. Външният вид на диаграма зависи от нейния тип. Всички диаграми, с изключение на кръговата, имат две оси: хоризонталната е оста на категорията, а вертикалната е оста на стойността. При създаване на 3D диаграми се добавя трета ос - серийната ос. Често диаграмата съдържа елементи като мрежа, заглавия и легенда. Линиите на мрежата са разширения на деленията, открити върху осите, заглавията се използват за обяснение на отделните елементи на диаграмата и естеството на данните, представени върху нея, легендата помага да се идентифицират сериите от данни, представени на диаграмата. Има два начина за добавяне на диаграми: вграждане в текущия работен лист и добавяне на отделен лист с диаграма. В случай, че самата диаграма представлява интерес, тя се поставя на отделен лист. Ако трябва да видите едновременно диаграмата и данните, въз основа на които е изградена, тогава се създава вградена диаграма.

Диаграмата се запазва и отпечатва с работната книга.

След като диаграмата бъде генерирана, ще можете да правите промени в нея. Преди да извършите каквото и да е действие с елементите на диаграмата, изберете ги, като щракнете върху тях с левия бутон на мишката. След това извикайте контекстното меню с десния бутон на мишката или използвайте съответните бутониЛента с инструменти за диаграми .

Задача: Използвайте електронната таблица, за да начертаете функцията Y=3,5x–5. Където X приема стойности от -6 до 6 на стъпки от 1.

Технология на работа:

1. Стартирайте електронната таблица на Excel.

2. В клетка A1 въведете "X", в клетка B1 въведете "Y".

3. Изберете диапазона от клетки A1:B1, подравнете текста в клетките към центъра.

4. В клетка A2 въведете -6, а в клетка A3 въведете -5. Попълнете клетките по-долу с маркера за автоматично попълване до параметър 6.

5. В клетка B2 въведете формулата: =3,5*A2–5. Използвайте маркера за автоматично довършване, за да разширите тази формула до края на параметрите на данните.

6. Изберете цялата таблица, която сте създали, и задайте нейните външни и вътрешни граници.

7. Изберете заглавката на таблицата и запълнете вътрешната област.

8. Изберете останалите клетки на таблицата и запълнете вътрешната област с различен цвят.

9. Изберете цялата таблица. Изберете от лентата с менюта Вмъкване -Диаграма , Тип: Точково, Изглед: Точково с гладки извивки.

10. Преместете диаграмата под таблицата.

Самостоятелна работа:

Начертайте функцията y=грях(х)/ хна сегмента [-10;10] със стъпка 0,5.

Изведете графиката на функцията: а) y=x; б) y=x 3 ; в) y=-x на сегмента [-15;15] със стъпка 1.

Отворете файла "Градове" (отидете в мрежовата папка - 9 клас-Градове).

Изчислете цената на разговора без отстъпката (колона D) и цената на разговора с отстъпката (колона F).

За визуално представяне изградете две кръгови диаграми. (1- диаграма на цената на разговор без отстъпка; 2- диаграма на цената на разговор с отстъпка).

Избран документ за преглед Excel pr.r. 8.docx

Библиотека
материали

Практическа работа 8

ИЗГРАЖДАНЕ НА ГРАФИКИ И ЧЕРТЕЖИ СЪС СРЕДСТВА MS EXCEL

1. Изграждане на чертеж"ЧАДЪР"

Ето функциите, чиито графики са включени в това изображение:

y1= -1/18x 2 + 12, xО[-12;12]

г2 = ​​-1/8x 2 +6, xО[-4;4]

г3= -1/8(х+8) 2 + 6, хО[-12; -4]

г4= -1/8(х-8) 2 + 6, хО

г5= 2(х+3) 2 – 9, хн[-4;0]

г6=1.5(х+3) 2 – 10, xО[-4;0]

- Стартирайте MS EXCEL

- В клеткаA1 въведете обозначение на променливах

· - Попълнете диапазона от клетки A2:A26 с числа от -12 до 12.

За всяка графика на функцията ще въведем последователно формули. За y1= -1/8x 2 + 12, xО[-12;12], за
г2 = ​​-1/8x 2 +6, xО[-4;4] и т.н.

Редът на действията:

Поставете курсора на клеткаВ 1 и влезтеy1

Към клеткатаНА 2 въведете формулата=(-1/18)*A2^2 +12

Кликнете Въведете на клавиатурата

Стойността на функцията се изчислява автоматично.

Разширете формулата до клетка A26

По същия начин в клеткаC10 (тъй като намираме стойността на функцията само на интервала x от [-4; 4]) въвеждаме формулата за графиката на функциятаг2 = ​​-1/8x 2 +6. ДР.

Резултатът трябва да бъде следният ЕТ

След като всички стойности на функциите са изчислени, можетеизграждайте графики тезифункции

Изберете диапазона от клетки A1:G26

В лентата с инструменти изберетеМеню за вмъкване → Диаграма

В прозореца на съветника за диаграми изберетеМясто → Избор желания изглед→ Натиснете Добре .

Резултатът трябва да бъде следното изображение:

Задача за самостоятелна работа:

Начертайте графиките на функциите в една координатна система.x от -9 до 9 на стъпки от 1 . Вземете чертеж.

1. "Точки"

2. "Котка" Филтриране (избиране) на данни в таблицата ви позволява да показвате само онези редове, чието съдържание на клетки отговаря на определеното условие или няколко условия. За разлика от сортирането, данните не се пренареждат при филтриране, а се скриват само тези записи, които не отговарят на зададените критерии за избор.

Филтрирането на данни може да се извърши по два начина:използване на автоматичен филтър или разширен филтър.

За да използвате автоматичния филтър, трябва:

о задайте курсора вътре в таблицата;

о изберете екипДанни - Филтър - Автофилтър;

о разширете списъка на колоната, по която ще бъде направен изборът;

о изберете стойност или условие и задайте критериите за избор в диалоговия прозорецПерсонализиран автоматичен филтър.

За да възстановите всички редове на изходната таблица, изберете реда всички в падащия списък на филтъра или изберете командатаДанни – Филтър – Показване на всички.

За да отмените режима на филтриране, поставете курсора в таблицата и изберете отново командата от менютоДанни - Филтър - Автофилтър (махнете отметката).

Разширеният филтър ви позволява да създавате множество критерии за избор и да извършвате по-сложно филтриране на данни от електронни таблици, като зададете набор от критерии за избор в няколко колони. Филтрирането на записи с помощта на разширения филтър се извършва с помощта на командата от менютоДанни - Филтър - Разширен филтър.

Упражнение.

Създайте таблица според примера, показан на фигурата. Запазете го като Sort.xls.

Технология за изпълнение на задачата:

1. Отворете документа Sort.xls

2.

3. Изпълнете команда от менютоДанни - Сортиране.

4. Изберете първия ключ за сортиране "Възходящ" (Всички отдели в таблицата ще бъдат сортирани по азбучен ред).

Спомнете си, че всеки ден трябва да разпечатваме списък с артикули, останали в магазина (с ненулево салдо), но за да направим това, първо трябва да получим такъв списък, т.е. филтрирайте данните.

5. Задайте курсора на рамката вътре в таблицата с данни.

6. Изпълнете команда от менютоДанни - Филтър

7. Демаркирайте таблиците.

8. Всяка клетка от заглавката на таблицата вече има бутон „Стрелка надолу“, той не се отпечатва, което ви позволява да зададете критериите за филтър. Искаме да оставим всички записи с различен от нула остатък.

9. Кликнете върху бутона със стрелка, който се появява в колонатаОстаналата част от сумата . Ще се отвори списък за избор. Изберете линияСъстояние. Задайте условието: > 0. ЩракнетеДобре . Данните в таблицата ще бъдат филтрирани.

10. Вместо пълен списъкстоки, ще получим списък на стоките, продадени до момента.

11. Филтърът може да бъде подобрен. Ако допълнително изберете отдел, можете да получите списък с недоставени стоки по отдел.

12. За да видите отново списъка с всички непродадени стоки за всички отдели, трябва да изберете критерия "Всички" в списъка "Отдел".

13. За да не се объркате в отчетите си, въведете дата, която автоматично ще се променя според Системно времекомпютърФормули - Вмъкване на функция - Дата и час - Днес .

Самостоятелна работа

MS Excel. Статистически функции»

1 задача (обща) (2 точки).

Използвайки електронна таблица, обработете данните с помощта на статистически функции.
1. Дава се информация за учениците в клас (10 души), включително оценките за един месец по математика. Пребройте броя на петиците, четворките, двойките и тройките, намерете средния резултат на всеки ученик и средния резултат на цялата група. Създайте диаграма, илюстрираща процента на оценките в група.

2.1 задача (2 точки).

Четирима приятели пътуват с три вида транспорт: влак, самолет и лодка. Николай преплува 150 км с параход, измина 140 км с влак и прелетя 1100 км със самолет. Василий преплува 200 км с параход, измина 220 км с влак и прелетя 1160 км със самолет. Анатолий прелетя 1200 км със самолет, измина 110 км с влак и измина 125 км с параход. Мария измина 130 км с влак, прелетя 1500 км със самолет и измина 160 км с лодка.
Създайте електронна таблица въз основа на горните данни.

Добавете колона към таблицата, която ще показва общия брой километри, изминати от всяко от момчетата.

Изчислете общия брой километри, които момчетата изминаха с влака, летяха със самолета и плаваха с лодката (всеки вид транспорт поотделно).

Изчислете общия брой километри на всички приятели.

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

Определете средния брой километри за всички видове транспорт.

2.2 задача (2 точки).

Създайте таблица „Езера на Европа“, като използвате следните данни за площ (кв. км) и най-голяма дълбочина (м): Ладога 17 700 и 225; Онега 9510 и 110; Каспийско море 371 000 и 995; Венерн 5550 и 100; Чудское с Псков 3560 и 14; Балатон 591 и 11; Женева 581 и 310; Vättern 1900 и 119; Констанс 538 и 252; Mälaren 1140 и 64. Определете най-голямото и най-малкото езеро по отношение на площта, най-дълбокото и най-плиткото езеро.

2.3 задача (2 точки).

Създайте таблица „Реките на Европа“, като използвате следните данни за дължината (км) и площта на басейна (хиляди кв. км): Волга 3688 и 1350; Дунав 2850 и 817; Рейн 1330 и 224; Елба 1150 и 148; Висла 1090 и 198; Лоара 1020 и 120; Урал 2530 и 220; Дон 1870 и 422; Сена 780 и 79; Темза 340 и 15. Определете най-дългите и най-късите реки, изчислете общата площ на речните басейни, средната дължина на реките в европейската част на Русия.

3 задача (2 точки).

Банката записва навременността на плащанията по заеми, издадени на няколко организации. Известни са размерът на заема и вече платената от организацията сума. За длъжниците са определени санкции: ако компанията изплати заема с повече от 70 процента, тогава глобата ще бъде 10 процента от дълга, в противен случай глобата ще бъде 15 процента. Изчислете глобата за всяка организация, средната глоба, общата сума пари, която банката ще получи допълнително. Определете средната глоба на бюджетните организации.

Намерете материал за всеки урок,