Работа с экселем для чайников: Ячейка в Excel – базовые понятия

Быстрое перемещение курсора по ячейкам листа Excel

Перемещение по ячейкам листа осуществляется с помощью курсора (управляемый черный прямоугольник). Чаще всего при заполнении данными листов Excel необходимо перемещаться к соседней ячейке. Но иногда требуется переместиться к любым отдаленным ячейкам.

Например, в конец / начала прайса и т.п. Каждая ячейка под курсором является активной. Просмотр содержимого листов Excel следует условно разделить на 2 типа: 1 просмотр с помощью перемещения курсора и 2 просмотр с помощью дополнительных инструментов предназначенных для просмотра без перемещения курсора. Например, полосы прокрутки листа.

О том, как в Excel стрелками быстро перемещаться по ячейкам, описано подробнее ниже.

Перемещение курсора к соседней ячейке в низ

Переместите курсор к нижней (следующей) ячейке. При загрузке программы по умолчанию курсор расположен на ячейке с адресом A1. Вам нужно перейти на ячейку A2. Для этого решения есть 5 вариантов:

1. Просто нажмите клавишу «Enter».
2. Перемещение по ячейкам стрелками. (Все стрелки клавиатуры влияют на перемещение курсора соответственно со своим направлением).
3. Наведите курсор мышки на ячейку с адресом A2 и сделайте щелчок левой кнопкой мыши.
4. С помощью инструмента «Перейти…» (CTRL+G или F5) .
5. Используя поле «Имя» (расположенное слева от строки формул).

Со временем в процессе работы с Excel вы заметите, что каждый вариант обладает своими преимуществами в определенных ситуациях. Иногда лучше нажать мишкой, а иногда перемещать курсор лучше клавишей «Enter». Также дополнительным примером может послужить данное задание как в Excel можно находить решения в несколько способов.

По умолчанию в Excel параметр перемещения курсора после нажатия клавиши «Enter» направлено в низ, на нижнюю ячейку (а если нажать SHIFT+ENTER, то курсор перейдет на верхнюю ячейку). При необходимости это можно изменить в настройках программы. Открываем окно «Параметры Excel» через меню «Файл»-«Параметры».

В появившемся окне следует выбрать параметр «Дополнительно». Нас интересует здесь: «Переход к другой ячейке после нажатия клавиши ВВОД». Ниже в опции направление указать желаемое… как показано на рисунке:

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



Быстрый переход к выбранным отдаленным ячейкам

В выше описанном задании мы перемещались по соседним ячейкам (С A1 до A2). Попробуйте в такой же способ переместить курсор (черный прямоугольник) на ячейку D3.

Как видите, в данной задаче самый короткий путь решения будет сделать 1 щелчок мышкой. Ведь для клавиатуры это 3 нажатия клавиш.

Для решения данной задачи можно еще использовать инструмент «Перейти…». Чтобы ним воспользоваться необходимо на закладке «Главная» открыть выпадающий список инструмента «Найти» и выбрать опцию «Перейти…». Или нажать комбинацию «горячих» клавиш CTRL+G или F5.

В появившемся окне впишите D3 (можно вписать малыми буквами d3, а программа сама заменяет маленькие буквы на большие и в формулах так же), после чего нажмите OK.

Для быстрого перемещения курсора в любой адрес ячейки листа, так же удобно использовать поле «Имя», которое находится в левом верхнем углу под полосой инструментов на одном уровне со строкой формул. Введите это поле D3 (или d3) и нажмите «Enter». Курсор мгновенно переместится по заданному адресу.

Если отдаленная ячейка, на которую следует переместить курсор, чтобы сделать ее активной, находится в пределах одного экрана (просмотра без прокрутки), то лучше воспользоваться мышкой. Если же ячейку нужно искать, но адрес ее известен заранее, то лучше воспользоваться полем «Имя» или инструментом «Перейти…».

В программе Excel большинство функций можно реализовать несколькими путями. Но далее в уроках мы будем использовать только самые короткие.

Перемещение курсора в конец листа

На практическом примере быстро проверим количество строк в листе.

Задание 1. Откройте новый чистый лист и поместите курсор в любую колонку. Нажмите на клавиатуре клавишу END, а потом «стрелка вниз» (или комбинация CTRL+«стрелка вниз»). И вы увидите, что курсор переместился в последнюю строку листа. Если нажать комбинацию клавиш CTRL+HOME, то курсор переместится в первую ячейку листа A1.

Теперь проверим адресное наименование последней колонки и их количество.

Задание 2. Нажмите комбинацию клавиш CTRL+«стрелка вправо» (или клавишу END, а потом «стрелка вправо»). Тогда курсор переместится в последнюю колонку с адресным наименованием XFD.

Чтобы узнать какая по счету последняя колонка листа, необходимо переключить стиль отображения адресов ссылок на ячейки. Для этого необходимо зайти: «Файл»-«Параметры»-«Формулы» и отметить галочку «Стиль ссылок R1C1», нажать OK. После чего в наименованиях колонок вместо букв будут отображаться цифры. Порядковый номер последней колонки листа 16384. После чего в таком же порядке снимите галочку, чтобы вернуть стандартный стиль колонок с латинскими буквами.

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

все уроки

Эксель (Excel) для Чайников: [ПОДРОБНОЕ РУКОВОДСТВО 2019]

Если вы никогда раньше не использовали табличный процессор для создания документов, советуем прочитать наше руководство Эксель (Excel) для чайников.

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

Подробная информация о базовых функциях и возможностях табличного процессора MS Excel.

Описание основных элементов документа и инструкция для работы с ними в нашем материале.

Кстати, чтобы эффективнее работать с таблицами Exel можете ознакомиться с нашим материалом Горячие клавиши Excel — Самые необходимые варианты.

Содержание:

Работа с ячейками. Заполнение и форматирование

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

Файл Эксель состоит из одного или нескольких листов, разграфленных на мелкие ячейки.

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

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

Рис. 1 – пример заполнения ячеек

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

При нажатии на ячейку в верхней части окна появится поле с ее адресом, названием и формулой (если клеточка учуствует в каких-либо расчетах).

Выберем ячейку «Доля акций». Адрес её расположения – А3. Эта информация указана в открывшейся панели свойств. Также мы можем увидеть содержимое.

Формул у этой клетки нет, поэтому они не показываются.

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

Кликните на клеточку правой клавишей манипулятора.

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

Рис. 2 – контекстное меню ячейки и ее основные свойства

к содержанию ↑

Сортировка данных

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

Перед вами уже заполненная таблица (как её создать разберемся дальше в статье). Представьте, что вам нужно отсортировать данные за январь по возрастанию.

Как бы вы это сделали? Банальное перепечатывание таблицы – это лишняя работа, к тому же, если она объемная, делать этого никто не будет.

Для сортировки в Excel есть специально отведенная функция. От пользователя требуется только:

• Выделить таблицу или блок информации;
• Открыть кладку «Данные»;
• Кликнуть на иконку «Сортировка»;

Рис. 3 – вкладка «Данные»

• В открывшемся окошке выберите колонку таблицы, над которой будем проводить действия (Январь).
• Далее тип сортировки (мы выполняем группировку по значению) и, наконец, порядок – по возрастанию.
• Подтвердите действие, нажав на «ОК».

Рис. 4 – настройка параметров сортировки

Произойдет автоматическая сортировка данных:

Рис. 5 – результат сортировки цифр в столбце «Январь»

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

к содержанию ↑

Математические расчеты

Главное преимущество Excel – возможность автоматического проведения расчетов в процессе заполнения таблицы.

К примеру, у нас есть две ячейки со значениями 2 и 17. Как в третью ячейку вписать их результат, не делая расчеты самостоятельно?

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

Затем нажмите на значок функции f(x), как показано на рисунке ниже.

В открывшемся окошке выберите действие, которое хотите применить. СУММ – это сумма, СРЗНАЧ – среднее значение и так далее.

Полный список функций и их наименований в редакторе Excel можно найти на официальном сайте компании Microsoft.

Нам нужно найти сумму двух ячеек, поэтому нажимаем на «СУММ».

Рис. 6 – выбор функции «СУММ»

В окне аргументов функции есть два поля: «Число 1» и «Число 2». Выберите первое поле и кликните мышкой на ячейку с цифрой «2».

Её адрес запишется в строку аргумента.

Кликните на «Число 2» и нажмите на ячейку с цифрой «17». Затем подтвердите действие и закройте окно.

Если необходимо выполнить математические действия с тремя или большим количеством клеточек, просто продолжайте вводить значения аргументов в поля «Число 3», «Число 4» и так далее.

Если в дальнейшем значение суммируемых ячеек будет изменяться, их сумма будет обновляться автоматически.

Рис. 7 – результат выполнения подсчетов

к содержанию ↑

Создание таблиц

В экселевских таблицах можно хранить любые данные.

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

Таблицы в Excel имеют преимущество перед аналогичной опцией в Word и других офисных программах.

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

К тому же, готовую таблицу можно интегрировать в файл docx с помощью обычной функции копирование-вставка.

Чтобы создать таблицу, следуйте инструкции:

• Откройте вкладку «Вставка». В левой части панели опций выберите пункт «Таблица». Если вам необходимо провести сведение каких-либо данных, выбирайте пункт «Сводная таблица»;
• С помощью мышки выделите место на листе, которое будет отведено для таблицы. А также вы можете ввести расположение данных в окно создания элемента;
• Нажмите ОК, чтобы подтвердить действие.

Рис. 8 – создание стандартной таблицы

Чтобы отформатировать внешний вид получившейся таблички, откройте содержимое конструктора и в поле «Стиль» кликните на понравившийся шаблон.

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

Рис. 9 – форматирование таблицы

Результат заполнения таблицы данными:

Рис. 10 – заполненная таблица

Читайте также:

к содержанию ↑

Добавление графиков/диаграмм

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

Чтобы создать диаграмму/график, нужно:

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

Рис. 11 – выбор типа используемой диаграммы

• Кликните на необходимую форму. На листе автоматически отобразится уже заполненная диаграмма:

Рис. 12 – результат добавление диаграммы

Если нужно изменить внешний вид получившейся визуализации, просто кликните по ней два раза.

Произойдёт автоматическое перенаправление в окно конструктора.

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

Аналогичным образом можно создать точечные графики, линейные диаграммы и схемы зависимости элементов таблицы.

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

Читайте также:

Как закрепить строку в Excel — Подробные инструкции

Тематические видеоролики:

Интерфейс программы

Microsoft Excel для Начинающих #1

Вводим данные

Microsoft Excel для Начинающих #2

Работаем с ячейками

Microsoft Excel для Начинающих #3

 

 

Самоучитель по работе в excel для чайников: уроки начинающим

Создание макроса – практический пример

Для примера возьмём самый обычный файл CSV. Это простая таблица 10х20, заполненная числами от 0 до 100 с заголовками для столбцов и строк. Наша задача превратить этот набор данных в презентабельно отформатированную таблицу и сформировать итоги в каждой строке.

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

Чтобы создать макрос, откройте View (Вид) > Macros (Макросы) > Record Macro (Запись макроса…)

Дайте своему макросу имя (без пробелов) и нажмите ОК.

Начиная с этого момента, ВСЕ Ваши действия с документом записываются: изменения ячеек, пролистывание таблицы, даже изменение размера окна.

Excel сигнализирует о том, что включен режим записи макроса в двух местах. Во-первых, в меню Macros (Макросы) – вместо строки Record Macro (Запись макроса…) появилась строка Stop Recording (Остановить запись).

Во-вторых, в нижнем левом углу окна Excel. Иконка Стоп (маленький квадратик) указывает на то, что включен режим записи макроса. Нажатие на неё остановит запись. И наоборот, когда режим записи не включен, в этом месте находится иконка для включения записи макроса. Нажатие на неё даст тот же результат, что и включение записи через меню.

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

Далее, введите в ячейки формулы в соответствии с названиями заголовков (даны варианты формул для англоязычной и русифицированной версии Excel, адреса ячеек – всегда латинские буквы и цифры):

• =SUM(B2:K2) или =СУММ(B2:K2)
• =AVERAGE(B2:K2) или =СРЗНАЧ(B2:K2)
• =MIN(B2:K2) или =МИН(B2:K2)
• =MAX(B2:K2) или =МАКС(B2:K2)
• =MEDIAN(B2:K2) или =МЕДИАНА(B2:K2)

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

После выполнения этого действия в каждой строке должны появиться соответствующие итоговые значения.

Далее, мы подведем итоги для всей таблицы, для этого делаем ещё несколько математических действий:

Соответственно:

• =SUM(L2:L21) или =СУММ(L2:L21)
• =AVERAGE(B2:K21) или =СРЗНАЧ(B2:K21) – для расчёта этого значения необходимо взять именно исходные данные таблицы. Если взять среднее значение из средних по отдельным строкам, то результат будет другим.
• =MIN(N2:N21) или =МИН(N2:N21)
• =MAX(O2:O21) или =МАКС(O2:O21)
• =MEDIAN(B2:K21) или =МЕДИАНА(B2:K21) – считаем, используя исходные данные таблицы, по причине указанной выше.

Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш Ctrl+A, либо щелкните по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов. Затем нажмите Comma Style (Формат с разделителями) на вкладке Home (Главная).

Далее, изменим внешний вид заголовков столбцов и строк:

• Жирное начертание шрифта.
• Выравнивание по центру.
• Заливка цветом.

И, наконец, настроим формат итоговых значений.

Вот так это должно выглядеть в итоге:

Если Вас все устраивает, остановите запись макроса.

Поздравляем! Вы только что самостоятельно записали свой первый макрос в Excel.

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

Чтобы очистить все ячейки от данных, щёлкните правой кнопкой мыши по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов, и из контекстного меню выберите пункт Delete (Удалить).

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

Важный момент! Если Вы сохраните файл с расширением XLTX, то макрос в нём работать не будет. Кстати, можно сохранить книгу как шаблон Excel 97-2003, который имеет формат XLT, он тоже поддерживает макросы.

Когда шаблон сохранён, можно спокойно закрыть Excel.

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

Формулы деление/умножение

Кроме суммирования существует функция деления в Excel, а также умножения. Ввести эти формулы можно аналогичным способом, выбрав вместо СУММ соответствующее действие.

Формула деления в Excel может быть введена вручную:

Через строку формул выполняется просмотр и коррекция данных. Введя числовые значения в таблицу, активируйте необходимое поле для размещения результата и кликните на поле строки fx.

рис.1. Введите с помощью клавиатуры знак «=»

Щелкните на ячейке с делимым числом.

рис.2. В строке формул появится ее адресрис.3. Введите кнопкой с клавиатуры знак «/» (деление)рис.4. Щелкните на ячейке с числом-делителемрис.5. Нажмите кнопку Enter, результат появится в соответствующем месте

Работа с лишними пробелами

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

Это проявляется в форматировании – числа с пробелами воспринимаются программой как текст

Убрать пробелы в ячейках Excel можно так:

рис.1. Скопируйте лишний пробел в ячейке

Перейдите на вкладку «Главная».

рис.2. Воспользовавшись функцией «Найти и выделить», а затем «Заменить»рис.3. Путем нажатия клавиш «Ctrl + H» можно выполнить быстрый вызов меню для поиска и замены

Рекомендуется сразу выделить весь диапазон ячеек и ввести скопированный пробел в появившемся меню. После чего нажать «Заменить все».

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

Нумерация ячеек и строк

Автоматическая нумерация строк в Excel выполняется несколькими способами. Простейший из них – перетаскивание специального маркера.

В Excel пронумеровать ячейки можно так:

Введите в соответствующие ячейки (две) числовые значения по порядку.

рис.1. Может использоваться последовательная нумерация, как на изображении ниже, или порядковые числа, например, «108» и «109»

рис.2. Выделите эти ячейки в один блок и наведите курсор на квадратик, расположенный в нижней части блока с правой стороны

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

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

Функции

Узнайте, как функции в Excel помогают сэкономить время. Если Вы только начинаете знакомство с функциями в Excel, то рекомендуем Вам сначала познакомиться с главой Введение в формулы и функции.

1. СЧЁТ и СУММ: Чаще всего в Excel используются функции, которые подсчитывают количество и сумму. Вы можете посчитать количество и сумму значений, выбранных по одному или по нескольким критериям.Примеры: Подсчёт вхождений текста, Подсчёт логических значений, Подсчёт пустых и непустых ячеек, Функция СУММПРОИЗВ.
2. Логические функции: Узнайте, как пользоваться логическими функциями Excel, такими как ЕСЛИ, И, ИЛИ.Примеры: Вложенные функции ЕСЛИ, Игра «Бросить кости».
3. Ссылки на ячейки: Ссылка на ячейку – это очень важный элемент Excel. Поймите разницу между относительной, абсолютной и смешанной ссылками, и Вы наверняка добьётесь успеха!Примеры: Копируем точную формулу, 3D ссылка, Создание внешней ссылки, Гиперссылки.
4. Дата и время: Чтобы ввести дату в Excel, используйте символы-разделители: слеш (/), тире (-) или точку (.). Чтобы ввести время, используйте двоеточие (:). Дату и время можно вводить в одну ячейку.Примеры: Функция РАЗНДАТ, Будние и рабочие дни, Дней до дня рождения, Табель, Последний день месяца, Праздники, Квартал, День года.
5. Текстовые функции: Excel предлагает огромное количество функций для самых различных операций с текстовыми строками.Примеры: Разделение строки, Количество экземпляров текста, Количество слов, Текст по столбцам, Верхний и нижний регистр, Удалить ненужные символы, Сравнение текста, Функции НАЙТИ и ПОИСК, Функции ПОДСТАВИТЬ и ЗАМЕНИТЬ.
6. Функции поиска и ссылок: Узнайте всё о функциях Excel для работы со ссылками и массивами, таких как ВПР, ГПР, ПОИСКПОЗ, ИНДЕКС и ВЫБОР. Примеры: Налоговые ставки, Функция СМЕЩ, “Левый” поиск, Двумерный поиск, Поиск максимального значения, Функция ДВССЫЛ.
7. Финансовые функции: Этот раздел рассказывает о наиболее популярных финансовых функциях Excel.Примеры: Кредиты различной длительности, Инвестиции и аннуитет, Расчёт сложных процентов, График погашения кредита, Амортизация.
8. Статистические функции: Здесь Вы найдёте обзор самых полезных статистических функций Excel.Примеры: Отрицательные числа на нули, Случайные числа, Функция РАНГ, ПЕРСЕНТИЛЬ и КВАРТИЛЬ, ПРЕДСКАЗ и ТЕНДЕНЦИЯ.
9. Округление: В этом разделе описаны три самых популярных функции для округления чисел в Excel. Это функции ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.Примеры: Отбрасываем десятичные знаки, Ближайшее кратное, Четные и нечетные.
10. Ошибки в формулах: Эта глава научит Вас справляться с некоторыми распространёнными ошибками в формулах Excel.Примеры: ЕСЛИОШИБКА, ЕОШИБКА, Циклическая ссылка, Зависимости формул, Ошибка плавающей запятой.
11. Формулы массива: Изучив эту главу, Вы будете понимать, что такое формулы массива в Excel. Одноячеечные формулы массива позволяют выполнять сразу серию вычислений в одной ячейке.Примеры: Подсчёт ошибок, Подсчёт уникальных значений, Подсчёт с критерием «ИЛИ», Суммирование каждой n-ой строки, Суммирование наибольших чисел, Суммирование диапазона с ошибками, Суммирование с критерием «ИЛИ», Поиск в двух столбцах, Наиболее часто встречающееся слово, Система линейных уравнений.

Пошаговое создание таблицы, вставка формул

Обычно в первой верхней строке с индексом 1 присутствуют наименования полей, или столбцов, а в строках вносятся отдельные записи. Поэтому первый шаг создания таблицы заключается в проставлении наименований столбцов (например, «Наименование товара», «Номер накладной», «Покупатель», «Количество», «Цена», «Сумма»).

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

Теперь поговорим о вычисляемых значениях. В нашем случае это столбец «Сумма», который равен умножению количества на цену. В нашем случае столбец «Сумма» имеет букву F, столбцы «Количество» и «Цена» – D и E соответственно.

Итак, ставим курсор в ячейку F2 и набираем символ «=». Это означает, что в отношении всех ячеек в столбце F будут произведены вычисления согласно некоей формуле.

Чтобы ввести саму формулу, выделяем ячейку D2, жмём символ умножения, выделяем ячейку E2, жмём Enter. В результате в ячейке D2 появится число, равное произведению цены на количество. Чтобы формула работала для всех строк таблицы, цепляем мышкой в ячейке D2 левый нижний угол (при наведении на него появится маленький крестик) и тащим его вниз до конца. В итоге формула будет работать для всех ячеек, но со своим набором данных, взятым в той же строке.

Если нам нужно распечатать таблицу, то для придания читабельного вида строки и столбцы нужно ограничить сеткой. Для этого выделяем участок таблицы с данными, включая строку с наименованиями столбцов, выбираем вкладку «Границы» (она расположена в меню «Шрифт») и жмём на символе окошка с надписью «Все границы».

Вкладка «Шрифт» позволяет форматировать шрифты по тому же принципу, ВТО и в Word. Так, шапку лучше оформить жирным начертанием, текстовые столбцы лучше выравнивать по левому краю, а числа – центрировать. Итак, вы получили первые навыки, как работать в Excel. Перейдём теперь к рассмотрению других возможностей табличного процессора.

Базовые особенности работы с таблицами

Один из самых главных элементов таблицы – ее название. Его можно увидеть во вкладке «Конструктор». Она отображается сразу после того, как будет нажата левая кнопка мыши на любую ячейку, входящую в нее. Название есть, даже если пользователь его не задает. Просто в таком случае по умолчанию дается имя «Таблица 1», «Таблица 2» и другие.

4

Если вы собираетесь использовать сразу несколько таблиц в вашем документе, то рекомендуем дать более понятные имена. В будущем тогда будет значительно проще понять, какая из них за что отвечает

Особенно это важно при работе с Power Query и Power Pivot. Давайте присвоим таблице имя «Отчет»

В Excel есть отдельная функция, предназначенная для того, чтобы просмотреть, какие таблицы и именованные диапазоны есть и быстро управлять их названиями. Для того, чтобы ею воспользоваться, необходимо открыть вкладку «Формулы», после чего найти пункт «Диспетчер имен».

5

Увидеть название таблицы можно и при ручном вводе формулы.

6

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

7

Начинающий пользователь сразу скажет: «Боже, как можно все это выучить»? Но на самом деле, этого не нужно делать, поскольку в ходе набора формулы появляются подсказки. Главное – не забыть открыть квадратную скобку (ее можно найти в английской раскладке там, где у нас находится кнопка «х»).

8

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

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

=Отчет

Простыми словами, ссылка указывает не на какой-то определенный диапазон, а на всю колонку таблицы.

9

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

Атанас Йонков Блоггер, Веб-разработчик [email protected]

В этом уроке я покажу Вам самые популярные макросы в VBA Excel, которые вы сможете использовать для оптимизации своей работы. VBA – это язык программирования, который может использоваться для расширения возможностей MS Excel и других приложений MS Office. Это чрезвычайно полезно для пользователей MS Excel, поскольку VBA может использоваться для автоматизации вашей работы и значительно увеличить Вашу эффективность. В этой статье Вы познакомитесь с VBA и я вам покажу некоторые из наиболее полезных, готовых к использованию примеров VBA. Вы сможете использовать эти примеры для создания собственных скриптов, соответствующих Вашим потребностям.

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

Table of Contents

Как включить макросы в Excel

В Excel нажмите комбинацию клавиш alt + F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку Microsoft Excel Objects слева и выберите Insert => Module. Это место, где сохраняются макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel как макрос. Из табуляции File => Save as, выберите Save as macro-enabled Workbok (расширение .xlsm) Теперь пришло время написать свой первый макрос!

1. Копирование данных из одного файла в другой.

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

2. Отображение скрытых строк

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

14. Отправка активного файла по электронной почте

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

15. Вставка всех графиков Excel в презентацию PowerPoint

Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:

16. Вставка таблицы Excel в MS Word

Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

17. Извлечение слов из текста

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

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

Как создать таблицу в Excel для чайников

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

Посмотрите внимательно на рабочий лист табличного процессора:

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

Сначала давайте научимся работать с ячейками, строками и столбцами.

Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Для выделения строки – по названию строки (по цифре).

Чтобы выделить несколько столбцов или строк, щелкаем левой кнопкой мыши по названию, держим и протаскиваем.

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

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

1. Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши.
2. Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы.
3. Если нужно сохранить ширину столбца, но увеличить высоту строки, воспользуемся кнопкой «Перенос текста» на панели инструментов.

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

Выделяем столбец /строку правее /ниже того места, где нужно вставить новый диапазон. То есть столбец появится слева от выделенной ячейки. А строка – выше.

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+»=»).

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+»=».

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Пошаговое создание таблицы с формулами

1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

Теперь при печати границы столбцов и строк будут видны.

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

Поменяйте, к примеру, размер шрифта, сделайте шапку «жирным». Можно установить текст по центру, назначить переносы и т.д.

Создание таблицы Excel

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

1

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

2

Второй – воспользоваться горячими клавишами Ctrl + T. Далее появится небольшое окошко, в котором можно более точно указать диапазон, входящий в таблицу, а также дать Excel понять, что в таблице содержатся заголовки. В качестве них будет выступать первая строка.

3

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

Перед тем, как разбираться в особенностях работы с таблицами, необходимо разобраться, как она устроена в Excel.

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС/ПОИСКПОЗ намного лучше, чем ВПР. Однако, многие пользователи Excel по-прежнему прибегают к использованию ВПР, т.к. эта функция гораздо проще. Так происходит, потому что очень немногие люди до конца понимают все преимущества перехода с ВПР на связку ИНДЕКС и ПОИСКПОЗ, а тратить время на изучение более сложной формулы никто не хочет.

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: покажет эту возможность в действии.

2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

Используя ПОИСКПОЗ/ИНДЕКС, Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР.

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.

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

С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто совершает поиск и возвращает результат, выполняя аналогичную работу заметно быстрее.

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

Выводы

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

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

Как освоить Excel за 11 шагов

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

Поэтому у вас может возникнуть желание научиться осваивать Excel, чтобы работать быстрее, проще и эффективнее.

В этой статье вы найдете 11 лучших советов и приемов, которые помогут вам использовать возможности Excel и произвести впечатление на своего начальника и коллег по работе.

Как быстро освоить Excel за 11 шагов

1. Как перемещаться по интерфейсу

Хорошее начало — научиться эффективно ориентироваться в интерфейсе Excel. Начнем с основ.

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

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

Нажав клавишу Ctrl и Вверх , Вниз , Влево или Вправо , вы перейдете к последней использованной ячейке в этом направлении.

Это очень удобно для навигации по большим спискам.

Одновременное нажатие клавиш Ctrl и Home приведет вас к первой ячейке в диапазоне данных.

2. Изучите несколько полезных сочетаний клавиш

Лучший способ ускорить повседневную работу с Excel — это выучить несколько полезных сочетаний клавиш с клавиатурой, мышью или сенсорной панелью.

Если вы новичок, начните с изучения основных сочетаний клавиш. Хорошие для изучения включают Ctrl + C и Ctrl + V для копирования и вставки. И Ctrl + Z для отмены последнего действия.

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

Два моих любимых сочетания клавиш: Ctrl + ; , чтобы ввести сегодняшнюю дату в ячейку. И дважды щелкните маркер заполнения (квадрат в правом нижнем углу ячейки), чтобы скопировать его в конец списка.

В Excel имеется более 500 сочетаний клавиш и множество полезных приемов для ускорения вашей работы.

Хотя вам не понадобятся все эти программы, выучить некоторые из них, чтобы упростить работу с Excel, — отличная идея.

Готовы начать использовать ярлыки Excel?

Загрузите нашу шпаргалку для печати ярлыков для Excel.

Введите адрес электронной почты

3. Закрепить панели

Эта замечательная функция позволяет всегда видеть ваши заголовки и другие метки при прокрутке электронной таблицы. Знание Excel является важным навыком.

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

На изображении ниже мы видим строку 1259, но заголовки все еще видны на экране.

Вы можете закрепить столько строк и столбцов, сколько вам нужно.

Щелкните первую ячейку после строк и столбцов, которые вы хотите закрепить. Например, если вы хотите закрепить 3 строки и 1 столбец, щелкните ячейку B4.

Щелкните Вид > Закрепить области > первый параметр Закрепить области.

Теперь при прокрутке вниз первые 3 строки остаются видимыми. И если вы прокрутите вправо, столбец A останется видимым.

4. Узнайте, как освоить формулы Excel

Один из основных способов освоить Excel – это научиться писать формулы. Это мускулы Excel.

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

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

Затем начните изучать некоторые из наиболее часто используемых функций. К ним относятся СУММ, ЕСЛИ, ВПР, СЧЁТЕСЛИ и СЦЕПИТЬ.

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

Ознакомьтесь с некоторыми из наших любимых формул и функций Excel.

5. Создайте простой раскрывающийся список

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

Для создания выпадающего списка;

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

2. Щелкните Данные > Проверка данных.

3. Выберите Список из списка разрешенных.

4. Затем либо введите элементы списка в поле источника, разделив их запятой. Или выберите диапазон ячеек, содержащих элементы списка.

Теперь у вас есть раскрывающийся список, делающий ввод данных простым и надежным.

6. Визуализация ключевых данных с помощью условного форматирования

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

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

Для быстрого примера, если значение больше 300, мы хотим изменить цвет ячейки на зеленый.

1. Выберите диапазон ячеек, к которым вы хотите применить условное форматирование.

2. Щелкните Главная > Условное форматирование > Правила выделения ячеек > Больше.

3. Введите 300 и выберите форматирование, которое хотите применить.

Здесь используется зеленая заливка и цвет шрифта, но доступно множество вариантов форматирования.

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

7. Мгновенное заполнение

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

Возьмем, к примеру, список ниже. Мы хотим извлечь имя из списка в столбце A.

Введите первое имя в ячейку B2, а затем второе имя в ячейку B3, и по мере того, как вы вводите Flash Fill, подхватывает шаблон вашего поведения и спрашивает, хотите ли вы, чтобы он повторялся в остальных строках. Это потрясающе!

Нажмите Enter, и вуаля, данные извлечены.

Существует несколько способов запуска Flash Fill. Вы также можете найти кнопку на вкладке Data ленты, и вы можете управлять ею с помощью сочетания клавиш Ctrl + E .

8. Суммируйте данные с помощью сводных таблиц

Сводные таблицы — один из самых удивительных инструментов в Excel. Они делают суммирование больших наборов данных простым как 1, 2, 3.

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

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

Для создания сводной таблицы;

1. Щелкните в списке данных, которые вы хотите обобщить.

2. Щелкните Вставка > Сводная таблица.

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

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

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

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

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

Сводные таблицы предлагают гораздо больше, но это выходит за рамки данной статьи.

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

9. Защита данных Excel

Функции Excel, такие как сводные таблицы, формулы и условное форматирование, будут работать, только если данные точны.

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

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

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

1. Выберите диапазон(ы) ячеек, которые вы хотите разблокировать (удерживайте нажатой клавишу Ctrl, чтобы выбрать несколько диапазонов одновременно).
2. Нажмите Ctrl + 1 , чтобы открыть диалоговое окно «Формат ячеек».
3. Перейдите на вкладку Защита , снимите флажок Блокировка окна и нажмите Ok .
4. Щелкните Просмотр > Защитить лист .
5. Вы можете ввести пароль для дополнительной безопасности, но это необязательно (обычно его не вводят, так как вы обычно защищаете от несчастных случаев, и пароль не нужен). Установите флажки для всех функций, которые необходимо выполнить пользователям, и нажмите Ok .

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

10. Включите питание с помощью Power Query и Power Pivot

Знание этих двух инструментов Power выведет вас на уровень выше обычного пользователя.

Power Query — это инструмент, используемый для импорта и формирования данных, готовых к анализу. Он находится на вкладке «Данные» ленты.

С помощью Power Query вы можете легко импортировать данные из различных (и постоянно меняющихся) мест, включая CSV, Интернет и из папки.

Затем редактор Power Query предоставляет удобную среду для выполнения ряда операций по очистке и формированию, таких как разделение столбцов, форматирование, удаление дубликатов и удаление сводных данных.

Power Query использует язык под названием M. Его сложно освоить, и, к счастью, он не нужен 99% пользователей Excel. Редактор предоставляет все, что может понадобиться пользователю.

Каждое сделанное редактирование сохраняется как шаг и может быть обновлено в будущем одним нажатием кнопки.

Power Pivot (также известная как модель данных) — это инструмент, позволяющий хранить огромные объемы данных. Хранение данных в Power Pivot позволяет выйти за пределы физических ограничений Excel.

В Power Pivot вы также можете создавать отношения между несколькими таблицами данных и писать функции, известные как функции DAX.

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

Это важный инструмент для понимания всем, кто занимается анализом огромных объемов данных или нуждается в выполнении сложного анализа.

---

Бонус:  Познакомьтесь с уроком Кена Пулса из его курса Power Pivot, в котором объясняется, что такое Power Pivot и как он может упростить рабочий процесс с Excel.

---

11. Создание макросов с помощью VBA

Макросы позволяют автоматизировать повторяющиеся задачи для повышения производительности.

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

Вы можете начать работу с макросами, записав свое выполнение задач Excel. Это сгенерирует код VBA и создаст макрос.

Вы можете найти средство записи макросов на вкладке «Вид» на ленте. Это последняя кнопка.

Запись макросов может сэкономить вам и вашим коллегам много времени.

Если вы хотите пойти дальше, вы можете изучить Excel VBA и редактировать свои макросы.

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

Чтобы увидеть код VBA, созданный записью;

Щелкните Вид > Макросы, выберите макрос в списке и щелкните Изменить.

Хотите освоить Excel сегодня?

Из всех советов о том, как освоить Excel, лучше всего пройти структурированный онлайн-курс Excel, который научит вас всему, что вам нужно знать. Практикуйтесь вместе с Microsoft MVP и изучайте новейшие навыки, чтобы повысить эффективность своего рабочего места уже сегодня!

Повысьте уровень своих навыков работы с Excel

Станьте сертифицированным мастером Excel с небольшими курсами GoSkills

Начать бесплатную пробную версию

Рабочая тетрадь Excel

для чайников, 2-е издание

Введение 1

Об этой книге 1

Конвенции, используемые в этой книге 2

Глупы

Часть 1. Создание рабочих листов 5

Глава 1. Знакомство с интерфейсом Excel 7

Запуск Excel 7

Определение частей окна Excel 9

Выбор команд в представлении Backstage 10

Выбор команд на ленте 11

Добавление пользовательской вкладки на ленту Excel 13

Добавление команд в группы на пользовательской вкладке 14

Выбор команд на панели быстрого доступа 15

Отображение панели быстрого доступа 16

Настройка панели быстрого доступа 16

Добавление дополнительных команд на панель быстрого доступа 17

Глава 2. Ввод данных рабочего листа 21

Открытие новой рабочей книги 21

Перемещение по рабочей книге 24

Перемещение в пределах отображаемой области 24

Перемещение в новую область рабочего листа 25

90 3 90 2 в рабочей книге Выбор диапазонов ячеек 28

Ввод данных в ячейки 30

Ввод данных в одну ячейку 30

Ввод данных в диапазон 32

Заполнение ряда данных с помощью маркера заполнения 33

Копирование формулы с помощью маркера заполнения 34

Сохранение данных электронной таблицы в файле рабочей книги 35

Глава 3: Форматирование рабочего листа 37

Изменение размеров и строки 37

.

Методы форматирования ячеек 40

Форматирование ячеек с помощью вкладки «Главная» ленты 41

Форматирование ячеек с помощью диалогового окна «Формат ячеек» 46

Использование стилей ячеек 52

Использование условного форматирования 53

Укрытие столбцов и строк 56

Глава 4: Отчеты о печатностях 59

Предварительные страницы на рабочем листе и закулисные виды 59

Справочные перерывы 61

Adgempore Headers 63

.

Добавление заголовков печати к отчету 66

Изменение параметров печати отчета 68

Печать всей рабочей книги или ее части 71

Печать диапазона 71

Печать всей рабочей книги 72

печатные диаграммы в рабочем листе 74

Печать Формулы листа 76

Глава 5: Модификация рабочего листа 79

Нахождение и идентифицируя область, которая требует EDITICE 79

3

92629 20003

. Диапазоны для редактирования 82

Редактирование записей данных 84

Удаление и вставка данных и ячеек 86

Перемещение и копирование данных и ячеек 88

Добавление примечаний к рабочему листу 92

Using Find and Replace and Spell Checking 93

Editing Multiple Worksheets 97

Part 2: Using Formulas and Functions 101

Chapter 6: Building Formulas 103

Building Formulas 103

Building formulas вручную 104

Построение формул с помощью встроенных функций 110

Редактирование формул 114

Изменение естественного порядка операций 115

Использование внешних ссылок 118

Контролирование, когда формулы пересчитаны 120

Глава 7: Копирование и корректирование формул 123

Копирование формул с относительной ссылки 124

С. Имена в формулах 132

Построение формул массива 136

Отслеживание и устранение ошибок в формулах 140

Работа с циклическими ссылками 142

Глава 8: Создание даты и времени и времени. Глава 9: Финансовые формулы и функции 155

Работа с финансовыми функциями 155

Использование основных инвестиционных функций 156

. Показание амортизации актива 162

Глава 10: Использование математических функций 167

Значения округления 167

Обнаружение продуктов, мощности и квадратных корней 169

. Выполняют подводные суммы 172

. 172

Условные суммы 175

0003

Подсчет ячейки 182

Использование статистических функций в анализе ToolPak Add-In 185

Глава 12: Использование функций Lookup 187

Посмотреть материал с Xlookup 188

.

Выполнение горизонтального поиска 191

Выполнение вертикального поиска 193

Глава 13. Использование логических функций 197

Работа с логическими функциями 197

Конструирование формул принятия решений 198

Выбор между альтернативными значениями 198

Выбор между альтернативными расчетами 202

Гнездование.

Построение текстовых формул 209

Использование текстовых функций 213

Часть 3. Работа с графикой 217

ГЛАВА 15: Данные рабочего листа диаграммы 219

Понимание диаграмм Excel 219

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

Форматирование. Объекты 231

Добавление различных типов графических объектов 235

Вставка сборных рисунков 236

Импорт графических файлов 237

Добавление графических фигур и текстовых полей 241

Конструирование WordArt 243

Конструирование SmartArt 245

Часть 4: Управление и защиту данных 249

Глава 17: Строительство и поддержание таблиц 251

Создание таблицы 251

251

Создание таблицы 251

251

. 252

Редактирование записей в форме данных 255

Сортировка таблиц 257

Использование ключей сортировки 258

Сортировка таблицы по нескольким ключам 259

Сортировка полей (столбцов) в таблице 260

Subtotaling A Table 262

Фильтрация таблицы 264

Запрос таблицы внешней базы данных 267

Глава 18: Защита рабочи Ввод данных в защищенный лист 278

Защита всей книги 280

Часть 5. Анализ данных 283

Глава 19: Выполнение анализа «что, если» 285

Выполнение анализа «что, если» с таблицами данных 285

Создание таблицы данных с одной переменной 286

Создание таблицы данных с двумя переменными 289

Анализ данных с помощью сценариев 20 3290 09 Поиск целей на рабочем листе 294

Создание сложных моделей с помощью Solver 296

0003

Модификация Pivottable 304

Модификация Pivottable Formatting 305

Pivoting the Pivottable Fields 306

Изменение Pivottable Summary Function и добавление расчетных полей 307

.

Глава 21. Использование макросов0003

Запись и воспроизведение Macros 318

Запись макросов с относительными ячейками.

Использование редактора Visual Basic 328

Редактирование записанного макроса 329

Добавление диалогового окна для обработки пользовательского ввода 331

Создание пользовательских функций 334

Использование пользовательской функции на листе 337

Сохранение пользовательских функций в файлах надстроек 338

Часть 7. Часть десятков 341

Глава 23. Десять основных функций Excel 32 2

30 Лента Excel и представление Backstage 343

Условное форматирование 344

Фильтрация сводных таблиц с помощью срезов 344

Форматирование и редактирование вкладки «Главная» 344

Вставка диаграммы на вкладке 345

Форматировать как таблицу 3450003

Слайдер Zoom на панели состояния 345

Page Mayout View 345

Galleries Style 346

ПРЕДУПРЕЖДЕНИЕ ПРЕДУПРЕЖДЕНИЯ 346

ГЛАВА 24: TOP DEVER для использования Excel Like Pro 347

Gethipting Workbou из шаблонов 347

Организация данных на разных листах 348

Создание рядов данных с автозаполнением 349

Использование имен диапазонов 349

Закрепление заголовков столбцов и строк 350

Предотвращение ошибок ввода данных с помощью проверки данных 350

Перехват значений ошибок 351

Сохранение памяти с помощью формул массива 352

Использование контуров для управления отображением данных 352

Использование просмотра рядом для работы с двумя книгами 353

Указатель 355

Учебное пособие Excel для чайников Пола Макфедриса, Грега Харви, мягкая обложка

Введение 1

Об этой книге 1

Условные обозначения, используемые в этой книге 2

Глупые предположения 2

За рамками книги 3

Значки, используемые в этой книге 3

Куда двигаться дальше 4

Часть 1 Создание рабочих листов 5

Глава 1 Знакомство с интерфейсом Excel 7 09 Запуск Excel 09 3 9000

Идентификация частей окна Excel 9

Выбор команд в представлении Backstage 10

Выбор команд на ленте 11

Добавление пользовательской вкладки на ленту Excel 13

9Глава 2 Ввод данных рабочего листа 21

Открытие новой рабочей книги 21

Перемещение по рабочей книге 24

Перемещение в пределах отображаемой области 24

Перемещение в новую область рабочего листа 25

Переход на другой лист рабочей книги 27

Выбор диапазонов ячеек 28

Создание записей в ячейках 30

Ввод данных в одну ячейку 30

Ввод данных в диапазон 32

Заполнение ряда данных маркер заполнения 33

Копирование формулы с маркером заполнения 34

Сохранение данных электронной таблицы в файле рабочей книги 35

Глава 3 Форматирование рабочего листа 37

Изменение размеров столбцов и строк 37

Настройка ширины столбцов в соответствии с данными 38

Управление высотой определенных строк 39

Методы форматирования ячеек 40

Форматирование ячеек с помощью вкладки «Главная» ленты 41

Форматирование ячеек с помощью диалогового окна «Формат ячеек» Использование стилей ячеек 46

Использование условного форматирования 53

Скрытие столбцов и строк 56

Глава 4 Печать отчетов рабочего листа 59

Предварительный просмотр страниц в рабочем листе и представлении Backstage 59

Настройка разрывов страниц 61

Добавление верхних и нижних колонтитулов 63

Добавление заголовков печати к отчету 66

Изменение параметров печати отчета 68

Печать всей рабочей книги или ее части 71

Печать диапазона a Печать всей рабочей книги 72

Печать диаграмм на рабочем листе 74

Печать формул рабочего листа 76

Глава 5 Изменение рабочего листа 79

Поиск и определение области, которую необходимо отредактировать 79

Выбор диапазонов для редактирования 82

Редактирование записей данных 84

Удаление и вставка данных и ячеек 86

Перемещение и копирование данных и ячеек 88

Редактирование нескольких листов 97

Часть 2 Использование формул и функций 101

Глава 6 Построение формул 103

Построение формул 103

Построение формул вручную 104

Формулы здания со встроенными функциями 110

Редактирование формул 114

Изменение естественного порядка эксплуатации 115

Использование внешних ссылок 118

КОНТРОЛИ Копирование формул с относительными ссылками 124

Копирование формул с абсолютными ссылками 126

Копирование формул со смешанными ссылками 128

Использование имен диапазонов в формулах 132

Формулы для массива здания 136

Трассирование и устранение ошибок формулы 140

Обращение с круговыми ссылками 142

ГЛАВА 8 с функциями даты Fancier Excel 150

Работа с функциями времени 152

Глава 9 Финансовые формулы и функции 155

Работа с финансовыми функциями 155

Использование основных инвестиционных функций 156

. Показатели амортизации актива 162

Глава 10 Использование математических функций 167

Значения закругления 167

Обнаружение продуктов, полномочий и квадратных корней 169

. Сумма более 172

Суммирование произведений, квадратов и их разностей 172

Условные суммы 175

0003

Подсчет ячеек 182

Использование статистических функций в надстройке Analysis ToolPak 185

Глава 12 Использование функций поиска 187

Поиск материала с помощью функции XLOOKUP 188

Возврат одиночных значений 909 Поиск90 по таблице a0090 0 1 191

Выполнение вертикального поиска 193

Глава 13 Использование логических функций 197

Работа с логическими функциями 197

Построение формул для принятия решений 198

Выбор между альтернативными значениями 198

Выбор между альтернативными вычислениями 202

гнездование.

Часть 3 Работа с графикой 217

Глава 15 Отображение данных рабочего листа 219

Знакомство с диаграммами Excel 219

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

Formatting Charts 226

Editing Charts 229

Chapter 16 Adding Graphics to Worksheets 231

Understanding Graphic Objects 231

Adding Various Types of Graphic Objects 235

Inserting prefab art 236

Importing graphics files 237

Adding graphic фигуры и текстовые поля 241

Создание WordArt 243

Создание SmartArt 245

Часть 4 Управление и защита данных 249

Глава 17 Создание и обслуживание таблиц 251

Создание таблицы 251

Добавление записей в новую таблицу 252

Редактирование записей в форме данных 255

Сортировка таблиц 257

Использование ключей сортировки 258 столбцы) в таблице 260

Подведение итогов таблицы 262

Фильтрация таблицы 264

Запрос таблиц внешней базы данных 267

Глава 18 Защита рабочих книг и данных рабочих листов 271

Охрана пароля, защищающая рабочую книгу 272

Защита рабочего листа 274

Введение данных в защищенном рабочем листе 278

Защита всей рабочей книги 280

ЧАСТЬ 5. Выполнение анализа «что, если» с помощью таблиц данных 285

Создание таблицы данных с одной переменной 286

Создание таблицы данных с двумя переменными 289

Анализ данных с помощью сценариев 292

В поисках целей на рабочем листе 294

Создание сложных моделей с решательностью 296

Глава 20 Генерация Pivottables 299

Работа с Pivottables 299

Создание Pivottables 302

Модифицируя Pivottable 304

Modififitififitabifitable The Pivottable Formattablettablettablettytting 3053

. Сведение полей сводной таблицы 306

Изменение сводной функции сводной таблицы и добавление вычисляемых полей 307

Создание сводных диаграмм 310

Часть 6 Макросы и Visual Basic для приложений 315

Глава 21 Использование макросов 317

Создание макросов 317

Запись и воспроизведение макросов 318

Запись макросов с относительными ссылками на ячейки 321

3 Назначение макросов на панель быстрого доступа

Назначение макросов ленте 324

Глава 22 Использование редактора Visual Basic 327

Использование редактора Visual Basic 328

Редактирование записанного макроса 329

Добавление диалогового окна, обрабатывающего вводимые пользователем данные 331

Создание пользовательских функций 334

Использование пользовательской функции на листе 337

Сохранение пользовательских функций в дополнительных файлах 338

Часть 7.