Как увеличить значение в ячейке excel. выполнение выборки

Случайная выборка в Excel

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

Исходный набор данных:

Сначала вставим слева два пустых столбца. В ячейку А2 впишем формулу СЛЧИС (). Размножим ее на весь столбец:

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

Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»:

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

Функция СМЕЩ

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

Синтаксис: =СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; ; ), где

ссылка – обязательный аргумент.

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

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

Если в результате смещения диапазон стал выходить на пределы листа, то функция возвращает ошибку #ССЫЛКА!.

Для примера будем использовать вложение функции СМЕЩ в функцию СУММ.Изначально ссылаемся на диапазон из 10 строк и 1 столбца, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.

Теперь сместим диапазон на один столбец влево, т.е. на -1.

Снова изменим ссылку, а именно расширим до 4 столбцов. После этого возвращаемая ссылка станет A3:D12. Результат на изображении.

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:

INDEX(array,row_num,)ИНДЕКС(массив;номер_строки;)

Каждый аргумент имеет очень простое объяснение:

array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

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

Вот простейший пример функции INDEX (ИНДЕКС):

=INDEX(A1:C10,2,3)=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2.

Очень просто, правда? Однако, на практике Вы далеко не всегда знаете, какие строка и столбец Вам нужны, и поэтому требуется помощь функции ПОИСКПОЗ.

ПОИСКПОЗ – синтаксис и применение функции

Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку «London» – это третий элемент в списке.

=MATCH(“London”,B1:B3,0)=ПОИСКПОЗ(“London”;B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

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

lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ, хотите ли Вы найти точное или приблизительное совпадение:
- 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
- – находит первое значение, равное искомому. Для комбинации ИНДЕКС/ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен .
- -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

На первый взгляд, польза от функции ПОИСКПОЗ вызывает сомнение. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Как перевернуть текст в excel

= Мир MS Excel/Статьи об Excel

Приёмы работы с книгами, листами, диапазонами, ячейками

Приёмы работы с формулами

Настройки Excel

Инструменты Excel

Интеграция Excel с другими приложениями

Форматирование

Выпадающие списки

Примечания

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

Гиперссылки

Excel и интернет

Excel для Windows и Excel для Mac OS

Инструмент камера возвращает динамический снимок указанного диапазона. Динамический — это значит он изменяется при изменении диапазона, который мы сфотографировали. По умолчанию камера недоступна во всех версиях Excel. Для того что бы ей воспользоваться необходимо сначала вывести её на панель меню (в Excel версий до 2007) или на панель быстрого доступа (в Excel версий после 2003). Как это делается?

Для Excel версий до 2007:

Меню Сервис — пункт Настройка — вкладка Команды — категории Сервис — команды Камера — левой кнопкой мыши перетаскиваем иконку камеры на удобное место в меню.

Для Excel версий после 2003:

Кликаем правой кнопкой мыши по панели быстрого доступа — Настройка панели быстрого доступа — Выбрать команды из Все команды — Камера — Добавить — ОК.

Теперь, когда инструмент камера добавлен в меню/на панель быстрого доступа можно приступить к решению вопроса, озвученного в названии этой статьи: Как перевернуть текст в ячейке?Как я уже писал выше — это невозможно. Формулы и функции не работают с форматированием, ориентацию текста в диалоговом окне Формат ячеек можно изменять только в пределах от 90 до -90 градусов, в VBA нет функций, позволяющих это сделать. Остаётся только перевернуть монитор

Выделяем ячейку, которую надо скопировать. Левой кнопкой мыши кликаем на иконку инструмента камера. Окантовка выделенной ячейки станет активной (как при обычном копировании). Левой кнопкой мыши указываем место на листе, куда необходимо вставить снимок и он автоматически вставляется. Всё, мы получили динамический снимок ячейки. Остаётся только перевернуть его на 180 градусов и готово. На рисунке, в ячейке В2, Вы видите снимок ячейки А1:

Все изменения производимые в ячейке А1 будут отображаться в В2.

ПРИМЕЧАНИЕ: Можно так же «фотографировать» и один диапазон. Одновременно несколько ячеек/диапазонов или несмежные диапазоны инструмент камера не обрабатывает, о чём появится соответствующее сообщение, если Вы попытаетесь применить камеру к ним. С получившимся снимком можно работать как с обычным рисунком, внедрённым на лист, то есть, изменять его размеры, свойства, форму, устанавливать прозрачность, рамки и прочее.

Поиск значений в списке данных

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

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

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

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

6 Надстройка Inquire

Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации.

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

Процесс использования надстройки включает такие действия:

Перейти к параметрам электронной таблицы.
Выбрать сначала надстройки, а затем управление надстройками COM.
Отметить пункт Inquire и нажать «ОК».
Перейти к вкладке Inquire.
Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare.
В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах.

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

5 программ для совместной работы с документами

10. Отбор значений с учетом повторов

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

Наиболее популярные статьи из этого раздела:

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

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

Решением является формула массива:

Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.

Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование, которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

Объединяем несколько условий.

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

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

Пример 1

Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из нескольких перечисленных в ней условий истинно.

= SE (OR (C2 = «Восток», C2 = «Юг»), «Экспорт», «Местный»)

Мы вставляем функцию ИЛИ как условие в функцию ЕСЛИ. В нашем случае, если регион покупателя — Восток или Юг, отгрузка считается экспортной.

Пример 2.

Давайте использовать несколько более сложных условий в функции ЕСЛИ.

Если регион продаж — Запад или Юг и количество больше 100, предоставляется скидка 10%.

= SE (И (ИЛИ (C2 = Запад, C2 = Юг), E2> 100), F2 * 0,1,0)

Функция И возвращает ИСТИНА, если все перечисленные в ней условия выполнены. Внутри функции И мы устанавливаем два условия:

Регион — Запад или Юг
Число больше 100.

Первый из них реализован так же, как и первый пример: ИЛИ (C2 = «Запад», C2 = «Юг»)

Во-вторых, здесь все очень просто: E2> 100

В строках 2, 3 и 5 выполняются оба условия. Эти покупатели получат скидку.

В строке 4 ни один из них не был выполнен. А в строке 6,7,8 выполняется только первая, но ее слишком мало. Следовательно, скидка будет нулевая.

Пример 3.

Конечно, эти несколько условий могут быть более сложными. Ведь логические функции можно «вкладывать» друг в друга.

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

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

Регион — Запад или Юг и число больше 100 (рассмотрено в примере 2)
В названии шоколада присутствует слово «черный».

В результате мы получаем формулу ЕСЛИ с несколькими условиями:

= ЕСЛИ (И (ЕЧИСЛО (НАЙТИ («Черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

Функция НАЙТИ ищет точное совпадение. Если для нас не важен регистр символов в тексте, то вместо НАЙТИ можно использовать аналогичную функцию ТОЧНЫЙ.

= ЕСЛИ (И (ЕЧИСЛО (МАТЧ («черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

В результате количество вложенных условий в Excel может быть очень большим

Важно лишь строго соблюдать логическую последовательность их выполнения

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

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

С одним условием

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

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

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

Нажать «Enter» – и появится результат поиска.

Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».

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

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

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

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

Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).

Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Поиск по нескольким столбцам

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

Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «{}».

Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «{}».

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

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)).

Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.

Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

В таблице с зарплатой за март добавить еще один столбец.

Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.

При помощи маркера заполнения протянуть полученное значение вниз.

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

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

Поставить курсор в ячейку, где он будет располагаться.
Перейти в раздел книги «Данные» – «Проверка данных».

В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).

Нажать «Ок». Отобразится список.

В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)

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

Обычное суммирование.

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

Ее синтаксис несложен.

=СУММ(параметр1; параметр 2…)

При работе с функцией СУММ учитывайте следующие тонкости.

Параметрами могут выступать как отдельные числа и иные значения, так и отдельные ячейки, и диапазоны с ними.
В качестве ЗНАЧЕНИЙ параметров могут быть любые данные – числа, текст, даты, – но суммировать Excel будет только числа.
Если хотя бы одно значение в параметрах содержит ошибку, то и результат всей функции СУММ выдаст ошибку.
Всего можно указать до 255 параметров. Этим сама функция СУММ выгодно отличается от ее разновидности АВТОСУММА, часто применяемой новичками. АВТОСУММА находит только сумму значений в ячейках, расположенных рядом. При этом, если в диапазоне есть пустые ячейки или ячейки с текстом, то АВТОСУММА уже не складывает значения, находящиеся за ними.

Рисунок 1

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

Рисунок 2

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

Рисунок 3

Суммирование по нескольким условиям. Функция СУММЕСЛИМН.

Ситуация усложняется, если при суммировании нам необходим учесть не один, а несколько критериев. В таком случае можно применить разные способы. Сегодня мы рассмотрим применение функции СУММЕСЛИМН. Она позволяет выполнить выборочное суммирование в зависимости от нескольких заданных условий. Всего таких условий можно указать до 127 вариантов. Как минимум надо задать 2 условия. Вы можете использовать и только одно условие, что не запрещено, однако в этом случае проще применить СУММЕСЛИ.

Синтаксис данной функции выглядит так

=СУММЕСЛИМН( диапазон суммирования; диапазон проверки на соответствие первому критерию ( то есть первому условию); первый критерий ( условие, которому должна соответствовать ячейка в диапазоне проверки первого критерия); диапазон проверки на соответствие второму критерию ( второму условию); второй критерий ( второе условие)… и так до 127 диапазонов проверки критериев и самих критериев).

Приведем пример работы СУММЕСЛИМН. Предположим следующее. Наименования товаров заданы в диапазоне B3:B50, количество упаковок каждого товара находится в графе С3:С50, а в диапазоне D3:D50 указаны соответствующие заданным позициям товара расценки. Наша задача – найти общее количество упаковок рубашек с ценой ниже 3000. Получаем следующую задачу:

«Найти общую сумму в диапазоне С3:С50, но при этом в диапазоне B3:B50 должно содержаться слово «рубашка», а в диапазоне D3:D50 значение должно быть меньше 3000». Итоговая формула будет выглядеть следующим образом.

=СУММЕСЛИМН(C3:C50;B3:B50;”рубашка”;D3:D50;”<3000″)

Рисунок 12

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

При работе с функцией СУММЕСЛИМН надо учитывать все правила и тонкости, которые применялись для функции СУММЕСЛИ. Кроме этого, существуют и особенности непосредственно функции СУММЕСЛИМН:

В отличии от функции СУММЕСЛИ равенство диапазонов суммирования и поиска соответствия условию является не рекомендательным, а обязательным. Если хотя бы один из указанных диапазонов не совпадет по размеру с остальными, получите не просто неверные данные, а значение ошибки #ЗНАЧ!
В отличии от функции СУММЕСЛИ, где третий параметр не обязателен и иногда допускается его пропуск, для функции СУММЕСЛИМН указание каждого параметра обязательно. Нельзя задать диапазон проверки условия и при этом не указать само условие или наоборот. Тем более вы не можете не указать диапазон суммирования, так как таким диапазоном автоматически будет считаться самый первый диапазон, указанный при введении данной функции. Заданные для проверки условия проверяются по содержимому разных диапазонов. Нельзя проверить на соответствие разным критериям ячеек одного и того же диапазона. Вы можете найти общую сумму затрат по выбранному филиалу, группе затрат и периоду, так как наименование филиала, группа затрат и период будут указаны в разных графах. Если же вы попытаетесь с помощью функции СУММЕСЛИМН найти общую сумм затрат по нескольким филиалам из общего списка, то у вас ничего не получится, так как наименования филиалов расположены в одной колонке таблицы.