Примеры использования функции найти в таблицах excel. примеры excel

Как заполнить диапазон

Для заполнения диапазона определенными значениями необходимо предпринимать такие действия:

1. Ввести нужное значение в ячейку B2. Оно может быть как числовым, так и текстовым. Также возможно введение формулы. В нашем случае это цифра 2.

2. Далее нажимаем маркер автозаполнения (как раз тот самый квадратик, который мы ранее просили не нажимать) и перетаскиваем его вниз до конца диапазона.

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

6

Автозаполнение – одна из самых востребованных функций Excel. Она позволяет осуществлять запись в ячейки диапазона не только одного значения, но и целого набора данных, которые соответствуют определенной закономерности. Например, числовой ряд 2, 4, 6, 8, 10 и так далее.

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

78

Аналогично можно заполнять диапазон нужными датами, которые также подчиняются определенной закономерности. Для этого, давайте, введем дату 13 июня 2013 года и дату 16 июня 2013 года в американском формате.

9

После этого осуществляем уже знакомое нами перетаскивание.

10

Встроенные функции программы

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

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

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

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

Выбор функции из предлагаемого списка

Эту же операцию можно произвести с помощью комбинации на клавиатуре — Shift+F3 (раньше мы писали о горячих клавишах Excel).

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

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

Мастер функций

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

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

Например, дана задача, найти функцию СУММЕСЛИМН. Для этого нужно зайти в категорию математических функций и там найти нужную.

Выбор функции и заполнение полей

Далее нужно заполнить поля чисел и выбрать условие. Таким же способом можно найти самые различные функции, в том числе «СУММЕСЛИ», «СЧЕТЕСЛИ».

Вам это может быть интересно:

Особенности использования функции ПОИСКПОЗ в Excel

Функция имеет следующую синтаксическую запись:

=ПОИСКПОЗ( искомое_значение;просматриваемый_массив; )

• искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
• просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
• – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:

1. -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
2. 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
3. 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.

1. Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
2. Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
3. Если аргумент явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
4. Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.

Функция ГПР в Excel

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

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

Для «чайников» поясняем — функция ГПР в Excel работает со строчками (буковка «Г» значит «горизонтальный») таблиц, другими словами её необходимо применять в тех вариантах, когда в 2-ух либо наиболее таблицах есть строчки с схожим содержимым. Порядок столбцов в объединяемых таблицах значения не имеет.

Ниже кратко рассмотрена работа функции ГПР, её синтаксис (для ручного написания, также осознания аргументов функции) и рассмотрен обычный пример внедрения ГПР на практике.

Дальше для ясности функция ГПР рассматривается на ординарном примере с 2-мя таблицами, которые представлены на снимке экрана ниже. Красноватым шрифтом выделены результаты работы функции ГПР, в итоге которой значения заработной платы попадают из 2-ой (нижней) таблицы в первую (сверху).

Режимы предварительного просмотра

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

1. Для отображения полей документа кликаем по кнопке «Показать поля», которая находится слева.
2. Размер полей, разумеется, можно редактировать. Дя этого наводим указатель мыши на линию границы нужного поля и как только курсор поменяет вид на характерный крестик, передвигаем линию, зажав левую кнопку мыши.
3. Для того чтобы скрыть показ полей, еще раз щелкаем по кнопке “Показать поля”.
4. С правой стороны есть еще одна кнопка – “По размеру страницы”. Если кликнуть по ней, документ будет отображен точно в таком же размере, каким он будет на листе бумаги.
5. Для выхода из режима “По размеру страницы”, соответственно, щелкаем еще раз по этой же кнопке.

Как сделать ВПР в Excel понятная инструкция

Чтобы понять, как пользоваться функцией ВПР в Excel, разберем ее синтаксис:

=ВПР(искомое значение; таблица; номер столбца; )

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

Таблица — ссылка на таблицу, в которой будет искать.

Номер столбца — порядковый номер столбца относительно самого левого столбца из выделенного диапазона поиска.Интервальный просмотр — указывается 0 (точный поиск) или 1 (приблизительный поиск)

Рассмотрим использование ВПР на примере:

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

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

Добавим в первую таблицу столбец Температура и напишем следующую формулу:

В примере формула ВПР ищет значение Январь из первой таблице в крайнем левом столбце второй таблицы. И подтягивает совпадающее значение из указанного столбца (в данном случае, из второго столбца).

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

Запомним, что в абсолютном большинстве случаем Интервальный просмотр ставим = 0.

Кстати, если не указать интервальный просмотр совсем, то формула не выдаст ошибку, т.к. этот аргумент необязательный. Но результат будет неожиданным, поскольку в этом случае ВПР считает не указанный Интервальный просмотр равным 1 (а это приблизительный поиск).

Не забываем добавить абсолютные ссылки (значки доллара $) к диапазону, иначе диапазон “съедет” при копировании формулы.

Итак, мы получили в нашу таблицу значение из таблицы-справочника — подтянули по критерию названия месяца. Осталось только протянуть или скопировать формулу во все ячейки этого столбца.

Функция ИНДЕКС Google Таблиц

В то время как ПОИСКПОЗ показывает, где искать ваше значение (его положение в диапазоне), функция ИНДЕКС Google Таблиц извлекает само значение на основе его смещения строки и столбца:

=ИНДЕКС(ссылка, , )

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

Если вы укажете оба необязательных аргумента (строку и столбец), Google Sheets INDEX вернет запись из целевой ячейки:

=ИНДЕКС(A1:C10, 7, 1)

Пропустите один из этих аргументов, и функция получит всю строку или столбец соответственно:

=ИНДЕКС(A1:C10, 7)

Предпросмотр в более ранних версиях Эксель

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

Алгоритм действий в Эксель 2007:

1. В открытом документе щелкаем по иконке Microsoft Office (в верхнем углу окна программы слева).
2. В появившемся перечне команд выбираем курсором “Печать”.
3. В результате откроется список дополнительных команд, где нажимаем на “Предварительный просмотр”.
4. В результате проделанных действий откроется окно с предварительным просмотром документа.

План действий в Эксель 2003:

1. В открытом документе щелкаем по меню “Файл”.
2. В появившемся списке команд кликаем по надписи “Предварительный просмотр”.
3. Откроется окно предпросмотра документа.

Определения терминов

В этой статье для описания встроенных функций Excel используются указанные ниже условия.

Термин	Определение	Пример
Массив таблиц	Вся таблица подстановки	A2: C5
Превышающ	Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».	E2
Просматриваемый_массив -или- Лукуп_вектор	Диапазон ячеек, которые содержат возможные значения подстановки.	A2: A5
Номер_столбца	Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.	3 (третий столбец в инфо_таблица)
Ресулт_аррай -или- Ресулт_вектор	Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.	C2: C5
Интервальный_просмотр	Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.	ЛОЖЬ
Топ_целл	Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.
Оффсет_кол	Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение “5” в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Как сравнить два столбца в Excel по строкам

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

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

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

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

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

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

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

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

Пролистывание страничек в режиме предпросмотра

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

Пролистывать странички можно различными методами:

1. Кликаем по области предпросмотра и потом крутим колесо мыши вверх-вниз
2. Используем для пролистывания клавиши вправо-влево, расположенные сходу под областью подготовительного просмотра, либо пишем в поле с текущим номером странички (меж этими клавишами) желаемую цифру, опосля чего же нажимаем Enter.
3. Пользуемся доп инструментами скролинга снутри области предпросмотра:
   • зажав левой клавишей мыши тянем ползунок вверх-вниз;
   • либо кликаем по стрелкам, размещенным на концах полосы прокрутки.
4. Жмем клавиши на клавиатуре:
   • End – перемещение в конец документа
   • Home – перемещение в начало документа
   • Стрелка ввысь – пролистывание вспять
   • Стрелка вниз – пролистывание вперед

IT IS MY LIFE…

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

ВПР и приблизительный интервальный просмотр

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

Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.

Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»)

Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Особенности работы функции ВПР

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

Функция ВПР ищет совпадение строго в крайнем левом столбце выделенной таблицы.

Ключевые слова здесь — “выделенной” таблицы. Чтобы понять, что это значит, добавим к таблице-справочнику еще один столбец слева (Год).

Чтобы сделать такой же ВПР, как в предыдущем примере (по критерию Месяц), нужно выделять столбцы H:I, хотя таблица содержит столбцы G:I. Иначе Excel будет искать в крайнем левом столбце, в котором содержится не месяц, а год.

Вывод: аргумент Таблица в данном случае — это выделенный диапазон ячеек, а не то, что мы видим “глазами” и считаем таблицей.

ВПР в Excel ищет первое совпадение с указанным критерием.

Для примера добавим еще одну строку в таблицу-справочник.

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

Но при этом, даже если мы изменим интервал таблицы для поиска, затянув в нее новую строку Январь 2023, то значение, которое подтянула ВПР, не изменится.

ВПР взяло первое совпадение.

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

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

Пример функции ВПР с текстовым критерием поиска

Пример функции ВПР с числовым критерием поиска

Пример 2

Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.

На рисунке ниже Вы можете увидеть один из таких примеров:

Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter, мы получим требуемый результат:

При использовании функции ПРОСМОТР в Excel значения в просматриваемом векторе должны быть отсортированы в порядке возрастания, иначе она может вернуть неверный результат.

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

Предпросмотр в наиболее ранешних версиях Эксель

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

Метод действий в Эксель 2007:

1. В открытом документе щелкаем по иконке Microsoft Office (в верхнем углу окна программки слева).
2. В показавшемся списке установок избираем курсором “Печать”.
3. В итоге раскроется перечень доп установок, где жмем на “Подготовительный просмотр”.
4. В итоге проделанных действий раскроется окно с подготовительным просмотром документа.

План действий в Эксель 2003:

1. В открытом документе щелкаем по меню “Файл”.
2. В показавшемся перечне установок кликаем по надписи “Подготовительный просмотр”.
3. Раскроется окно предпросмотра документа.

Пример 2

Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.

На рисунке ниже Вы можете увидеть один из таких примеров:

Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter, мы получим требуемый результат:

При использовании функции ПРОСМОТР в Excel значения в просматриваемом векторе должны быть отсортированы в порядке возрастания, иначе она может вернуть неверный результат.

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

Оцените качество статьи

Нам важно ваше мнение:

Пример 2

Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.

На рисунке ниже Вы можете увидеть один из таких примеров:

Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter, мы получим требуемый результат:

При использовании функции ПРОСМОТР в Excel значения в просматриваемом векторе должны быть отсортированы в порядке возрастания, иначе она может вернуть неверный результат.

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

Как работать в программе Excel: пособие для начинающих

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

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

Давайте разберёмся, как работать с элементами таблицы – стройками, столбцами и отдельными ячейками.

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

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

Соответственно, для выделения строки нужно щелкнуть мышкой по цифре, соотносящейся с нужной строкой.

Для выделения диапазона строк или столбцов действуем следующим образом:

• щёлкаем правой кнопкой по первому индексу (буква или цифра), при этом строка/столбец выделится;
• отпускаем кнопку и ставим курсор на второй индекс;
• при нажатой Shift щёлкаем ПКМ на второй цифре/букве – выделенной станет соответствующая прямоугольная область.

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

Изменение границ ячеек

Пользователи, пробующие самостоятельно научиться пользоваться программой Excel, часто сталкиваются с ситуацией, когда в ячейку вносится содержимое, превышающее её размеры. Это особенно неудобно, если там содержится длинный текст. Расширить правую границу ячейки можно двумя способами:

• вручную, кликнув левой кнопкой мыши по правой границе на строке с индексами и, удерживая её нажатой, передвинуть границу на нужное расстояние;
• есть и более простой способ: дважды щёлкнуть мышью по границе, и программа самостоятельно расширит длину ячейки (опять же на строке с буквами-индексами).

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

С помощью кнопки «Распределение по вертикали» можно отобразить не помещающийся в ячейку текст в несколько строк.

Отмена внесенных изменений производится стандартным способом, с помощью кнопки «Отмена» или комбинации CTRL+Z. Желательно такие откаты делать сразу – потом это может и не сработать.

Если требуется отметить форматирование строк, можно воспользоваться вкладкой «Формат», в которой выбрать пункт «Автоподбор высоты строки».

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

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

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

Это же окно можно вызвать комбинацией CTRL+SHIFT+«=».

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

Как скопировать выпадающий список в Excel

В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6.

Для того чтобы скопировать выпадающий список с текущим форматированием:

• нажмите левой клавишей мыши на ячейку с выпадающим списком, которую вы хотите скопировать;
• нажмите сочетание клавиш на клавиатуре CTRL+C;
• выделите ячейки в диапазоне А2:А6, в которые вы хотите вставить выпадающий список;
• нажмите сочетание клавиш на клавиатуре CTRL+V.

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

• нажмите левой клавишей мыши на ячейку с выпадающим списком, который вы хотите скопировать;
• нажмите сочетание клавиш на клавиатуре CTRL+C;
• выберите ячейку, в которую вы хотите вставить выпадающий список;
• нажмите правую кнопку мыши => вызовите выпадающее меню и нажмите “Специальная вставка“;

В появившемся окне в разделе “Вставить” выберите пункт “условия на значения“:

Нажмите “ОК“

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

Как же конкретно работает формула ВПР

• Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
• Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром , в противном случае возвращается код ошибки #Н/Д (#N/A).

Использование ВПР в программе Excel

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

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

Первая – это список предметов, единицы их измерения и количество.

Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.

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

– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.

– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).

Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.

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

Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».

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

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

Функции просмотра и ссылки

ПОИСКПОЗ

Как отфильтровать данные в таблице Excel

Для фильтрации информации в таблице нажмите справа от заголовка колонки «стрелочку», после чего появится всплывающее окно:

• «Текстовый фильтр» отображается когда среди данных колонки есть текстовые значения;
• «Фильтр по цвету» так же как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
• «Числовой фильтр» позволяет отобрать данные по параметрам: «Равно…», «Не равно…», «Больше…», «Больше или равно…», «Меньше…», «Меньше или равно…», «Между…», «Первые 10…», «Выше среднего», «Ниже среднего», а также настроить собственный фильтр.
• Во всплывающем окне, под «Поиском» отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

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

Использование функции ПРОСМОТР

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

Примечание: данные, по которым будет выполняться поиск, должны быть расположены строго в порядке возрастания, иначе функция ПРОСМОТР корректно работать не будет, то есть:

• Числа: … -2, -1, 0, 1, 2…
• Буквы: от А до Я, от A до Z и т.д.
• Логические выражения: ЛОЖЬ, ИСТИНА.

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

Есть два способа применения функции ПРОСМОТР: векторная форма и форма массива. Разберем подробнее каждый из них.

Метод 1: векторная форма

Пользователи Excel чаще всего используют именно этот способ. Вот, в чем он заключается:

1. Рядом с исходной таблицей создаем еще одну, шапка которой содержит столбцы с названиями “Искомое значение” и “Результат”. На самом деле, это не является обязательным условием, однако, так проще работать с функцией. Названия заголовков, также, могут быть другими.
2. Встаем в ячейку, в которой планируем выводить результат, после чего щелкаем по значку “Вставить функцию” слева от строки формул.
3. Перед нами появится окно Мастера функций. Здесь мы выбираем категорию “Полный алфавитный перечень”, пролистываем список вниз, находим оператор “ПРОСМОТР”, отмечаем его и жмем OK.
4. На экране отобразится небольшое окошко, в котором нам нужно выбрать один из двух списков аргументов. В данном случае останавливаемся на первом варианте, т.к. разбираем векторную форму.
5. Теперь нам нужно заполнить аргументы функции, после чего нажать кнопку OK:
   • “Искомое_значение” – здесь мы указываем координаты ячейки (прописываем вручную или просто щелкаем по нужному элементу в самой таблице), в которую будем вводить параметр, по которому будет выполняться поиск. В нашем случае – это “F2”.
   • “Просматриваемый_вектор” – указываем диапазон ячеек, среди которых будет производиться поиск искомого значения (у нас это “A2:A8”). Здесь мы, также, можем прописать координаты вручную, либо выделить требуемую область ячеек в таблице с помощью зажатой левой кнопки мыши.
   • “Вектор_результатов” – здесь мы указываем диапазон, из которого следует выбрать результат, соответствующий искомому значению (будет находиться в той же строке). В нашем случае пусть будет “Количество, шт.”, т.е. диапазон “C2:C8”.
6. В ячейке с формулой мы видим результат “#Н/Д”, что может восприниматься как ошибка, но это не совсем так.
7. Чтобы функция заработала, нам нужно ввести в ячейку “F2” какое-нибудь наименование (например, “Раковина”), содержащееся в исходной таблице, регистр при этом не важен. После того, как мы нажмем Enter, функция автоматически подтянет нужный результат (у нас это будет 19 шт).Примечание: опытные пользователи могут обходиться без Мастера функций и сразу же вводить формулу функции в соответствующей строке со ссылками на требуемые ячейки и диапазоны.

Метод 2: форма массива

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

1. Вставляем в ячейку для вывода результата функцию ПРОСМОТР – как и в первом методе, но теперь выбираем список аргументов для массива.
2. Указываем аргументы функции и жмем кнопку OK:
   • “Искомое_значение” – заполняется так же, как для векторной формы.
   • “Массив” – задаем координаты всего массива (или выделяем его в самой таблице), включающего просматриваемый диапазон и область результатов.
3. Чтобы воспользоваться функцией, как и в первом методе вводим наименование товара и жмем Enter, после чего результат автоматически появится в ячейке с формулой.

Примечание: форма массива для функции ПРОСМОТР используется редко, т.к. является устаревшей и остается в современных версиях Эксель для поддержания совместимости с книгами, созданными в более ранних версиях программы. Вместо нее желательно использовать современные функции: ВПР и ГПР.