Якщо для побудованої діаграми на аркуші з'явилися нові дані, які потрібно додати, то можна просто виділити діапазон з новою інформацією, скопіювати його (Ctrl + C) і потім вставити прямо в діаграму (Ctrl + V).

Миттєве заповнення (Flash Fill)

Припустимо, у вас є список повних ПІБ (Іванов Іван Іванович), які вам треба перетворити в скорочені (Іванов І.І.). Щоб зробити це, потрібно просто почати писати бажаний текст в сусідньому стовпці вручну. На другий чи третій рядку Excel спробує вгадати наші дії і виконає подальшу обробку автоматично. Залишиться тільки натиснути клавішу Enter для підтвердження, і всі імена будуть перетворені миттєво. Подібним чином можна отримувати імена з email, склеювати ПІБ з фрагментів і так далі.

Копіювання без порушення форматів

Ви, швидше за все, знаєте про чарівний маркере автозаповнення. Це тонкий чорний хрест в правому нижньому кутку комірки, потягнувши за який можна скопіювати вміст комірки або формулу відразу на кілька осередків. Однак є один неприємний нюанс: таке копіювання часто порушує дизайн таблиці, так як копіюється не тільки формула, але і формат осередку. Цього можна уникнути. Відразу після того, як потягнули за чорний хрест, натисніть на смарт-тег – спеціальний значок, що з'являється в правому нижньому кутку скопійованої області.

Якщо вибрати опцію «Копіювати тільки значення» (Fill Without Formatting), то Excel скопіює вашу формулу без формату і не буде псувати оформлення.

Відображення даних з таблиці Excel на мапі

В Excel можна швидко відобразити на інтерактивній карті ваші годинне, наприклад продажу по містах. Для цього потрібно перейти в «Магазин додатків» (Office Store) на вкладці «Вставка» (Insert) і встановити звідти плагін «Карти Bing» (Bing Maps).

Після додавання модуля його можна вибрати в списку, що випадає «Особисте» (My Apps) на вкладці «Вставка» (Insert) і помістити на ваш робочий лист. Чи залишиться виділити ваші осередки з даними і натиснути на кнопку Show Locations в модулі карти, щоб побачити наші дані на ній. При бажанні в налаштуваннях плагіна можна вибрати тип діаграми і кольору для відображення.

Швидкий перехід до потрібного листу

Якщо у файлі кількість робочих листів перевалило за 10, то орієнтуватися в них стає важкувато. Клацніть правою кнопкою миші по будь-якій з кнопок прокрутки ярличків листів в лівому нижньому кутку екрану. З'явиться зміст, і на будь-який потрібний лист можна буде перейти миттєво.

Перетворення рядків у стовпці і назад

Якщо вам коли-небудь доводилося руками перекладати осередки з рядків в стовпці, то ви оціните наступний трюк:

Виділіть діапазон.
Скопіюйте його (Ctrl + C) або, натиснувши на праву кнопку миші, виберіть «Копіювати» (Copy).
Клацніть правою кнопкою миші по комірці, куди хочете вставити дані, і виберіть в контекстному меню один з варіантів спеціальної вставки – значок «Транспонувати» (Transpose). У старих версіях Excel немає такого значка, але можна вирішити проблему за допомогою спеціальної вставки (Ctrl + Alt + V) і виберіть пункт «Транспонувати» (Transpose).

Умовне форматування

Щось на зразок діаграми, тільки показники знаходяться не в сусідній комірці, а прямо на тлі даних. Цю функцію включають, щоб виділити деякі дані в таблиці.
Наприклад, вчитель у школі робить таблицю середньої успішності кожного учня. Тоді умовне форматування прямо всередині осередків з оцінками побудує «графік», на якому будуть виділені середні бали кожного учня, наприклад, від низького до високого. Або ж виділить осередки, в яких середній бал, наприклад, нижче або вище 6. Параметри можуть бути різними.
Щоб функція запрацювала, потрібно відкрити вкладку «Головна», виділити поле з осередками, в групі інструментів «Стилі» знайти значок «Умовне форматування»і вибрати підходящий варіант – це може бути гістограма, колірна шкала або набір значків. Цією ж командою можна самостійно встановити правила виділення осередків.

Іскристі лінії

Це мудре слово в Excel позначає діаграму, створену прямо в осередку і показує динаміку даних рядка. Створюється вона так:

Натиснути «Вставка»
Відкрити «Міні-діаграми»
Вибрати команду «Графік» або «Гістограма»
У вікні вказати діапазон з числами і осередки, в яких повинні з'явитися Міні-діаграми, засновані на цих числах

макроси

І ця функція створена, щоб полегшити користувачам життя і важкі робочі завдання. Вона фіксує послідовність виконуваних дій, а потім проробляє їх автоматично, без чиєї-небудь участі.
Потрібно включити макрос, відкривши вкладку «Розробник» і відшукавши на панелі інструментів значок «Макрос», а поруч з нею – схожий значок з червоним кружечком в верхньому лівому куточку. Якщо багато разів потрібно виконувати одне і теж дія – так запуститься запис макросу, і далі комп'ютер зробить сам.

прогнози

Ця опція неспроста не афішується розробниками – вона може передбачати майбутнє! Насправді функція може спрогнозувати, порахувати майбутні значення на основі вже відомих даних, які вже прописані. Доступ до неї простий:

Вказати не менш 2 осередків з вихідними даними
Відкрити «Дані» – «Прогноз» – «Лист прогнозу»
У віконці «Створення листа прогнозу» клікнути на графік або гістограму
В області «Завершення прогнозу» встановити дату закінчення, натиснути «Створити»

Заповнити порожні клітинки списку

Це позбавить від монотонного введення однакових фраз в безліч осередків. Звичайно, можна скористатися старим добрим копіпастом (копіювати ctrl + c, вставити ctrl + v), але, якщо потрібно заповнити не 10 осередків, а, наприклад, сотню-іншу, і місцями текст буде різним, – наступна підказкою точно стане в нагоді.
Припустимо, є величезний список справ на весь тиждень, і потрібно навпаки кожного завдання поставити певний день тижня. Не варто прописувати по 20 понеділків, вівторків і п'ятниць. У стовпці ставимо кожен день тижня на тому місці, де повинен початися список справ на цей день. Це має виглядати приблизно так:
Далі виділяється стовпець з нашими днями тижня, у вкладці «Головна» натискаються кнопки «Знайти і виділити», «Виділити групу осередків», «Порожні клітинки». Потім в першій порожній клітинці потрібно поставити знак «=», стрілкою «вгору» на клавіатурі повернутися до заповненої комірки (на прикладі в таблиці – «понеділок»). Натиснути ctrl + enter. Готово, тепер все порожні клітинки повинні заповнитися продубльованими даними.

Знайти помилки у формулі

Буває, формула не працює, але причина «поломки» незрозуміла. Іноді, щоб розібратися в складній формулі (де як аргумент функції беруться інші функції) або знайти в ній помилку, потрібно обчислити тільки її частину. Ось дві підказки:
Частина формули обчислюється прямо в рядку формул. Для цього необхідний ділянку потрібно виділити і натиснути F9. Все просто, але є одне «але». Якщо забути повернути все на місце, тобто скасувати обчислення функції, і натиснути enter – порахована частина залишиться у вигляді числа.
Клікнути на «Обчислити формулу» у вкладці «Формули». Відкриється вікно, де можна обчислювати формулу покроково і тим самим знайти момент, де з'являється помилка, якщо вона, звичайно, є.

«Розумна» таблиця

Якщо у користувача і MS Excel різне розуміння поняття таблиці, це може позбавити його багатьох зручних функцій і розтягнути час роботи. Накреслену олівцем або за допомогою кордону – програма вважатиме звичайним набором осередків. Щоб дані сприймалися саме як таблиця, це поле потрібно відформатувати.
Необхідно виділити потрібну область, у вкладці «Головна» натиснути кнопку «Форматувати як таблицю». У списку різних форм і кольорів варіантів оформлення знайти підходящий.
Тепер це справжня таблиця, і якщо в неї хочеться додати нові формули – вставляти їх потрібно прикордонні осередки, тоді вони автоматично поширяться на весь стовпець. Плюс до всього шапка таблиці завжди буде видна і при прокручуванні курсора вниз.

Автоматичний і ручний перерахунок

Для книги, яка містить сотні складних формул можна налаштувати перерахунок на вимогу користувача. Для цього:

Введіть формулу на чистий аркуш (щоб можна було перевірити як працює даний приклад).
Виберіть інструмент: «Формули» – «Параметри обчислень» – «Вручну».
Переконайтеся, що тепер після введення даних в осередок (наприклад, число 7 замість 1 в осередку A2 як на малюнку), формула не перераховує результат автоматично. Поки користувач не натисне клавішу F9 (або SFIFT + F9).

Увага! Швидка клавіша F9 – виконує перерахунок у всіх формулах книги на всіх аркушах. А Комбінація гарячих клавіш SHIFT + F9 – виконує перерахунок тільки на поточному аркуші.

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

Видаляємо непотрібні стовпці і рядки

Це досить поширена проблема при роботі в Excel. При звичаї користувач випадково переміщається за допомогою гарячого поєднання клавіш Ctrl + стрілка вправо або вниз, натиснутих випадково, і переноситься в кінець листа, та так і зберігає робочу книгу, при цьому значно її ускладнює. Особливо це відбувається, коли в кінець книги додається випадковий символ, знак або заливка.

Перевірити такий варіант можливо по характерній ознаці, це дуже маленький повзунок.

Замінити формат робочої книги на * .xlsb

У випадках, коли ваша робота пов'язана з величезними таблицями і їх розмір досягає великих обсягів, то слід зберігати електронні таблиці у форматі * .xlsb. Це розширення зберігається як двійковий формат, своєрідний спеціальний формат для створення і зберігання ваших «баз даних» мають в основі електронні таблиці.

Коли збереження будуть проведені в цьому форматі, будь-який великий файл зменшиться відразу ж в 2-3 рази, до речі, швидкість обчислень також збільшиться на кілька порядків, що буде вас радувати.

Прибираємо надмірне умовне форматування

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

Для видалення всього зайвого, для початку, вибираєте необхідний діапазон, а можна і весь робочий лист. Наступним кроком в панелі управління, у вкладці «Головна» в розділі «Стилі» імені команди з випадає меню «Умовне форматування», далі потрібен пункт «Видалити правила» і вибираєте потрібний підпункт видалення.

Видаляємо непотрібні даних всередині структури файлу Excel

Я можу посперечатися, що далеко не кожен користувач знає, що файли Excel являють собою своєрідний архів і така структура файлу почала своє життя з 2007 випуску. А це означає, що тепер файли Excel можна відкрити архіватором, таким як WinRar. А ось всередині файлу і можуть перебувати файли, які, в деяких випадках значно гальмують Excel.

Для проведення процедури по зменшенню розміру, перестрахуйтеся, і зробіть резервне копіювання вашого файлу. Потім відкрийте файл за допомогою контекстного меню, відкриваємо файл, натиснувши на мишці праву кнопку і вибравши пункт “Відкрити за допомогою» і з доступних програм вибираєте програму архіватор. Або, другий варіант, відкриваєте архіватор і в меню «Файл» клікаєте на пункт «Відкрити всередині». Результатом будь-якого з варіантів буде відкриття файлу Excel, як архіву з файлами і папками.

В архіві, швидше за все в папці «xl», видаліть папки «printerSettings» і «drawings». Після всіх мук запускаємо знову файл і на все системні обурення погоджуємося кнопкою «Ок», файл відновитися і запуститься.

Звертаю вашу увагу, що якщо файл містить мальовані об'єкти типу кнопок або фігур то видаляти всю папку «drawings» не варто, разом з нею випаруються і фігурки. Досить в папці видалити vmlDrawing.vml який вміщує різноманітну інформацію і може досягати великих розмірів.

Неправильно настроєна друк на принтер

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

Іноді трапляється так, що стирання налаштувань принтера з пункту 7, може привести до того, що неправильні настройки принтера гальмують весь файл.

Змінити версію Excel на більш пізню

Ні що не стоїть на місці, навіть наша планета, так і Excel розвивається, адже досконалості немає меж. Змінюється і розвивається програмний продукт, оптимізується програмний код, що дозволяє, іноді значно, підвищити продуктивність роботи з таблицями та розрахунки формул, швидкодія може збільшитися до 20% в більш нових версіях, наприклад в 2016 відносно 2007.

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

Вдосконалить версію, переходите на більш продуктивні продукти MS Office, бажано від 2013 версії і вище.

Видалити зайві рядки / стовпці (якщо повзунок прокрутки дуже маленький)

Найчастіша проблема для excel, з якої я зустрічаюся. Якщо хтось випадково перемістився на кінець листа (на рядок номер 1 млн) і так зберіг книгу. Розмір файлу відразу збільшився. Дійти до кінця таблиці можна і випадково, якщо натиснути Ctrl + стрілка вниз або вправо. Буває, що в кінці книги знаходиться який-небудь випадковий символ або заливка.

Головна ознака гальм – це розмір повзунка, він дуже маленький, коли файл збережений невірно, як на картинці.

Виправте ситуацію, видаліть зайві рядки або стовпці. Знайдіть останню корисну осередок для вас, виділіть першу вільну позицію після неї (а краще першу порожній рядок / стовпець після неї), натисніть Ctrl + Shift + End. Таке поєднання клавіш виділяє осередки нижче вибраного рядка або правіше обраного стовпця. Права кнопка миші – Видалити – Видалити рядок або стовпець (як правило, проходить довго). Після видалення виберіть осередок А1 і збережіть файл. Повзунок повинен збільшитися.

Видалити зайві об'єкти

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

Щоб видалити такі об'єкти виконайте макрос, натисніть Alt + F11 і копіюйте текст нижче.

sub DelOb() For Each i In ActiveSheet.Shapes i.Delete Next end sub

Або виділіть і видаліть об'єкти вручну. Перейдіть в меню Головна – Редагування – Знайти і виділити – пункт Виділення групи осередків – Об'єкти. Тепер видаляйте.

Видалити зайві дані в структурі файлу Excel

Навіть досвідчені користувачі не знають, що файл Excel, як каже Вікіпедія, це файл-архів. Починаючи з 2007 випуску.

Тобто файл Excel відкривається, наприклад, архиваторами 7-zip або WinRar. Всередині відкритого файлу можуть зберігатися непотрібні файли, що гальмує Excel часом в десятки разів.

Видалимо незручність? Спершу зробіть резервну копію файлу Потім запустіть 7-zip або інший архіватор, меню «Файл» – «Відкрити всередині». Можливо відкрити файл клікнувши правою кнопкою миші – Відкрити за допомогою і вибравши .exe файл WinRar або 7-zip.

Відкриється архів, він же файл Excel c папками і файлів.

Знаходимо папки «drawings» і / або «printerSettings» (швидше за все вони будуть в папці xl) і видаляємо їх.

Для WinRar робимо те ж саме.

Після чого відкриваємо книгу як файл Excel, він трохи полаявся, виведе кілька системних повідомлень, що не знаходить даних і т.п. На всі вікна натискаємо ОК, файл відновиться.

Будьте уважні, якщо в вашому файлі є намальовані кнопки або інші фігури, то видаляти всю папку drawings – значить і видаляти корисні фігури.

Тому в папці видаліть тільки файли vmlDrawing.vml, вони можуть накопичувати інформацію і важити до 100 мб.

Гальмує Excel – правильно налаштуйте зведені таблиці

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

Що дозволить зменшити файл майже в два рази.

Змініть формат файлу на .xlsb

Якщо ви працюєте з величезними таблицями і ваші файли більше 0,5 мб вагою, то краще зберігати такі книги в форматі .xlsb. Двійковий формат книги Excel, тобто спеціальний формат для створення «бази даних» на основі електронних таблиць. Якщо зберегти великий файл в такому форматі, вага книги зменшиться в два-три рази. Розрахунки в файл теж будуть проходити швидше, в деяких випадках в 2 рази швидше.

Встановлено непізнаний принтер

Якщо у Вас на комп'ютері не визначений принтер, тобто не варто принтер за замовчуванням, то зайдіть в пристрої та принтери і змініть принтер за замовчуванням на будь-який інший (навіть якщо фізично принтера немає), якщо принтер є, краще змінити дрова.

Буває що навіть при видаленні налаштувань принтера з пункту 5 настройки принтера гальмують файл.

Рада з Excel. Гарячі клавіші

Порада номер два випливає з першого – вчіть гарячі клавіші. Корисних гарячих клавіш не так вже й багато, тому вивчивши 10-20 поєднань ви швидко відчуєте різницю в швидкості роботи.

“Гарненький рада!” – заперечите ви. – “І як їх вчити? Ставати на табуретку перед колегами і розповідати як вірші?”

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

Наприклад, можна взяти список гарячих клавіш і вибрати 3-5 найбільш використовуваних функцій. Спробувати відпрацювати їх, потренуватися як вони працюють. Звикнути натискати їх коли вони потрібні. Згодом пальці самі ляжуть на клавіатуру так, щоб швидко і зручно перемикатися між листами. Вивчили ці – переходите до наступних, а потім ще і ще.

Рада з Excel. Функції та їх комбінації, які обязатлеьно освоїти

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

Також у кожній функції може бути безліч особливостей використання. Так, наприклад, зает ви що функція СУММ може суміровать значення з різних листів вашої книги і при цьому не потрібно виділяти кожне з них окремо? Тобто

замість формули

СУМ (Лист1! А1; Аркуш2! А1; Ліст3! А1; Ліст4! А1; Ліст5! А1; Ліст6! А1; Ліст7! А1; … ЛістN! A1)

Формула буде виглядати як

СУМ (Лист1: ЛістN! А1)

Все це до чого? Крім багатого функціоналу Excel, який є в його стандартних формулах, існує безліч комбінацій, знання яких дозволяє вирішувати і не стандартні завдання. Так, у Excel зовсім немає функції МІНЕСЛІ. Так, є СУММЕСЛИ, СЧЁТЕСЛІ, а МІНЕСЛІ не зробили. Також МАКСЕСЛІ, МЕДІАНАЕСЛІ і т.п., але при цьому все це вирішується шляхом використання функцій областей. Можливо ви бачили, коли формула одягається в фігурні дужки.

Деякі функції відмінно працюють тільки в зв'язці. Це я зараз кажу про ІНДЕКС та ПОИСКПОЗ. Здавалося б, безглузді функції окремо, але в парі дають відмінний функціонал.

Загалом пані та панове, рекомендую вам вчити формули і всякі трюки з цими формулами.

Що для цього потрібно? Наприклад, підписатися на нашу групу в і чекати виходу нових постів.

Рада з Excel. Структура і форматування в файлах

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

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

Як цього можна уникнути? Дам кілька порад:

Відформатуйте файл. Нехай у вас буде однакова кількість знаків після коми, однаковий шрифт по всьому документу і обмежена колірна гама. Мені ось подобається використовувати палітри одного кольору але різних тонів (блакитний, синій і темно-синій). Виглядає дуже стильно.
Розділіть вихідні дані, розрахунки та результати. Не завжди це потрібно, але коли даних стає дуже багато наявність зведеної таблички просто рятує.
Робіть однакові формули по стовпцю або рядку. погодьтеся не дуже правильно коли один і той же показник в різні періоди вважається по різному. При цьому зазвичай цього не видно поки не заглянеш в формулу. а често можна ще й забути.
Намагайтеся уникати циклічних посилань і посилань на зовнішні файли. Зазвичай саме вони дають різний результат на різних комп'ютерах. Був випадок, коли похибка при оновленні зовнішніх посилань була більше ніж з 7 нулями. Ай!
Спрощуйте і не еконономьте місце. Excel дозволяє зробити як завгодно багато рядків і стовпців. За свої 10 років роботи з програмою я ніколи не використовував лист повністю. Навряд чи у вас це вийде, тому не городите складні формули. Краще зробіть розрахунок в кілька дій.

Використані джерела і корисні посилання по темі: https://Lifehacker.ru/uskorennaja-rabota-v-excel/ https://zen.yandex.ru/media/id/5a323345780019a66a1ed2c7/uskoriaem-rabotu-v-excel-poleznye- sovety-funkcii-bystrye-klavishi-5ac7331bdcaf8e9121c8583b https://exceltable.com/formuly/avtomaticheskiy-pereschet-formul http://topexcel.ru/pochemu-tormozit-excel-12-sposobov-uluchshit-rabotu-s-fajlami/ https://excelworks.ru/2015/03/18/tormozit-excel/ https://finversity.ru/tryuki-excel/kak-rabotat-v-excel-v-3-5-raz-byistree.html

Джерело запису: lastici.ru