Съдържание
- Как да конфигурирам Excel 2007 и Excel 2010 за игнориране на празни клетки при създаване на диаграма или графика
- Метод 1: Конфигурирайте как Excel третира скрити и празни клетки
- Метод 2: Използвайте формула, за да конвертирате празни клетки в # N / A
- Обобщавайки
Роби пише най-вече за Skyrim, но също така понякога хвърля светлина върху странностите на приложенията на Microsoft като Excel и Outlook.
Как да конфигурирам Excel 2007 и Excel 2010 за игнориране на празни клетки при създаване на диаграма или графика
Това е проблем, който имах и аз самият, където исках да създам диаграма, когато повечето от клетките в диапазона, който искате да графирате, са празни (съдържа празни клетки).
За да разрешите това и да създадете диаграма, която правилно показва данните, може да се изкушите, както направих в миналото, да копирате стойностите на данните в нов диапазон, така че данните да са непрекъснати (без пропуски). Това може да отнеме изключително много време и да досади, особено ако количеството данни е голямо. Excel трябва да улесни колективния ни живот, затова реших да потърся решение на този проблем, който да включва възможно най-малко допълнителна ръчна работа.
В моя случай се интересувах от графиката на средните седмични стойности на броя на дневните продажби, направени от моя екип. Когато създадох диаграма, като избрах диапазона от данни, имах само диаграма без данни.
Ще разгледаме два метода за разрешаване на това:
- Как да промените начина, по който диаграмата се справя с празни или празни клетки
- Как да използвам формула за промяна на съдържанието на празни клетки на # N / A (Excel автоматично игнорира клетки с # N / A, когато създавате диаграма)
На фигурата по-долу можете да видите:
- Данните, които използвам за създаване на диаграмите (вляво)
- Празната начална диаграма (горе вдясно)
- Попълнената диаграма (долу вдясно)
Метод 1: Конфигурирайте как Excel третира скрити и празни клетки
Първият метод, който ще използваме, за да създадем диаграма, която правилно показва данните, е да променим начина, по който Excel 2007 и Excel 2010 третират празни клетки в графики.
По подразбиране в диаграма, когато Excel види празна клетка, тя създава празнина. Когато имате диапазон от данни с повече празни клетки, отколкото клетки с данни, това означава, че Excel ефективно създава графика, пълна с пропуски.
За начало трябва да създадем диаграма на средните седмични продажби.
- Създаваме линейна графика, като избираме данните в B3: B31 и D3: D31 (щракнете и задръжте Ctrl ключ за избор на колони, които не са съседни)
- Щракнете върху Поставете и изберете Линия бутон в Графики група и изберете Линия
След като създадем графиката, трябва да променим начина, по който Excel третира празните клетки. Да го направя:
- Кликнете с десния бутон графиката и изберете Изберете Данни
- Щракнете Скрити и празни клетки
- Пропуски е настройката по подразбиране, както обсъждахме по-рано
- Нула в нашия пример ще доведе до данните да бъдат отбелязани като стойност, нула, нула, нула, нула, нула, нула, стойност
- Свържете точките с данни с линия прави точно както казва, свързва точките за данни заедно
Фигурата по-долу показва нашата графика, създадена с помощта Нула (вляво) и Свържете точките с данни с линия (вдясно). Пропуски вече видяхме с празната диаграма по-горе.
- Също така подредих хоризонталната ос от щракване с десен бутон го и избор Форматиране на ос
- Под Опции на оста раздела, промених Основна единица да се Фиксирани 7 дни
- Смених Ос на позицията да се На отметки така че да показва пълната дата
Метод 2: Използвайте формула, за да конвертирате празни клетки в # N / A
Вторият метод за гарантиране, че Excel прави диаграми на данните, съдържащи правилно голям брой пропуски или празни клетки, е използването на формула. За да постигнем това, ще използваме факта, че когато Excel вижда # N / A в клетка, той не го включва в диаграма. Формулата, която ще използваме в E3, е:
= IF (D3 = "", # N / A, D3)
The АКО функция разглежда съдържанието на клетка D3 и АКО D3 е равно на “” (с други думи празна клетка), след което променя текущата клетка на # N / A. Ако Excel намери нещо в клетката, той просто ще копира клетката. Например ще копира съдържанието на D3 в E3.
Имам статия, която обсъжда АКО функция по-подробно. Аз също покривам използването IFERROR за потискане на очакваните грешки и използване АКО с логическите функции И, ИЛИ и НЕ. Можете да го намерите тук:
- След това копирам формулата надолу по колоната E.
- След това създавам диаграма точно както направихме по-горе, използвайки датите в колона Б и резултатите от формулите в колона Е
Както можете да видите от фигурата по-горе, диаграмата правилно показва данните, въпреки че Excel използва Показване на празни клетки като Пропуски (настройката по подразбиране за всички диаграми, създадени в Excel 2007 и Excel 2010).
След това щяхме да подредим графиката, както направихме по-горе,
- Подреждане на хоризонталната ос
- Изтриване на Легенда
- Добавяне на a Заглавие (щракнете върху Оформление с избраната графика, изберете Заглавие на диаграмата и изберете предпочитания тип заглавие)
Обобщавайки
Често данните влизат във вас от електронни таблици, върху които нямате контрол и не винаги са форматирани точно както бихте искали. Вместо да редактирате отново данните така, че да отговарят на това, което искате да направите, винаги е много по-добре и по-бързо да накарате Excel да заобиколи всички форматиращи предизвикателства, които данните могат да имат, и да доведе до желаните резултати, без съществено ръчно редактиране от ваша страна.
В тази статия разгледахме проблем, който често срещам:
Как да създадете диаграми, използвайки данни, които съдържат голям брой празни клетки. Разгледахме два метода как да разрешим това:
- Промяна на начина, по който Excel третира празни клетки в самата диаграма
- Използвайки АКО оператори за промяна на празни клетки в # N / A (Excel 2007 и Excel 2010 игнорират клетки, съдържащи # N / A при създаване на диаграми)
Използвайки АКО изявления във формула е по-гъвкав от промяната на начина на работа на Excel с празни клетки в диаграма, тъй като можете да поискате от него да промени каквото и да е на # N / A, а не само празни клетки. Това означава, че ако получавате електронни таблици с боклуци или данни, които не искате, можете също така да накарате Excel да ги игнорира за целите на създаването на диаграма от данните.
Надявам се, че сте намерили тази статия за полезна и информативна. Моля, не се колебайте да оставяте коментарите си по-долу и много благодаря за четенето!
Тази статия е точна и вярна доколкото е известно на автора. Съдържанието е само за информационни или развлекателни цели и не заменя лични съвети или професионални съвети по бизнес, финансови, правни или технически въпроси.
