Самоучитель 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 приходится достаточно часто. Например:

1. Номер фактуры или договора состоит из более 12-ти чисел. Допустим номер: «1234567891012» Excel округляет большие числа в такой формат «1,23457E+12».
2. Некоторые номера телефонов автоматически конвертируются в дату. Номер телефона: 2012-10-17 отображается как «17.10.2012».
3. Иногда нужно записать формулу обычным текстом.

Поэтому важно научиться управлять форматами ячеек.

Попробуйте заполнить табличку, так как показано на рисунке:

Как видно на примере проблема возникает при введении текста «10/12». После нажатия клавиши «Enter» получаем дату:

Необходимо заставить Excel распознавать числа как текст.

Решение 1. В ячейке D2 введите перед текстовым значением символ апострофа «’» (апостроф следует ввести с английской раскладки клавиатуры). Теперь число преобразовано в текстовое значение.

Решение 2. Задайте в ячейке D2 текстовый формат. Для этого откройте окно «Формат ячеек» CTRL+1. На вкладке «Число» выберите из списка «Числовые формат» опцию «Текстовый» и нажмите ОК. Теперь можно вводить любые числа как текст без апострофа.

Главным отличием выше описанных двух примеров является закрепление за ячейкой способа отображения ее значений с помощью присвоения формата. Если в первом решении в ячейку будут вводиться новые типы данных (без апострофа), то они будут отображаться по умолчанию.

Примечание. Второе решение закрепляет за ячейкой ее формат, и любые другие типы данных будут преобразованы, пока пользователь не изменит для нее другие форматы (например, «Общий» — по умолчанию).



Математические операции с текстом в Excel

Если функция ссылается на ячейку, в которой число записано текстом, то при вычислении значение этой ячейки равно «0». Но здесь нужно быть очень внимательным, так как бывают исключения. Это может приводить к ошибочным расчетам. Приводим конкретный пример.

1. Заполните диапазон ячеек A1:D6 так как показано на рисунке:
2. Колонку E не нужно заполнять, так как в ней отображается содержимое ячеек в колонке D. Обратите внимание в ячейках B3 и B6 числовые данные записаны как текстовые через апостроф «’».
3. В колонку 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

Часы

Обновлено 11 марта 2023 г.

Краткое изложение учебника по Microsoft Excel

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