Изменение содержимого ячейки

Лист

Колонтитули

Поля

Сторінка

Ориентация - книжна(по вертикалі), альбомна(по горизонталі)

Масштаб - зміна розмірів роздруку по відношенню до оригіналу в %

Размер бумаги - залежить від типу принтера.

Відступи від краю листа, центрування. Дуже швидко можна змінити поля в режимі Попереднього перегляду Þ Поля.

Це інформація, що виводиться над/под текстом кожної друкованої сторінки

Щоб написати свої - Кнопки Верхній(Нижний) Колонтитул. Кожен Колонтитул складається з трьох секцій: лівою, правою, центральною, кожна секція може містити свою інформацію:

Ø текст. (Кнопка Шрифт дозволяє вибрати параметри шрифту для виділеного тексту)

Ø номер сторінки

Ø кількість сторінок

Ø поточну на момент друку дату

Ø поточний на момент друку час

Ø ім'я робочої книги

Ø ім'я таблиці - ім'я листа

Дає можливість друкувати тільки виділену область даних, друкувати виділені рядки або стовпці як заголовки на кожній сторінці.

Засіб створення видів.

Часто доводиться створювати робочу книгу, яка використовується для зберігання різноманітної інформації. Щоб мати можливість роздрукувати декілька різних звітів з такої робочої книги, зручно використовувати засіб створення видів (Представления) Excel.

Засіб створення видів дозволяє присвоїти імена різним видам робочого листа і швидко перемикатися між ними. Представления може включати наступні параметри:

Ø установки друку, задані в діалоговому вікні Параметры страницы;

Ø приховані рядки і стовпці;

Ø параметри виведені на екран, визначені в діал.. вікні Параметры;

Ø посилання на вибрані комірки і діапазони комірок;

Ø адреса активної комірки;

Ø розміри вікон і їх положення;

Ø закріплені області.

Наприклад, можна визначити таке представлення робочого листа, в якому будуть приховані деякі стовпці або друкуватиметься тільки підсумковий діапазон комірок, або сторінка буде представлена в альбомній орієнтації і т.д.

Засіб створення видів вбудований в Excel і називається Представления. Для того, щоб створити Представление, треба надати листу той вигляд, якому необхідно присвоїти ім'я. Потім:

1. вибрати команду Вид Þ Представление;

2. клацнути по кнопці Добавить;

3. ввести ім'я вигляду в діалоговому вікні, що з'явилося.

Макроси. (Сервис Þ Макрос)

Макрос – це послідовність команд, які автоматизують деякі функції Excel, що дозволяє зробити роботу ефективнішою і уникнути багато помилок. Наприклад, можна створити макрос для форматування і друку місячного звіту. Після написання і відладки макрос можна викликати однією командою, щоб автоматично виконати процедуру, що віднімає багато часу.

У Excel використовуються макроси VBA (Visual Basic for Applications). VBA – це мова програмування з широкими можливостями. За допомогою макросів VBA можна виконувати різні операції:

Ø вставити рядок тексту або формулу;

Ø автоматизувати операції, що часто повторюються (наприклад, якщо підготувати місячний звіт і його процедура не дуже складна, то записати макрос, який можна буде запускати в наступні місяці)

Ø автоматизувати операції, що повторюються (якщо треба виконати деяку дію в 12 різних робочих книгах)

Ø створити нову команду (об'єднати декілька команд меню)

Ø створити нову кнопку на панелі інструментів

Ø і ін.

В більшості випадків можна записати свої дії у вигляді макросу, а потім виконати цей макрос, при цьому вам навіть не потрібно дивитися на текст програми. Таким чином займатися самою мовою VBA немає необхідності. Засіб запису макросів Excel допомагає перетворити ваші дії в текст програми на VBA.

Виберіть команду Сервис Þ Макрос Þ Начать запись. В результаті з'явиться діалогове вікно Запись макроса, в якому представлені наступні опції: ім'я макросу, поєднання клавіш, зберегти в (ця книга), опис макросу.

Щоб приступити до запису своїх дій, клацніть по кнопці ОК. З'явиться панель інструментів Остановить запись, який містить дві кнопки Остановить запись і Относительная ссылка. Коли ви закінчите запис макросу, виберіть команду Сервис Þ Макрос Þ Остановить запись або клацніть на кнопці Остановить запись, розташованому на панелі інструментів.

Зазвичай при записі макросу Excel зберігає точні адреси комірок, які ви вибираєте, тобто виконує абсолютний запис. Якщо ви клацніть по кнопці Относительная ссылка під час запису макросу, то Excel перейде з абсолютного (за умовчанням) у відносний режим запису.

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

Підбір параметра.

У разі потреби пошуку певного результату для комірок за допомогою підбору значення інших комірок можна скористатися вбудованою функцією EXCEL «Подбор параметра».

Якщо значення даної комірки є формулою, залежною від інших комірок, то викликавши пункт меню «Сервис»à «Подбор параметра» можемо ввести значення якої комірки необхідно підібрати, ввести необхідне значення для даної комірки і ввести адресу комірки, яку можна змінювати. В результаті виконання даної операції EXCEL, проаналізувавши формулу, обчислить необхідне значення змінної комірки.

Можливості зведених таблиць.

Зведена таблиця – це інтерактивна таблиця, яка упорядковує і групує великі об'єми даних в таблицях або базах даних. Створюючи зведену таблицю, користувач задає імена полів, які повинні бути поміщені в її рядках і стовпцях. Одним з основних достоїнств є швидкість при зміні розміщення полів.

Зведені таблиці створюються за допомогою Мастера сводных таблиц. В тому випадку, якщо дані, по яких необхідно скласти звіт розміщуються в декількох діапазонах, що мають однакову структуру і загальні заголовки стовпців і рядків, то їх можна згрупувати для аналізу в одній зведеній таблиці.

Для модифікації зведеної таблиці використовуються піктограми панелі інструментів Сводные таблицы і команди контекстного меню.

Діаграми.

Діаграма – це спосіб наочного представлення інформації, заданої у вигляді таблиці. При зміні даних таблиці негайно змінюються, відповідні ним діаграми.

Excel дозволяє створювати діаграми будь-яких типів: з областями; лінійчаті; гістограма; графік; кругова; кільце; пелюсткова; точкова; поверхня; бульбашкова; біржова; циліндрова; конічна; пірамідальна та інші.

Використання Мастера Диаграмм є найпростішим і надійнішим способом створення діаграм.

Основні поняття і терміни.

Ось - вісь категорій Х і вісь значень Y -по ним відкладаються точки даних і вони утворюють межі діаграми і мають шкалу для нанесення даних. Вісь Z використовується тільки в об'ємних діаграмах. Круги осей не мають. У лінійчатих осі переставлені.

Точка данных - один елемент даних.

Ряд данных - Сукупність точок даних На графіках крапки сполучені однією лінією.

Легенда - демонструє ключі (значки, кольори), які використовуються для розрізнення рядів даних. Назва кожного ряду даних використовується як мітка легенди.

Маркер данных- Елемент, який представляє точку даних на діаграмі. Прямокутники, сектори, символи - це маркери і вигляд їх залежить від типу діаграми.

Область побудови - прямокутна область, обмежена двома осями. Така область є і в кругових діаграмах.

Коли діаграма побудована, її можна модифікувати, використовуючи пункт меню Діаграма або команди контекстного меню.

Бази даних

На робочому полі кожен запис розташовується в окремому рядку. А для кожного поля виділяється окремий стовпець. Кожному полю і стовпцю присвоюються окремі імена. Стовпцям присвоюються унікальні імена полів які заносяться в перший рядок списку - рядок заголовків.

База данных- це область на робочому листі для зберігання даних списку. Пов'язані один з одним дані записуються в окремі рядки, кожному стовпцю відповідає своє поле списку з унікальним ім'ям.

Бази даних призначені для зберігання великих об'ємів структурованої інформації.

Основними задачами при роботі з Базами Даних є:

Ø Організація введення даних

Ø Перегляд даних

Ø Сортування і фільтрація даних в таблицях

Ø Підведення підсумків.

База даних в EXCEL --- це таблиця, спеціальним чином організована:

Перший рядок --- заголовки полів

Рядок --- запис БД

Стовпець --- поле запису (у стовпці знаходяться дані одного типу).

Весь інструмент роботи з БД в EXCEL зосереджений в одному пункті меню: Данные.

При виклику пунктів з меню Данные необхідно знаходитись в одному з елементів таблиці БД.

Введення і редагування даних (Данные --- Форма)

Для створення нової БД в EXCEL потрібно ввести рядок із заголовками стовпців зверху робочого листа і перший запис з реальними даними у відповідних полях. Після цього для введення і редагування решти частини записів можна використовувати Формуданих (діалогове вікно, в якому поряд з порожніми полями для введення інформації відображаються імена полів БД).

Таблиця БД допускає всі ті ж дії, що і звичайна таблиця. Тому ввести новий запис до БД можна звичайним для таблиць введенням даних в комірки. При такому введенні доведеться копіювати формулу з попереднього запису, копіювати формати комірок.

У вікні Форми відображаються всі поля одного запису БД

Зліва --- назви полів Поряд з назвами --- поля поточного запису в БД. Якщо поле обчислюване, то воно недоступне для редагування., оскільки відбувається автоматичне копіювання формул.

Переміщення по полях запису.

v TAB --- перейти до наступного поля.

v SHIFT + TAB --- повернутися до попереднього поля.

v Клацання мишкою по полю.

Переміщення по записах.

v Смуга прокрутки.

v Клавіші­¯ --- предыдущая/ подальший запис.

v CTRL +­ --- перший запис.

v CTRL +¯ --- останній запис.

v ENTER --- перехід на наступний запис

Хоча форми даних дійсно є зручним способом для включення нових записів, це не єдина можливість поповнення вашої бази даних.

При додаванні новому запису безпосередньо до таблиці БД без використання форми даних ви повинні дотримуватися особливої обережності, щоб не залишити який-небудь рядок у середині бази порожньою (завжди вводьте новий запис безпосередньо в наступному порожньому рядку під останнім існуючим записом БД). Ви також повинні бути уважними при введенні інформації в кожне поле (пам'ятайте, що рядок з іменами полів можна внести до заголовка робочого листа, щоб вона залишалася на екрані при прокрутці БД вниз).

Щоб знайти запис, який потребує редагування, можна скористатися кнопкою Критерии у формі даних. Після клацання на цій кнопці відобразиться порожня форма, в поля якої можна ввести відповідні критерії пошуку. Умови можна задавати по всіх полях, зокрема обчислюваних. Умови, задані по декількох полях об'єднуються логічним И.

Сортування записів в БД

Для зміни порядку записів в БД призначені спеціальні функції сортування EXCEL. Замість того щоб піклуватися про порядок, в якому ви додаєте записи в БД, просто покладетеся на команду ДанныеÞСортировка, за допомогою якої їх модно відобразити в потрібній вам послідовності. Цей порядок записів може часто мінятися, залежно від призначення інформації.

Щоб відсортувати БД просто вкажіть поле або поля (стовпець або стовпці) в БД, по значеннях яких повинне виконуватися сортування, і виберіть тип сортування – по убуванню або за збільшенням. При виборі порядку за збільшенням EXCEL упорядковує текстові значення в заданих полях в алфавітному порядку від А до Я, а числові значення від самого меншого до самого більшого (дати розташовуються в порядку від старіших до новіших). При виборі порядку по убуванню EXCEL упорядковує текстові значення в заданих полях в зворотному порядку.

Для кожної операції по сортуванню БД можна указувати до трьох полів. Зазвичай необхідність у вказівці більш одного поля виникає, якщо у вибраному для сортування полі присутні значення, що повторюються. В цьому випадку, задавши друге поле, ви можете вказати EXCEL, яким чином упорядкувати імена, що повторюються. Якщо ж ви поповнюєте сортування по полю з унікальною інформацією, то у вказівці другого поля для сортування немає ніякого сенсу.

Сортування за допомогою кнопок панелі інструментів проводиться по стовпцю з поточної коміркої.

Вибір записів з БД

У EXCEL процес вибору записів з БД називається фільтрацією записів, оскільки критерій, який застосовується до БД, фільтрує все, окрім задовольняючих йому записів. Записи, які залишаються після фільтрації БД, утворюють підмножину БД.

Автофільтр

За допомогою функції Автофильтр EXCEL максимально спрощує процес відбору потрібних записів. Після включення автофільтрації за допомогою команди Данные® Фильтр®Автофильтр поряд з кожною назвою поля БД EXCEL помістить кнопку списку, що розкривається. Після клацання на такій кнопці з'являється список, що розкривається, містить значення (Все),(Первые 10...),(Условие...), а також значення всіх елементів відповідного поля (у порядку зростання).

Щоб видалити фільтр і знову відобразити всі записи в БД, виберіть зверху списку значення ( Все ).

Щоб створити фільтр, який визначає діапазон значень для вибору або декілька логічних умов, в списку відповідного поля, що розкривається, потрібно вибрати значення ( Условие ) і задати умови в діалоговому вікні Призначений для користувача автофільтр. Можна вибрати до двох умов в одному полі, зв'язаних И або Или. Якщо задаються умови в декількох полях, то вони працюватимуть за принципом И.

Щоб видалити кнопки списків, що розкриваються, і відобразити всі записи БД, відключите автофільтрацію, повторно вибравши команду Данные ® Фильтр ®А втофильтр ®.

Розширений фільтр
(Данные® Фильтр® Расширенный фильтр)

Розширений фільтр дозволяє використовувати для пошуку складніші критерії, ніж в призначених для користувача автофільтра, і об'єднувати їх в довільних поєднаннях як по И, так і по ИЛИ.

При роботі розширений фільтр спирається на три області

v Область бази даних

v Область критеріїв пошуку. Ця область формується з рядка заголовків полів, які будуть ключовими при відборі записів, і рядків критеріїв. У вільне місце на листі заголовки копіюються, щоб уникнути неточності. Якщо критерії знаходяться в одному рядку, то вони працюють за принципом И, якщо в різних, то за принципом ИЛИ.

v Цільова область. У цю область копіюються записи, що задовольняють заданим критеріям. Якщо встановлений прапорець «Фільтрувати записи на місці», то ця область не встановлюється..

У прикладі відбираються записи, що містять прізвища, що починаються з букви В і далі по алфавіту або оклад більше 300.

Підсумки. (Данные®Итоги)

Часто буває потрібним знати проміжні суми для тих або інших таблиць. Можна обчислити суму по діапазону комірок, але зручніше скористатися інструментом Итоги. Заздалегідь необхідно відсортувати таблицю по стовпцю, при зміні якого потрібно отримати проміжний результат.

Знак «-» означає, що можна заховати рядки, що відносяться до групи, залишивши тільки результат.

1. Дважды щелкните ячейку, содержимое которой необходимо изменить.
2. Измените содержимое ячейки.
3. Чтобы ввести или отменить изменения, нажмите клавишу ENTER или ESC.

— Чтобы включить или выключить редактирование непосредственно в ячейке, выберите команду Параметры в меню Сервис, выберите закладку Правка, а затем установите или снимите флажок "Правка прямо в ячейке". Если флажок "Правка прямо в ячейке" снят, формулы можно изменять только в строке формул.

— Чтобы переместить курсор в конец содержимого ячейки, выделите эту ячейку и нажмите клавишу F2.

1. Чтобы удалить содержимое ячейки, переведите в неё курсор мышью или клавишами и нажмите Delete.

Присваивание имен ячейкам рабочей книги

Для ссылки на ячейки в столбцах и строках можно использовать заголовки этих столбцов и строк листа. Также для представления ячеек, диапазонов ячеек, формул или констант (Константа. Постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Выражение и результат вычисления выражения константами не являются.) можно создавать имена.

Имя. Слово или строка знаков, представляющее ячейку, диапазон ячеек, формулу или константу. Понятные имена, такие как «Продукты», используют для ссылок на диапазоны, названия которых трудно запомнить, например, Продажи!C20:C30. Заголовки можно использовать в формулах, содержащих ссылки на данные на том же листе; для представления группы ячеек, находящейся на другом листе, этой группе следует присвоить имя.

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

Для присвоения имени ячейке или группе ячеек

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

2. Щелкните поле Имя, расположенное у левого края строки формул (Строка формул. Панель в верхней части окна Microsoft Excel, которая используется для ввода или изменения значений или формул в ячейках или на диаграммах. На ней отображается константа или формула, содержащаяся в активной ячейке.).

3. Введите имя для назначения этим ячейкам.

4. Нажмите клавишу ENTER.

При изменении содержимого ячейки ей нельзя присвоить имя.

Тема 4: Блоки (диапазоны).

1. Блок – это прямоугольная группа связанных ячеек. Другое его название, встречающееся в литературе, - диапазон. Частным случаем блока является один столбец или одна строка или даже одна ячейка. Обозначается блок через адреса левой верхней и правой нижней ячейки через “:”, например А1:Е6. В функциях, как Вы помните, в качестве аргумента обычно используется блок. Например, обычно формат устанавливается для группы ячеек, которые выделяются как блок, а затем для них всех задается формат.

Выделение блока. Обычно блок выделяется с помощью мыши. Для этого надо подвести указатель мыши к левой верхней ячейке блока, нажать левую клавишу мыши и, не отпуская, перетащить указатель мыши к правой нижней ячейке блока. Блок выделится цветом. Блок также можно выделить задав его координаты в строке формул в поле “Имя”.

Для выделения строки надо щелкнуть мышью на номер строки или нажать Shift+пробел.

Для выделения столбца надо щелкнуть мышью на название столбца или нажать Ctrl+пробел.

Задание имен ячейкам и блокам. Хотя для работы достаточно использовать координаты, но часто бывает удобнее давать блокам (в т.ч. ячейкам) запоминающиеся имена. Для этого надо выделить блок и ввести его имя в поле “Имя” в строке формул. Чтобы просмотреть список имен надо раскрыть список поля “Имя”.

Эти операции можно осуществлять через “Меню”, Вставка, Имя. Удаление имени лучше осуществлять через Меню.

Тема 5: Копирование формул, фиксация ячеек.

Редактирование формул. Нередко в процессе работы надо исправить или дополнить формулу. Для этого выделяется соответствующая ячейка и нажимается клавиша F2 – Excel переходит в режим редактирования. Теперь в строке формул можно изменить содержимое выражения, используя клавиши: Home, End, ß, à, Del и Забой (BackSpace). Т.е. можно работать как в обычном редакторе, только однострочном. Выход из этого режима клавишей Enter.

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

Копирование формул. При копировании формулы, она изменяется таким образом, чтобы соответствовать расположению ячейки, в которую она копируется. Например, если мы копируем ячейку С4 в Е4 и она содержит =С2*С3, то в результате копирования мы получим =Е2*Е3. Аналогично, если мы копируем по столбцу, например С4 в С14, то получим =С12*С13. Т.е. при копировании по строке изменяются буквы, а по столбцу – номера. При копировании с изменением и столбца и строки изменяются и буквы и номера. Если адрес ячейки при копировании не должен изменяться, или может изменяться только по столбцу или только по строке, то применяется фиксация ячейки, Например: $D$12, $D12, D$12

Сводная таблица.

Сводная таблица — это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. Меняя местами строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы можно осуществить фильтрацию данных, а также отобразить детальные данные области.

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

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

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

Лекция № 2 (10) Excel.

Тема 6: Отображение таблицы в Excel. Раздел Формат.

Типы данных в программировании в Excel.

Тема 7: Разделы Вставка и Правка.

Тема 8: Построение диаграмм.

Тема 6: Отображение таблицы в Excel. Раздел Формат.

Таблица Excel отображается на одном Листе. Часто на Листе располагается несколько таблиц. Основным элементом таблицы является ячейка. Они объединяются в строки, столбцы и диапазоны (блоки). Команда Формат ячейки включает следующие вкладки: Число, Выравнивание, Шрифт, Граница, Вид, Защита.

Вкладка Число задаёт формат данных в ячейке:

- Общий

- Числовой

- Денежный

- Финансовый

- Дата

- Время

- Процентный

- Дробный

- Экспоненциальный

- Текстовый

- Дополнительный (все форматы)

Реально в Excel используется только два типа данных – Число и Текст. Форматы Дата и Время являются частным случаем типа данных Число. Формат Общий означает, что тип и формат данных Excel должна выбрать сама. И, разумеется, уж она выберет!!! Поэтому обычно применение пользователем этого типа данных является ошибкой (бальной!).

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

Форматы Денежный и Финансовый являются разновидностью формата Числовой, но позволяют указать единицу измерения (р., $ и т.д.). Обычно вместо них рекомендуется использовать формат Числовой, а единицу измерения задавать в шапке таблицы.

Форматы Дата и Время являются фактически также разновидностью формата Числовой, но позволяют выводить на экран значения в удобном для пользователя виде. Следует обратить внимание на наличие объединённого формата Дата и Время. Подробнее этот вопрос будет раскрыт при изучении функций Даты и Времени.

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

Формат Текстовый является фактически комментарием. Он не несёт для Excel никакой реальной информации, при использовании в арифметических операциях считается равным нулю и, главным образом, служит для пояснения содержимого таблицы пользователю. Содержимое ячейки автоматически считается текстом, если оно начинается с буквы (включая знак пробела). Если текст начинается с цифры, то перед ним надо поставить апостроф – < ' >. В самой ячейке апостроф не будет отображаться, в ней будет виден только сам текст, а в строке формул апостроф будет виден.

Формат Дополнительный (все форматы) включает несколько дополнительных (специальных) форматов, полезных при экспорте и импорте из баз данных. Указание " (все форматы) " является не вполне корректным.

Вкладка Выравнивание задаёт размещение данных в ячейке. Она включает опции:

- Выравнивание по горизонтали,

- Выравнивание по вертикали,

- Ориентация,

- Направление текста,

а также флажки Отображение:

o переносить по словам,

o автоподбор ширины,

o объединение ячеек.

Опция Выравнивание по горизонтали аналогична Word'у, но кроме 4-х имеющихся там вариантов включает ещё: по значению, с заполнением, по центру выделения и распределённый.

Вариант В ыравнивание по значению позволяет по расположению данных в ячейке определить их тип. Напоминаю, что в Excel фактически существует 2 типа данных – текстовый и числовой. Текстовый тип данных (формат Текстовый) выравнивается по левому краю, а Числовой (все остальные форматы) по правому.

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

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

Вариант Распределённый позволяет распределить данное по всей ширине ячейки. В отличие от Выравнивание по ширине это справедливо и для последней (единственной) строке абзаца. Разумеется данное должно представлять собой набор слов. Флажок Распределять по ширине отменяет Отступ. Параметр Отступ задаёт величину отступа абзаца справа, слева или с обеих сторон.

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

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

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

Вкладка Шрифт задаёт параметры шрифта и является частным случаем аналогичной вкладки Word'а. Флажок Обычный задаёт параметры шрифта по умолчанию (Arial, обычный, 10). Эти параметры можно изменить через Сервис, Параметры, Общие, Стандартный шрифт.

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

Вкладка Вид – это заливка ячеек.

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

Команда Формат ячейки включает:

- Высота – позволяет задать высоту строки числом;

- Автоподбор высоты,

- Скрыть,

- Отобразить.

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

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

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

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

В объемной диаграмме ось Z направлена вертикально, а ось Х (расстояние) и ось Y (ширина) представляют две стороны основания диаграммы.

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

- Ряды данных. На графике в качестве ряда данных отображаются значения, задаваемые в виде блока, обычно строки или столбца. Эти данные отображаются в виде графических элементов диаграммы, зависящих от ее типа, например полос, секторов, линий и т.п. На одной диаграмме может быть отображено несколько рядов данных, например: Цена, Количество и Стоимость. В этом случае желательно, чтобы значения разных рядов не отличались друг от друга на несколько порядков, иначе малые значения будут не видны.

- Категории – это число элементов в ряду данных. Во многих случаях все ряды данных имеют одинаковое количество элементов, а значит в диаграмме используется только одна категория. Но в некоторых диаграммах используется несколько категорий.

- Легенда. Определяет отдельные элементы диаграммы.

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

В Excel можно строить диаграммы разных типов. Выбор типа диаграммы зависит как от характера данных, так и от вида их анализа. Большинство типов диаграмм позволяют представлять данные как в плоском, так и в объемном виде. Объемные диаграммы выглядят более эффектно и позволяют подчеркнуть различие между разными наборами данных. Но 3-хмерное представление данных часто затрудняет их анализ. Основными типами диаграмм являются:

· Гистограмма

· Линейчатая

· График

· Круговая

· Точечная

· С областями

· Кольцевая

· Лепестковая

· Поверхность

· Пузырьковая

· Биржевая

· Коническая, Цилиндрическая и Пирамидальная

Гистограмма показывает изменение данных за определенный период времени и иллюстрирует соотношение отдельных значений данных. Категории располагаются по горизонтали, а значения по вертикали. Таким образом уделяется большее внимание изменениям во времени. Гистограмма с накоплением демонстрирует вклад отдельных элементов в общую сумму. В трехмерной гистограмме сравнение данных производится по двум осям. Показанная на рисунке трехмерная диаграмма позволяет сравнить объемы продаж в Европе за каждый квартал с объемами продаж в двух других регионах. Используется для иллюстрации соотношения между отдельными значениями ряда.

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

График отражает тенденции изменения данных за равные промежутки времени.

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

Точечная диаграмма отображает взаимосвязь между числовыми значениями в нескольких рядах и представляет две группы чисел в виде одного ряда точек в координатах xy. Эта диаграмма отображает нечетные интервалы (или кластеры) данных и часто используется для представления данных научного характера. При подготовке данных следует расположить в одной строке или столбце все значения переменной x, а соответствующие значения y - в смежных строках или столбцах. Похожа на график, но удобна для анализа одновременно нескольких рядов данных.

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

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

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

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

Пузырьковая диаграмма является разновидностью точечной диаграммы. Размер маркера данных указывает значение третьей переменной. При подготовке данных следует расположить в одной строке или столбце все значения переменной x, а соответствующие значения y - в смежных строках или столбцах.

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

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

Создание диаграммы

Диаграммы связаны с данными листа, на основе которых они были созданы, и изменяются каждый раз, когда изменяются данные на листе.

Можно создать либо внедренную диаграмму, либо лист диаграммы.

1. Выделите ячейки, содержащие данные, которые должны быть отражены на диаграмме.

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

2. Нажмите кнопку Мастер диаграмм.

3. Следуйте инструкциям мастера.

· Создание стандартной диаграммы за один шаг

Типом диаграммы, использующимся по умолчанию в Microsoft Excel, является гистограмма.

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

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

Добавление рисунка к элементу диаграммы

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

1 Выберите элемент диаграммы, к которому нужно добавить рисунок.

2 Нажмите на стрелку рядом с кнопкой Цвет заливки, выберите цвет заливки, а затем — вкладку Рисунок.

3 Укажите нужный рисунок.

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

5 Укажите нужные параметры на вкладке Рисунок.

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

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

Лекция № 3 (11) Функции Excel.

Тема № 9. Функции Excel.

Функции даты и времени. Microsoft Excel запоминает дату в виде числа, называемого значением, а время – в виде десятичной части этого значения (время является частью даты). Даты и значения времени представляются числами; чтобы их можно складывать и вычитать, а также использовать в других вычислениях. Например, чтобы определить число дней между двумя датами, можно вычесть одну дату из другой. При изменении формата ячеек, содержащих компоненты даты и времени, на основной формат можно отобразить дату или время в виде числа с десятичной точкой. Значение этой функции не обновляется непрерывно.Microsoft Excel для Windows и Microsoft Excel для Macintosh используют по умолчанию различные системы дат.

Microsoft Excel поддерживает обе системы дат: система дат 1900 и 1904. По умолчанию, Microsoft Excel для Windows использует систему дат 1900. Чтобы воспользоваться системой дат 1904, выберите команду Параметры в меню Сервис, а затем — вкладку Вычисления. Установите флажок Система дат 1904.

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

Система дат	Первая дата	Последняя дата
	1 января 1900 г. (значение 1)	31 декабря 9999 г. (значение 2958525)
	2 января 1904 г. (значение 1)	31 декабря 9999 г. (значение 2957063)

Чтобы изменить систему дат, установите или снимите флажок система дат 1904 на вкладке Вычисления в пункте Параметры (меню Сервис).

В числовом формате даты цифры справа от десятичной запятой представляют время; цифры слева от десятичной запятой представляют дату. Например, в системе дат 1900, дата в числовом формате 367,5 представляет код даты и времени: соответствующий 12 часам дня 1 января 1901 года.

¨ ТДАТА – Возвращает текущую дату и время в числовом формате.

Синтаксис: ТДАТА()

Функция ТДАТА меняет свое значение только при расчете листа Excel или при выполнении макроса, содержащего эту функцию.

§ ДАТА Возвращает дату в числовом формате для заданной даты. Для получения более подробной информации о числовом формате даты см. ТДАТА.

Синтаксис: ДАТА(год; месяц; день)

Год - это число от 1900 до 2078.

Месяц - это число, представляющее номер месяца в году. Если месяц больше 12, то это число прибавляется к первому месяцу указанного года. Например, ДАТА(90;14;2) возвращает числовой формат даты 2 февраля 1991 года.

День - это число, представляющее номер дня в месяце. Если день больше числа дней в указанном месяце, то это число прибавляется к первому дню указанного месяца. Например, ДАТА(91;1;35) возвращает числовой формат даты 4 февраля 1991 года.

· Функция ДАТА наиболее полезна в формулах, в которых год, месяц и день являются формулами, а не константами.

¨ ДАТАЗНАЧ – Возвращает числовой формат даты, представленной в виде текста. Функция ДАТАЗНАЧ используется для преобразования даты из текстового представления в числовой формат.

Синтаксис: ДАТАЗНАЧ(дата_как_текст)

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

Примеры ДАТАЗНАЧ("22.8.55") равняется 20323

¨ ДЕНЬ – Возвращает номер дня в месяце для даты в числовом формате. День возвращается как целое число диапазоне от 1 до 31.

Синтаксис: ДЕНЬ(дата_в_числовом_формате)

¨ МЕСЯЦ – Возвращает месяц, соответствующий аргументу дата_в_числовом_формате. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь).

Синтаксис: МЕСЯЦ(дата_в_числовом_формате)

¨ СЕГОДНЯ – Возвращает текущую дату в числовом формате. Числовой формат даты - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Синтаксис: СЕГОДНЯ()

¨ ДЕНЬНЕД – Возвращает день недели, соответствующий аргументу дата_в_число­вом_формате. День недели определяется как целое в интервале от 1 (Воскресенье) до 7 (Суббота).

Синтаксис: ДЕНЬНЕД(дата_в_числовом_формате;тип)

Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Аргумент дата_в_числовом_формате можно задать как текст, например, как "15 Апр 1993" или "4.15.93", а не как число.

Тип - это число, которое определяет тип возвращаемого значения.

Тип Возвращаемое число

1 или опущен Число от 1 (Воскресенье) до 7 (Суббота). Аналогично предыдущей версии Microsoft Excel.

2 Число от 1 (Понедельник) до 7 (Воскресенье)

3 Число от 0 (Понедельник) до 6 (Воскресенье)

Можно использовать функцию ТЕКСТ, чтобы преобразовать значение в нужный числовой формат при использовании системы дат 1900:

ТЕКСТ("4.16.90"; "ДДДД") равняется Понедельник

¨ ГОД – Возвращает год, соответствующий аргументу дата_в_числовом_формате. Год определяется как целое в интервале 1900-9999.

Синтаксис: ГОД(дата_в_числовом_формате)

Дата_в_числовом_формате - это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Можно задать аргумент дата_в_числовом_формате как текст, например, как "19 Мар 1955" или "19.3.55", а не как число. Текст автоматически преобразуется в дату в числовом формате.

¨ ДОЛЯГОДА – Возвращает долю года, которую составляет количество дней между двумя датами (начальной и конечной). Функция ДОЛЯГОДА служит для определения доли общегодовых гонораров или обязательств, приходящихся на указанный период.

Если эта функция недоступна, следует установить надстройку Пакет Анализа, а затем подключить его с помощью команды Надстройки... меню Сервис.

Синтаксис: ДОЛЯГОДА(нач_дата;кон_дата;базис)

Нач_дата - это дата, которая соответствует начальной дате.

Кон_дата - это дата, которая соответствует конечной дате.

Базис - это тип используемого способа вычисления дня.

Базис Способ вычисления дня

0 или опущен US (NASD) 30/360

1 Фактический/фактический

2 Фактический/360

3 Фактический/365

4 Европейский 30/360

· Все аргументы усекаются до целых.

· Если нач_дата или кон_дата не являются допустимой датой, то функция ДОЛЯГОДА возвращает значение ошибки #ЧИСЛО!.

· Если базис < 0 или если базис > 4, то функция то функция ДОЛЯГОДА возвращает значение ошибки #ЧИСЛО!.

¨ ЧАС – Возвращает час, соответствующий заданной дате в числовом формате. Час определяется как целое в интервале от 0 (12:00 AM) до 23 (11:00 PM).

Синтаксис: ЧАС(дата_в_числовом_формате)

¨ МИНУТЫ – Возвращает минуты, соответствующие аргументу дата_в_чис­ло­вом_фор­мате. Минуты определяются как целое в интервале от 0 до 59.

Синтаксис: МИНУТЫ(дата_в_числовом_формате)

¨ СЕКУНДЫ – Возвращает секунды, соответствующее аргументу дата_в_числовом_фор­мате. Секунды определяются как целое в интервале от 0 (нуля) до 59. Функция СЕКУНДЫ используется, чтобы получить секунды момента времени, заданного датой в числовом формате.

Синтаксис: СЕКУНДЫ(дата_в_числовом_формате)

¨ ВРЕМЯ – Возвращает дату в числовом формате для заданного времени. Дата в числовом формате, возвращаемая функцией ВРЕМЯ - это десятичная дробь в интервале от 0 до 0,99999999, представляющая время от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера).

Синтаксис: ВРЕМЯ(часы;минуты;секунды)

Часы - это число от 0 (нуля) до 23, представляющее час.

Минуты - это число от 0 до 59, представляющее минуту.

Секунды - это число от 0 до 59, представляющее секунду.

Примеры

ВРЕМЯ(12; 0; 0) равняется 0,5, что эквивалентно 12:00:00

ВРЕМЯ(16; 48; 10) равняется 0,700115741, что эквивалентно 16:48:10

ТЕКСТ(ВРЕМЯ(23; 18; 14); "ЧЧ:ММ:СС") равняется "23:18:14"

¨ ВРЕМЗНАЧ – Возвращает числовой формат для времени, представленного аргументом время_как_текст. Время в числовом формате - это десятичная дробь в интервале от 0 до 0,99999999, представляющая время от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера). Функция ВРЕМЗНАЧ используется для преобразования времени, представленного в виде текста, в числовой формат времени.

Синтаксис: ВРЕМЗНАЧ(время_как_текст)

Время_как_текст - это текстовая строка, содержащая время в любом формате, допустимом в Microsoft Excel. Информация о дате в аргументе время_как_текст игнорируется.

Функции обработки текста

С помощью функций обработки текста можно с помощью формул производить действия над строкам текста — например, изменить регистр или определить длину строки. Можно также объединить несколько строк в одну. В примере ниже показано, как с помощью функций СЕГОДНЯ и ТЕКСТ создать сообщение, содержащее текущую дату и привести его к виду "дд-ммм-гг". ="Балансовый отчет от "&ТЕКСТ(СЕГОДНЯ(),"дд-мм-гг")

Список функций обработки текста:

СИМВОЛ

ПЕЧСИМВ

КОДСИМВ

СЦЕПИТЬ

РУБЛЬ

СОВПАД

НАЙТИ

ФИКСИРОВАННЫЙ

ЛЕВСИМВ

ДЛСТР

СТРОЧН

ПСТР

ПРОПНАЧ

ЗАМЕНИТЬ

ПОВТОР

ПРАВСИМВ

ПОИСК

ПОДСТАВИТЬ

Т

ТЕКСТ

СЖПРОБЕЛЫ

ПРОПИСН

ЗНАЧЕН

СИМВОЛ – Возвращает символ с заданным кодом. Функция СИМВОЛ используется, чтобы преобразовать числовые коды символов, которые получены из файлов с других компьютеров, в символы данного компьютера.

Операционная среда Таблица символов

Macintosh Таблица символов Macintosh

Windows ANSI

Синтаксис – СИМВОЛ(число)

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

Дата добавления: 2014-01-04