Эксель для чайников 2019 – Уроки Excel для чайников и начинающих пользователей

Содержание

Функция ВПР в excel для чайников, без воды и пространных рассуждений

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

Что такое функция ВПР в Эксель – область применения

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

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

В случаях, когда работников предприятия всего два-три, или товаров – до десятка, можно сделать все вручную. При должной внимательности работать человек будет без ошибок. Но если значений для обработки, например, тысяча, требуется автоматизация работы. Для этого в Excel существует ВПР (анг. VLOOKUP).

Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.

Исходные данные таблица 1

Ф. И. О.	Штраф
Петров	12 000,00 ₽
Сидоров	200,00 ₽
Иванов	38 000,00 ₽

Исходные данные таблица 2

Ф. И. О.	З.П.
Иванов	20 000 ₽
Петров	19 000 ₽
Сидоров	21 000 ₽

Объединенные данные таблица 3

Ф. И. О.	З.П.	Штраф
Иванов	20 000 ₽	38 000 ₽
Петров	19 000 ₽	12 000 ₽
Сидоров	21 000 ₽	200 ₽

Функция ВПР в Excel – как пользоваться

Для того чтобы таблица 1 пришла к конечному виду, в ней вписываем заголовок столбца, например «Штраф». На самом деле, это необязательно, можно написать любой текст, или оставить его незаполненным. Работать функция будет также по клику мыши в поле, где должно появиться найденное в другой таблице значение.

Теперь нужно вызвать функцию. Это можно сделать разными способами:

Прописать функцию вручную.
Нажать «Вставить функцию», выбрать «Ссылки и массивы», выбрать ВПР.
Открыть вкладку «Формулы», выбрать «Ссылки и массивы», выбрать ВПР.

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

Необходимо заполнить значения для функции ВПР

Искомое_значение – это то, что Эксель будет искать в другой таблице. В нашем случае, Иванов, Петров, Сидоров. Кликаем по первой фамилии в списке это ячейка А2. Удерживаем на клавиатуре клавишу Shift, после чего кликаем по последней фамилии в столбце, ячейка А4. В итоге получаем выделенный список фамилий с которым ВПР будет работать.
Таблица – место где будет осуществлен поиск. В нашем случае вторая таблица лежит в Лист 2. Переходим на него. Здесь необходимо выделить весь интервал поиска. Кликаем по левой верхней ячейке интервала. Удерживаем на клавиатуре Shift и нажимаем левой кнопкой мыши правую нижнюю ячейку. Нажимаем F4. Интервал поиска задан.

Номер_столбца – номер столбца из таблицы, в которой ищутся данные. В нашем случае – 2.
Интервальный_просмотр – это логическое значение. В нашем случае необходимо написать «ЛОЖЬ».
Нажать «ОК»
Теперь необходимо скопировать функцию в каждую пустую ячейку. Можно, например, сделать с помощью копировать — вставить, а можно нажать на ячейку с результатом работы функции, и в правом нижнем углу кнопкой мыши слева нажать на жирный квадратик, не отпуская левую кнопку потянуть вниз.

Результат налицо – в таблице 3 (смотреть выше).

ВПР – инструкция для работы с двумя условиями

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

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

Характеристики телефонов таблица 4

Название	ОЗУ	Камера
ZTE	0,5	5
ZTE	1	5
DNS	1	8
DNS	0,5	8
Alcatel	1	8
Alcatel	256	2

Характеристики телефонов таблица 5

Название	ОЗУ	Цена
ZTE	0,5	1 990 ₽
ZTE	1	3 099 ₽
DNS	1	3 100 ₽
DNS	0,5	2 240 ₽
Alcatel	1	4 500 ₽
Alcatel	256	450 ₽

Пример выбран на телефонах, но понятно, что данные могут быть совершенно любыми. Как видно из таблиц, марки телефонов не отличаются, а отличаются ОЗУ и Камера. Для создания сводных данных нам нужно выбрать телефоны по марке и ОЗУ. Для работы функции ВПР по нескольким условиям нужно столбцы с условиями объединить.

Добавляем крайний левый столбец. Например, называем его «Объединение». В первую ячейку значений, у нас B 2, пишем конструкцию «= B 2& C 2». Размножаем с помощью мыши. Получается, как в таблице 6.

Характеристики телефонов таблица 6

Объединение	Название	ОЗУ	Цена
ZTE 0,5	ZTE	0,5	1 990 ₽
ZTE 1	ZTE	1	3 099 ₽
DNS1	DNS	1	3 100 ₽
DNS 0,5	DNS	0,5	2 240 ₽
Alcatel 1	Alcatel	1	4 500 ₽
Alcatel 256	Alcatel	256	450 ₽

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

Смотрите видеоурок как пользоваться функцией ВПР в Эксель для чайников:

Функция ВПР в Экселе не представляет ничего сложного в работе, а открывающиеся возможности для обработки данных огромны.

komp.guru

работа с таблицами, графиками…: dartii

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

После этого вы сможете создать свой первый табличный лист с таблицами, графиками, математическими формулами и форматированием.
Подробная информация о базовых функциях и возможностях табличного процессора MS 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

источник

dartii.livejournal.com

эксель для чайников видео

Уважаемые участники проекта «Нефинансист», если есть вопросы, то пожалуйста пиши…

Уроки Excel. Основы Excel для начинающих….

…

Первая часть по работе с диаграммами….

Пять первых шагов к освоению программы Excel. Базовый видеокурс по работе в программе Эксе…

Именные ячейки. Как перевернуть таблицу. Как разбить текст по столбцам….

Закрепление областей, консолидация….

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

inlove.kz

Функция ВПР в Excel для чайников

Многие наши ученики говорили нам, что очень хотят научиться использовать функцию

ВПР (VLOOKUP) в Microsoft Excel. Функция ВПР – это очень полезный инструмент, а научиться с ним работать проще, чем Вы думаете. В этом уроке основы по работе с функцией ВПР разжеваны самым доступным языком, который поймут даже полные «чайники». Итак, приступим!

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

Что такое ВПР?

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

Сейчас мы найдём при помощи ВПР цену товара Photo frame. Вероятно, Вы и без того видите, что цена товара

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

Мы вставим формулу в ячейку E2, но Вы можете использовать любую свободную ячейку. Как и с любой формулой в Excel, начинаем со знака равенства (=). Далее вводим имя функции. Аргументы должны быть заключены в круглые скобки, поэтому открываем их. На этом этапе у Вас должно получиться вот что:

=VLOOKUP(
=ВПР(

Добавляем аргументы

Теперь добавим аргументы. Аргументы сообщают функции ВПР, что и где искать.

Первый аргумент – это имя элемента, который Вы ищите, в нашем примере это Photo frame. Так как аргумент текстовый, мы должны заключить его в кавычки:

=VLOOKUP("Photo frame"

=ВПР("Photo frame"

Второй аргумент – это диапазон ячеек, который содержит данные. В нашем случае данные содержатся в диапазоне A2:B16. Как и с любой другой функцией Excel, Вы должны вставить разделитель между аргументами (запятая в англоязычной версии Excel или точка с запятой – в русифицированной версии).

=VLOOKUP("Photo frame",A2:B16
=ВПР("Photo frame";A2:B16

Важно помнить, что ВПР всегда ищет в первом левом столбце указанного диапазона. В этом примере функция будет искать в столбце A значение Photo frame. Иногда Вам придётся менять столбцы местами, чтобы нужные данные оказались в первом столбце.

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

=VLOOKUP("Photo frame",A2:B16,2
=ВПР("Photo frame";A2:B16;2

Четвёртый аргумент сообщает функции ВПР, нужно искать точное или приблизительное совпадение. Значением аргумента может быть TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Если TRUE (ИСТИНА), формула будет искать приблизительное совпадение. Данный аргумент может иметь такое значение, только если первый столбец содержит данные, упорядоченные по возрастанию. Так как мы ищем точное совпадение, то наш четвёртый аргумент будет равен FALSE (ЛОЖЬ). На этом аргументы заканчиваются, поэтому закрываем скобки:

=VLOOKUP("Photo frame",A2:B16,2,FALSE)
=ВПР("Photo frame";A2:B16;2;ЛОЖЬ)

Готово! После нажатия Enter, Вы должны получить ответ: 9.99.

Как работает функция ВПР?

Давайте разберёмся, как работает эта формула. Первым делом она ищет заданное значение в первом столбце таблицы, выполняя поиск сверху вниз (вертикально). Когда находится значение, например, Photo frame, функция переходит во второй столбец, чтобы найти цену.

ВПР – сокращение от Вертикальный ПРосмотр, VLOOKUP – от Vertical LOOKUP.

Если мы захотим найти цену другого товара, то можем просто изменить первый аргумент:

=VLOOKUP("T-shirt",A2:B16,2,FALSE)
=ВПР("T-shirt";A2:B16;2;ЛОЖЬ)

или:

=VLOOKUP("Gift basket",A2:B16,2,FALSE)
=ВПР("Gift basket";A2:B16;2;ЛОЖЬ)

Другой пример

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

Чтобы определить категорию, необходимо изменить второй и третий аргументы в нашей формуле. Во-первых, изменяем диапазон на A2:C16, чтобы он включал третий столбец. Далее, изменяем номер столбца на 3, поскольку категории содержатся в третьем столбце.

=VLOOKUP("Gift basket",A2:C16,3,FALSE)
=ВПР("Gift basket";A2:C16;3;ЛОЖЬ)

Когда Вы нажмёте Enter, то увидите, что товар Gift basket находится в категории Gifts.

Если хотите попрактиковаться, проверьте, сможете ли Вы найти данные о товарах:

Цену coffee mug
Категорию landscape painting
Цену serving bowl
Категорию scarf

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

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru