Самоучитель xl: Самоучитель Excel с примерами для пользователей среднего уровня
Содержание
Самоучитель Excel с примерами для пользователей среднего уровня
Самоучитель Excel с примерами для ежедневной офисной работы менеджера. Это иллюстрированный пошаговый справочник пользователя, в котором детально описаны решения различных задач в области аналитики, учета, статистики, анализа данных, финансовых расчетов и вычислений. В самоучителе описаны методы организации рабочего пространства Excel, которые позволяют использовать программу как персональный рабочий станок с высокой производительностью и комфортным управлением данными.
Раздел 1: Решения проблем с введением данных в ячейки
Вводим в ячейку число как текст.
Заполнение ячеек в Excel знаками после запятой.
4 способа заменить точку на запятую в Excel.
Раздел 2: Автоматическое заполнение ячеек таблиц
Как автоматически заполнить ячейки в MS Excel с большими таблицами.
Пример как изменить сразу все цены в Excel.
Быстрое заполнение и редактирование шаблона таблиц.
Копирование формул без изменения адресов относительных ссылок.
Простые расчеты и вычисления без использования формул.
Раздел 3: Форматирование ячеек
Как изменить формат ячейки в Excel быстро и качественно.
Нестандартное условное форматирование по значению ячейки в Excel.
Стили ячеек в Excel и средства управления ими.
Создание шаблонов и тем для быстрого форматирования.
Раздел 4: Защита данных
Защита файла паролем и шифрованием.
Как настроить автосохранение документа.
Защита персональных данных в файле.
Защита листа и ячеек в Excel.
Скрыть лист в списке скрытых листов.
Проверка ввода данных в Excel и ее особенности.
Раздел 5: Работа с таблицами
Автоматическое создание таблиц Excel.
Автоматическое добавление строк и столбцов в таблицу.
Стили таблиц для автоматического форматирования диапазонов ячеек.
Возможности умной таблицы.
Удаление дубликатов с помощью таблиц.
Раздел 6: Формулы в Excel
Абсолютные относительные адреса ячеек в формуле.
Автоматический пересчет формул и вручную.
Трехмерные ссылки в формулах.
Зависимость формул и структура их вычисления.
Как убрать ошибки в ячейках.
Зависимости формул и построение их схемы.
Проверка вводимых значений.
Раздел 7: Имена диапазонов ячеек
Как присвоить имя значению.
Имена диапазонов с абсолютным адресом.
Область видимости имени на листе.
Имена диапазонов с относительным адресом ссылки.
Автоматически изменяемые диапазоны ячеек.
Изменяемые диапазоны и функция промежуточные итоги.
Раздел 8: Создание диаграмм и графиков в Excel
Автоматическое создание графиков и диаграмм.
Как построить график функции в Excel.
Как сделать диаграмму с процентами.
Как построить лепестковую диаграмму.
Пузырьковая диаграмма в Инфографике.
Поверхностная диаграмма и пример ее построения.
Построение линии тренда в Excel для анализа графика.
Построение графиков в Excel практическая работа.
Интерполяция графика и табличных данных.
Спарклайн позволяет создать мини график в ячейке.
Работа с шаблонами графиков.
Раздел 9: Подбор параметра, поиск решения и сценарии
Уравнения и задачи на подбор параметра.
3 примера использования подбора параметра.
Надстройка поиск решения и подбор нескольких параметров.
Сценарии в Excel позволяют прогнозировать результат.
Раздел 10: Подбор параметра, поиск решения и сценарии
Сортировка и фильтр на примере базы данных клиентов.
Обучение сводным таблицам на примерах.
Вводим в ячейку число как текст
Часто в Excel числа необходимо записать как текст. И на оборот, введенные нами текстовые значения программа меняет на числа или даты.
На первом уроке рассмотрим, в каких случаях может возникнуть конфликт между пользователем и программой при введении данных в ячейки.
Так же найдем лучшее решения для правильного введения в ячейки числа как текст.
Как преобразовать число в текст?
Вводить числа как текст в Excel приходится достаточно часто. Например:
- Номер фактуры или договора состоит из более 12-ти чисел. Допустим номер: «1234567891012» Excel округляет большие числа в такой формат «1,23457E+12».
- Некоторые номера телефонов автоматически конвертируются в дату. Номер телефона: 2012-10-17 отображается как «17.10.2012».
- Иногда нужно записать формулу обычным текстом.
Поэтому важно научиться управлять форматами ячеек.
Попробуйте заполнить табличку, так как показано на рисунке:
Как видно на примере проблема возникает при введении текста «10/12». После нажатия клавиши «Enter» получаем дату:
Необходимо заставить Excel распознавать числа как текст.
Решение 1. В ячейке D2 введите перед текстовым значением символ апострофа «’» (апостроф следует ввести с английской раскладки клавиатуры).
Теперь число преобразовано в текстовое значение.
Решение 2. Задайте в ячейке D2 текстовый формат. Для этого откройте окно «Формат ячеек» CTRL+1. На вкладке «Число» выберите из списка «Числовые формат» опцию «Текстовый» и нажмите ОК. Теперь можно вводить любые числа как текст без апострофа.
Главным отличием выше описанных двух примеров является закрепление за ячейкой способа отображения ее значений с помощью присвоения формата. Если в первом решении в ячейку будут вводиться новые типы данных (без апострофа), то они будут отображаться по умолчанию.
Примечание. Второе решение закрепляет за ячейкой ее формат, и любые другие типы данных будут преобразованы, пока пользователь не изменит для нее другие форматы (например, «Общий» — по умолчанию).
Математические операции с текстом в Excel
Если функция ссылается на ячейку, в которой число записано текстом, то при вычислении значение этой ячейки равно «0». Но здесь нужно быть очень внимательным, так как бывают исключения.
Это может приводить к ошибочным расчетам. Приводим конкретный пример.
- Заполните диапазон ячеек A1:D6 так как показано на рисунке:
- Колонку E не нужно заполнять, так как в ней отображается содержимое ячеек в колонке D. Обратите внимание в ячейках B3 и B6 числовые данные записаны как текстовые через апостроф «’».
- В колонку D введите формулы, которые отображаются в колонке E как на рисунке.
Теперь обратите внимание на результаты вычислений функций и формул в колонке D.
В ячейке D2 функция =СУММ(A2:C2) отображает правильный результат 1+100+10=111. А в ячейке D3 функция суммирования меняет значение ячейки B3 на число 0, так как там данные отображаются в текстовом формате. В результате: 1+0+10=11.
Внимание! При вычислении аналогичных значений ячеек, но уже с помощью обычной формулы =A6+B6+C6 все значения ячеек воспринимаются как числовые значения, несмотря на то, что ячейка B6 имеет текстовый формат. Формулы могут суммировать, умножать, разделять и вычитать текст, если он состоит только из символов чисел (0-9), а функции не могут.
Из этого примера возникает простой вывод. При вычислении и расчетах в Excel необходимо использовать одинаковые типы данных, не зависимо от формул и функций. Это лучше чем конвертировать текст в число и позволит избежать много ошибок. Не выполняйте никаких математических операций с текстом.
Читайте так же: как перевести число и сумму прописью в Excel.
все уроки
#1 Учебное пособие по Excel в сети
Вы можете найти соответствующие примеры и функции в правой части каждой главы внизу каждой главы. Ниже вы можете найти 80 популярных примеров.
1 Найти повторяющиеся значения. В этом примере показано, как находить повторяющиеся значения (или трижды) и как находить повторяющиеся строки в Excel.
2 Раскрывающийся список. Раскрывающиеся списки в Excel полезны, если вы хотите быть уверены, что пользователи выбирают элемент из списка, а не вводят свои собственные значения.
3 ВПР: Функция ВПР — одна из самых популярных функций в Excel. Эта страница содержит много простых примеров VLOOKUP.
4 Гистограмма: В этом примере показано, как построить гистограмму в Excel.
5 Регрессия: В этом примере показано, как выполнять анализ линейной регрессии в Excel и как интерпретировать сводные выходные данные.
6 Изменение в процентах: Формула изменения в процентах очень часто используется в Excel. Например, для расчета ежемесячного изменения и общего изменения.
7 Диаграмма Парето: Диаграмма Парето сочетает в себе гистограмму и линейный график. Принцип Парето гласит, что для многих событий примерно 80% следствий возникают из-за 20% причин.
8 График погашения кредита: В этом примере показано, как создать график погашения кредита в Excel.
9 Случайные числа: В Excel есть две очень полезные функции, когда речь идет о генерации случайных чисел. РЭНД и СЛУЧМЕЖДУ.
10 Удалить дубликаты: В этом примере показано, как удалять дубликаты в Excel.
11 Если: функция ЕСЛИ — одна из наиболее часто используемых функций в Excel. Эта страница содержит много простых примеров IF.
12 Блокировка ячеек: Вы можете заблокировать ячейки в Excel, если хотите защитить ячейки от редактирования.
13 Стандартное отклонение: на этой странице объясняется, как рассчитать стандартное отклонение на основе всей совокупности с помощью функции СТАНДОТКЛОН.П в Excel и как оценить стандартное отклонение на основе выборки с помощью функции СТАНДОТКЛОН.С в Excel.
14 Подсчет уникальных значений: В этом примере показано, как создать формулу массива, которая подсчитывает уникальные значения.
15 Диаграмма Ганта: Excel не предлагает Ганта в качестве типа диаграммы, но диаграмму Ганта легко создать, настроив тип гистограммы с накоплением.
16 Countif: мощная функция COUNTIF в Excel подсчитывает ячейки на основе одного критерия. Эта страница содержит много простых примеров COUNTIF.
17 Бюджет: В этом примере показано, как создать бюджет в Excel.
18 Линейный график: Линейные графики используются для отображения трендов во времени. Используйте линейную диаграмму, если у вас есть текстовые метки, даты или несколько числовых меток на горизонтальной оси.
19 Транспонирование: используйте параметр «Специальная транспонирование вставки», чтобы переключать строки в столбцы или столбцы в строки в Excel. Вы также можете использовать функцию ТРАНСП.
20 Корреляция: мы можем использовать функцию КОРРЕЛ или надстройку Analysis Toolpak в Excel, чтобы найти коэффициент корреляции между двумя переменными.
21 Табель учета рабочего времени: В этом примере показано, как создать простой калькулятор табеля учета рабочего времени в Excel.
22 Смещение: функция СМЕЩ в Excel возвращает ячейку или диапазон ячеек, который представляет собой заданное количество строк и столбцов из ячейки или диапазона ячеек.
23 Круговая диаграмма: Круговые диаграммы используются для отображения доли каждого значения (среза) в общей сумме (круговой диаграмме). В круговых диаграммах всегда используется один ряд данных.
24 Таблицы данных: вместо создания различных сценариев вы можете создать таблицу данных, чтобы быстро опробовать различные значения для формул.
Вы можете создать таблицу данных с одной переменной или таблицу данных с двумя переменными.
25 t-тест: В этом примере показано, как выполнять t-тест в Excel. Стьюдентный тест используется для проверки нулевой гипотезы о том, что средние значения двух совокупностей равны.
26 Расширенный фильтр: В этом примере показано, как применять расширенный фильтр в Excel для отображения только тех записей, которые соответствуют сложным критериям.
27 Частотное распределение. Знаете ли вы, что можно использовать сводные таблицы для простого создания частотного распределения в Excel? Вы также можете использовать пакет инструментов анализа для создания гистограммы.
28 Точечная диаграмма: Используйте точечную диаграмму (диаграмму XY) для отображения научных данных XY. Диаграммы рассеивания часто используются, чтобы выяснить, существует ли связь между переменными X и Y.
29 Anova: В этом примере показано, как выполнять однофакторный ANOVA (дисперсионный анализ) в Excel.
Один фактор или односторонний ANOVA используется для проверки нулевой гипотезы о том, что средние значения нескольких совокупностей равны.
30 Сравнить два списка. В этом примере показано, как сравнить два списка с использованием условного форматирования.
31 Сложные проценты: что такое сложные проценты и какова формула сложных процентов в Excel? Этот пример дает вам ответы на эти вопросы.
32 SUMIF: Мощная функция SUMIF в Excel суммирует ячейки на основе одного критерия. Эта страница содержит множество простых примеров SUMIF.
33 Столбчатая диаграмма. Столбчатая диаграмма представляет собой горизонтальную версию гистограммы. Используйте линейчатую диаграмму, если у вас есть большие текстовые метки.
34 Конкатенация: Используйте CONCATENATE, CONCAT, TEXTJOIN или оператор & в Excel для конкатенации (объединения) двух или более текстовых строк.
35 Закрепить области: Если у вас есть большая таблица данных в Excel, может быть полезно закрепить строки или столбцы.
Таким образом, вы можете держать строки или столбцы видимыми при прокрутке остальной части рабочего листа.
36 Средневзвешенное значение: Чтобы вычислить средневзвешенное значение в Excel, просто используйте СУММПРОИЗВ и СУММ.
37 Индекс и сопоставление: Используйте ИНДЕКС и ПОИСКПОЗ в Excel и произведите впечатление на своего босса. Вместо ВПР используйте ИНДЕКС и ПОИСКПОЗ. Для расширенного поиска вам понадобятся ИНДЕКС и ПОИСКПОЗ.
38 Удалить пустые строки: В этом примере показано, как удалять пустые строки или строки, содержащие пустые ячейки.
39 СУММПРОИЗВ: Чтобы вычислить сумму произведений соответствующих чисел в одном или нескольких диапазонах, используйте мощную функцию СУММПРОИЗВ Excel.
40 Объединить ячейки: объединить ячейки в одну большую ячейку, чтобы было ясно, что метка в Excel применяется к нескольким столбцам. Используйте CONCATENATE, TEXTJOIN или Flash Fill для объединения ячеек без потери данных.
41 Вычитание: в Excel нет функции ВЫЧИТАТЬ.
Однако есть несколько способов вычитания чисел в Excel. Готовы ли вы улучшить свои навыки работы с Excel?
42 Проценты: Расчет процентов в Excel прост. Процент просто означает «из 100», поэтому 72% — это «72 из 100», а 4% — это «4 из 100» и т. д.
43 Содержит определенный текст: чтобы проверить, содержит ли ячейка определенный текст, используйте ISNUMBER и ПОИСК в Excel. В Excel нет функции СОДЕРЖИТ.
44 PMT: функция PMT в Excel рассчитывает платеж по кредиту на основе постоянных платежей и постоянной процентной ставки. Эта страница содержит много простых примеров PMT.
45 Вычислить возраст: Чтобы вычислить возраст человека в Excel, используйте РАЗНДАТ и СЕГОДНЯ. Функция DATEDIF имеет три аргумента.
46 Обтекание текста: Обтекание текста в Excel, если вы хотите отобразить длинный текст на нескольких строках в одной ячейке.
47 Косвенный: используйте функцию ДВССЫЛ в Excel для преобразования текстовой строки в допустимую ссылку. Вы можете использовать оператор & для создания текстовых строк.
48 Умножение: Чтобы умножить числа в Excel, используйте символ звездочки (*) или функцию ПРОИЗВЕД. Узнайте, как умножать столбцы и как умножать столбец на константу.
49 Промежуточный итог: Используйте функцию ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel вместо СУММ, СЧЁТ, МАКС и т. д., чтобы игнорировать строки, скрытые фильтром, или игнорировать строки, скрытые вручную.
50 Поиск цели: если вы знаете, какой результат вы хотите получить от формулы, используйте функцию поиска цели в Excel, чтобы найти входное значение, которое дает результат этой формулы.
51 CAGR: в Excel нет функции CAGR. Однако просто используйте функцию RRI в Excel, чтобы рассчитать совокупный годовой темп роста (CAGR) инвестиций за период лет.
52 Если ячейка пуста: используйте функцию ЕСЛИ и пустую строку в Excel, чтобы проверить, пуста ли ячейка. Используйте IF и ISBLANK для получения точно такого же результата.
53 Среднее: Функция СРЗНАЧ в Excel вычисляет среднее (среднее арифметическое) группы чисел.
54 Подстрока: в Excel нет функции ПОДСТРОКА. Используйте MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM и MAX в Excel для извлечения подстрок.
55 Подсчет символов: узнайте, как подсчитать количество символов в ячейке или диапазоне ячеек и как подсчитать, сколько раз определенный символ встречается в ячейке или диапазоне ячеек.
56 Сумма: используйте функцию СУММ в Excel для суммирования диапазона ячеек, всего столбца или несмежных ячеек.
57 Сравнить два столбца: чтобы сравнить два столбца, используйте ЕСЛИ, ЕОШИБКА и ПОИСКПОЗ в Excel. Вы можете отображать дубликаты или уникальные значения.
58 Разделить: в Excel нет функции РАЗДЕЛИТЬ. Просто используйте косую черту (/) для разделения чисел в Excel.
59 Удаление пробелов: Функция ОБРЕЗ в Excel удаляет начальные, лишние и конечные пробелы. Используйте функцию ПОДСТАВИТЬ, чтобы удалить все пробелы или неразрывные пробелы.
60 Переместить столбцы. Чтобы переместить столбцы в Excel, используйте клавишу Shift или команду «Вставить вырезанные ячейки».
Вы также можете изменить порядок всех столбцов одним волшебным движением.
61 Скрыть столбцы или строки: иногда бывает полезно скрыть столбцы или строки в Excel.
62 Галочка: Чтобы вставить символ галочки в Excel, просто нажмите SHIFT + P и используйте шрифт Wingdings 2. Вы также можете вставить флажок в Excel.
63 Вставить строку: Чтобы быстро вставить строку в Excel, выберите строку и используйте сочетание клавиш CTRL SHIFT +.
64 Операторы сравнения: используйте операторы сравнения в Excel, чтобы проверить, равны ли два значения друг другу, не превышает ли одно значение другого значения и т. д.
65 NPV: правильная формула NPV в Excel использует функцию NPV для расчета текущую стоимость ряда будущих денежных потоков и вычитает первоначальные инвестиции.
66 Пункты списка: На этой странице показаны три способа вставки пунктов списка в Excel.
67 Среднееесли: Функция СРЗНАЧЕСЛИ в Excel вычисляет среднее значение ячеек, соответствующих одному критерию.
СРЗНАЧЕСЛИМН вычисляет среднее значение ячеек, соответствующих нескольким критериям.
68 График с прямоугольниками и усами: В этом примере показано, как создать график с прямоугольниками и усами в Excel. График с прямоугольниками и усами показывает минимальное значение, первый квартиль, медиану, третий квартиль и максимальное значение набора данных.
69 Гиперссылки: используйте диалоговое окно «Вставить гиперссылку» в Excel, чтобы создать гиперссылку на существующий файл, веб-страницу или место в этом документе. Вы также можете использовать функцию ГИПЕРССЫЛКА.
70 Затенение чередующихся строк: В этом примере показано, как использовать условное форматирование для затенения чередующихся строк.
71 IRR: используйте функцию IRR в Excel для расчета внутренней нормы прибыли проекта.
72 Разница во времени: Вычисление разницы между двумя значениями времени в Excel может оказаться сложной задачей. Время обрабатывается внутри как числа от 0 до 1.
73 Быстрый анализ: используйте инструмент быстрого анализа в Excel для быстрого анализа данных.
Быстро подсчитывайте итоги, быстро вставляйте таблицы, быстро применяйте условное форматирование и многое другое.
74 Зачеркнутый: В этом примере показано, как применить зачеркнутое форматирование в Excel.
75 Сегодняшняя дата: Чтобы ввести сегодняшнюю дату в Excel, используйте функцию СЕГОДНЯ. Чтобы ввести текущую дату и время, используйте функцию СЕЙЧАС.
76 Спарклайны. Спарклайны в Excel — это графики, помещающиеся в одну ячейку. Спарклайны отлично подходят для отображения трендов. Excel предлагает три типа спарклайнов: Line, Column и Win/Loss.
77 Прогноз: функция ПРОГНОЗ.ЛИНЕЙНЫЙ в Excel прогнозирует будущее значение по линейному тренду. Функция FORECAST.ETS в Excel прогнозирует будущее значение с помощью экспоненциального тройного сглаживания, которое учитывает сезонность.
78 Слайсеры. Используйте слайсеры в Excel для быстрой и простой фильтрации сводных таблиц. Подключите несколько слайсеров к нескольким сводным таблицам, чтобы создавать потрясающие отчеты.
79 Разделить ячейки: Чтобы разделить содержимое ячейки на несколько ячеек, используйте мастер «Текст в столбцы», мгновенное заполнение или формулы.
80 Календарь. В этом примере показано, как создать календарь в Excel (календарь на 2023 год, календарь на 2024 год и т. д.). Если вы спешите, просто скачайте файл Excel.
Посмотреть все 300 примеров.
Изучите MS Excel за 7 дней
АвторSusan Gipson
Часы
Обновлено
Краткое изложение учебника по Microsoft Excel
Excel — самый мощный инструмент для управления и анализа различных типов данных. Этот учебник по Microsoft Excel для начинающих содержит подробные уроки по изучению Excel и тому, как использовать различные формулы, таблицы и диаграммы Excel для управления малыми и крупными бизнес-процессами. Этот курс Excel для начинающих поможет вам изучить основы Excel.
