Не найдено ни одной ячейки удовлетворяющей указанным условиям excel
Обновлено: 07.07.2024
Многие технические и экономические задачи приводят к необходимости решения уравнений и систем уравнений , приближенного вычисления функций , решения оптимизационных задач и т . д . Для решения таких задач успешно применяются вычислительные машины .
Существует специальное направление в математике - вычислительная математика , которая занимается разработкой методов реализации на компьютерах перечисленных задач .
Табличные формулы (array formula) называют также формулами массива . Табличные формулы — средство Excel, позволяющее в формулах обращаться с блоками , как с обычными ячейками . Эти формулы позволяют давать компактные решения сложных задач . В некоторых задачах вообще невозможно обойтись без табличных формул .
Ввод и редактирование табличных формул Задание. Создайте новую рабочую книгу . Присвойте первому листу
имя Табличные формулы Введите исходные данные .
Создание имен . Для наглядности дадим векторам имена .
Задание. Выделите диапазон со вторым и третьим столбцами таблицы ( В 1: С 5) и дайте команду меню " Вставка / Имя / Создать ". Диапазон В 2: В 5
получит имя " Приход ", а диапазон С 2: С 5 - имя " Расход ". Убедитесь , что теперь можно выделять эти блоки , выбирая из выпадающего списка в поле имени соответствующее имя .
Ввод табличной формулы с использованием имен диапазонов .
1. Выделим блок D2:D5. В этом блоке активна ячейка D2.
2. Наберем знак равенства =.
3 . Нажмем функциональную клавишу F3. Появится диалоговое окно " Вставка имени ". Выберем имя " Приход " и щелкнем " ОК ". Формула примет вид = Приход .
4. Наберем знак минус —.
5. Вновь нажмем клавишу F3. В диалоговом окне " Вставка имени " выберем имя " Расход " и щелкнем " ОК ". Формула примет вид = Приход - Расход .
6. Нажмем сочетание клавиш Ctrl+Shift+Enter . Во всех ячейках блока появится формула .
Прокомментируем шаги . На третьем и пятом шаге мы выбирали имя из списка имен . Можно было ввести имя непосредственно с клавиатуры , но предложенный метод проще и нет риска ошибиться в наборе имени . На шестом шаге мы нажимаем не Enter, как ранее при вводе формулы , a Ctrl+Shift+Enter ( при нажатии клавиши Enter должны быть нажаты обе клавиши Ctrl и Shift ). Это очень важно . Если бы мы нажали Enter, то формула была бы введена только в активную ячейку блока D2. Фигурные скобки , окружающие формулу , говорят о том , что это табличная формула . Эти скобки нельзя набирать вручную ( формула будет воспринята как текст )._
Ввод табличной формулы . Разумеется , табличную формулу можно вводить и без использования имен .
Задание. Скопируйте блок А 1 : С 5 в А 11 : С 15. Повторим все шаги . Выделим блок D12:015.
В этом блоке активной ячейкой является D12. Наберем знак равенства
=. Выделим блок В 12: В 15, наберем знак минус -, выделим блок С 12: С 15, нажмем сочетание клавиш Ctrl-Shift+Enter.
Во всех ячейках блока появится формула . Мы получили две идентичные таблицы .
Выделение блока с табличной формулой .
Есть и другой способ выделения .
Задание. Нажмите клавишу F5 ( эквивалент пункта меню " Правка / Перейти "). В диалоговом окне щелкните кнопку " Выделить ", установите переключатель " Текущий массив ".
Изменение табличной формулы . Попытаемся очистить одну из ячеек , занятую табличной формулой .
Отредактировать формулу можно так : выделить блок с формулой ( клавиши Ctrl+/), нажать функциональную клавишу F2, внести изменения в формулу , нажать сочетание клавиш Ctrl+Shift+Enter.
Задание . Ввести в блок формулу , потом отменить это .
Задание . Перейдите на новый рабочий лист и назовите его " Константы ".
В блоке ( вектор - столбце ) А 2: А 5 записать числа : 1,2,3,4. Требуется получить в блоке B2:D5 три вектор - столбца , каждый из которых представляет собой результат умножения исходного вектор - столбца на числа
( Этот пример мы можем решить уже известным нам методом : разместить числа 2, -3, 4 в диапазоне В 1:D1, записать в ячейку В 2 формулу =$ А 2* В $1 и скопировать ее в остальные ячейки диапазона B2:D5.)
Задание . Дадим более экономное решение . Выделим блок В 2:D5. Запишем в него табличную формулу >. Получится тот же результат , но мы сэкономили место на рабочем листе : теперь блок В 1:D1 пуст .
Табличный массив можно интерпретировать как вектор - строку , а блок А 2: А 5 как вектор - столбец . Получается , что мы перемножили вектор - столбец (4 х 1) на вектор - строку (1 х 3) и получили матрицу (4x3):
Но если мы введем формулу * А 2: А 5>, то получим гот же результат , хотя с позиций матричной алгебры вектор - строку (1x3) нельзя умножать на вектор - столбец (4x1) из - за несогласованности размеров ( число
столбцов в первом сомножителе должно равняться числу строк во втором сомножителе ).
Итак , массив чисел , разделенных точкой с запятой и заключенных в фигурные скобки , можно рассматривать как вектор - строку .
Если вместо точки с запятой использован другой разделитель — двоеточие , то массив можно интерпретировать как ветор - столбец .
Задание . В A10:D10 разместите числа : 1, 2, 3, 4. В блок F10:I12
поместите табличную формулу =*A10:D10. Сопоставьте с результатом предыдущего задания . Поменяйте местами сомножители и убедитесь , что результат не изменяется .
Функции, возвращающие блок Задание . Перейдите на новый рабочий лист и назовите его " Функции ".
В Excel имеются функции , возвращающие сразу несколько значений . Естественно , что формулы , которые содержат такие функции , надо вводить
как табличные и перед вводом этих формул выделять нужное количество ячеек .
Подсчет распределения чисел по диапазонам значений. Функция ЧАСТОТА ( исходный _ массив , массив _ интервалов ) подсчитывает , сколько значений из исходного _ массива попадает в диапазон между значениями , представленными в массиве _ интервалов . Более точно , если массив _ интервалов — массив < а : Ь : с >, то числа ИСХОДНОГО массива распределяются по промежуткам :
.
Обратите внимание , что промежутки полузамкнутые , т . е ., например ,
, а .
Количество промежутков на единицу больше количества элементов в массиве _ интервалов .
Задание . Введите в блок А 1: А 10 числа 1, 2, 2, 3, 4, 6, 5, 4, 4,3 — это исходный массив . В С 1: С 4 введите числа 2, 3, 4, 5 — это массив интервалов . В Е 1: Е 5 ( количество ячеек в результате на 1 больше , чем в массиве карманов ) введите табличную формулу )>. Тогда нет необходимости заполнять блок С 1 : С 4.
Задание . Введите числа
и подсчитайте количество двоек , троек , четверок и пятерок . ( Обратите внимание , что формулу с функцией ЧАСТОТА нужно ввести только в четыре ячейки .)
Матричные операции Задание . Перейдите на новый рабочий лист и назовите его " Матрицы ".
Простейшие операции , которые можно проделывать с матрицами : сложение ( вычитание ), умножение на число , перемножение , транспонирование , вычисление обратной матрицы .
Сложение матриц и умножение матрицы на число.
Задание . Сложить матрицы M и N, где
Ввести матрицы M и N в блоки А 1 : С 2 и Е 1 :G2. В блок А 4: С 5 введем табличную формулу .
Обратите внимание , что выделен блок , имеющий те же размеры , что и исходные матрицы .
Использование имен делает процедуру ввода табличной формулы намного проще .
Задание . Дайте диапазонам А 1: С 2 и E1:G2 имена М и N. В блок E4:G5 введите табличную формулу . Результат , естественно , тот же .
Задание . Теперь вычислим линейную комбинацию матриц 2 М - N. В блок А 7: С 8 введем табличную формулу . Должны получиться
Рассмотренные примеры и упражнения показывают , что обычная
операция умножения применительно к блокам не вполне эквивалентна перемножению матриц .
И действительно , для матричных операций в Excel предусмотрены функции , входящие в категорию " Математические ". Перечислим эти функции :
МОПРЕД — вычисление определителя матрицы ; МОБР — вычисление обратной матрицы ; МУМНОЖ — перемножение матриц ; ТРАНСП — транспонирование .
Первая из этих функций возвращает число , поэтому вводится как обычная формула .
1)с помощью чего можно очистить активный рабочий лист в Excel.
Т.е. что нужно написать для кнопки, чтобы лист очистился от цифр и графиков например.
2)И второй вопрос:
у меня есть на листе небольшой список учеников, для каждого вводятся оценки и по нажатию кнопки вычисляется среднее значение.
Как сделать так, чтобы программа автоматически видела сколько строк заполнена и вычисляла для всех, которые заполнены.
Мне дали вот это: UsedRange.Rows.Count. А как этим пользоваться подскажите пожалуйста, очень надо.
2)
Тут макросы не нужны. Введите формулы. Они будут давать результат прямо в процессе ввода данных. К моменту, когда ввод будет завершен, результат будет уже готов. Ячейки с формулами можно для удобства пользователя защитить от изменений, чтобы предотвратить порчу формулы.
3)
Вопросы в темах не смешивайте. Задавайте отдельно.
Naeel Maqsudov, удалить нужно было только цифры. А не Cells.ClearContents. Только цифры со всего листа - это уже сложнее! Верно?! Может, если это список учеников, то не весь лист очищать, а только ту его часть, где выставляются цифры (оценки)?
Можно, конечно, диапазону имя присвоить.
Кстати, Naeel Maqsudov, вопрос об "обработке" ячеек только какого-либо одного формата на листе (число, денежный, дата. ) уже поднимался где-то в формуме?! Не так ли?
Чтобы помнить все что тут поднималось, надо иметь семь пядей во лбу.
А у меня только шесть . С половиной
Если нужно только данные определенного типа (не формата, а типа), то можно так:
Очень нехороший цикл. (xlLastCell - не дает "забыть" давно очищенную и удаленную ячейку!)Странно! Cells.SpecialCells(xlCellTypeAllFormatConditions) согласно материалам, приведенным в справке, должен вернуть "Cells of any format", а мне эта штуковина возвращает: Run-time error '1004':
Не найдено ни одной ячейки, удовлетворяющей указанным условиям.
Что при этом имеется в виду под Cells of any format .
Нужна функция, которая может искать выполнения некоторого условия и возвращать значение либо имя первой подходящей ячейки. Возможно, она есть в Excel "из коробки", просто я о ней не знаю.
Для примера пусть будет РПГ, в которой есть таблица уровней и требуемого для их достижения с самого начала игры опыта, причём нет явной линейной/геометрической прогрессии, чтобы найти ответ без поиска по таблице (пример набран из головы):
Нужно узнать уровень персонажа, если известен его текущий опыт.
Если персонаж получил новый уровень только что (2000 опыта на картинке), то формула была бы =ВПР(B11;$A$2:$B$9;2;ЛОЖЬ)
однако персонаж после получения уровня успел набрать некоторое количество опыта и ВПР в данном случае не справляется.
Более приближённый к жизни пример: есть список ёмкостей для жидкостей и некоторое количество жидкости, которая должна быть вся налита в одну из ёмкостей. Как найти самую маленькую ёмкость, которой будет достаточно?
UPD:
Справился сам, но решение мне кажется кривоватым.
1. Если опыт отсортирован по возрастанию то формула выдаст, в какой сверху ячейке она нашла то, что нужно. Условие для поиска - "< < < https://habrastorage.org/web/f91/96a/ed0/f9196aed08464f0bb32e041031347a88.jpg" alt="f9196aed08464f0bb32e041031347a88.jpg"/>
В примере с ёмкостями при таком подходе нужно использовать "меньше" вместо "меньше или равно" и прибавлять 1.
- Вопрос задан более трёх лет назад
- 723 просмотра
1. СЧЁТЕСЛИ выдаст, в какой сверху ячейке условие было нарушено - надо написать обратное условие.
В данном случае вместо больше меньше: "< < < https://habrastorage.org/web/f91/96a/ed0/f9196aed08464f0bb32e041031347a88.jpg" alt="f9196aed08464f0bb32e041031347a88.jpg"/>
В примере с ёмкостями при таком подходе нужно использовать "меньше" вместо "меньше или равно" и прибавлять 1. Значения в столбце А обязательно должны быть отсортированы по возрастанию.
A1:F11 - рабочая таблица
Н2 - поисковая ячейка
H6=АДРЕС (СУММПРОИЗВ ((A1:F11=H2)*СТРОКА (A1:F11));СУММПРОИЗВ ((A1:F11=H2)*СТОЛБЕЦ (A1:F11))
А далее можно монстрячить всякое непотребство с ДВССЫЛ (в формате RC будет удобнее если в рассчётах участвуют и столбцы):
=СЧЁТЗ(ДВССЫЛ("A"&"2"):ДВССЫЛ("B"&"9"))
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Описание
В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание образца листа
В этой статье используется образец листа для иллюстрации встроенных функций Excel. Рассматривайте пример ссылки на имя из столбца A и возвращает возраст этого человека из столбца C. Чтобы создать этот лист, введите указанные ниже данные в пустой лист Excel.
Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
Определения терминов
В этой статье для описания встроенных функций Excel используются указанные ниже условия.
Определение
Вся таблица подстановки
Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».
Просматриваемый_массив
-или-
Лукуп_вектор
Диапазон ячеек, которые содержат возможные значения подстановки.
Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.
3 (третий столбец в инфо_таблица)
Ресулт_аррай
-или-
Ресулт_вектор
Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.
Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.
Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение "5" в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).
Функции
LOOKUP ()
Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.
Ниже приведен пример синтаксиса формулы подСТАНОВКи.
= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)
Следующая формула находит возраст Марии на листе "образец".
= ПРОСМОТР (E2; A2: A5; C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как "Мария" находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).
Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.
Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
Функция ВПР или вертикальный просмотр используется, если данные указаны в столбцах. Эта функция выполняет поиск значения в левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Функцию ВПР можно использовать для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Следующая формула находит возраст Марии на листе "образец".
= ВПР (E2; A2: C5; 3; ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется "3" в качестве Колумн_индекс (столбец C). Так как "Мария" находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).
Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
INDEX () и MATCH ()
Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.
Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:
= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)
Следующая формула находит возраст Марии на листе "образец".
= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как "Мария" находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
СМЕЩ () и MATCH ()
Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.
= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)
Эта формула находит возраст Марии на листе "образец".
= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как "Мария" находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
Читайте также: