|
Примечание : Наличие фигурных скобок {...} означает, что речь идёт о
формулах массива
[1]
[2] [3]
- Как с помощью формул преобразовать "число", где в качестве
разделителя используется точка, в число с запятой ? 09.09.2007
- Как получить первый и последний день недели
(понедельник, воскресенье), исходя из указанной даты ? 12.09.2007
- Как получить дату отстоящую на заданное число месяцев от
начальной даты ? 26.03.2011
- Как с помощью формул получить наименьшую и наибольшую дату ?
12.09.2007
- Как определить наиболее часто встречающийся в ячейке символ ?
24.06.2008
- Как перемножить значения ячеек, если ячейки могут
содержать текст ? 06.09.2009
- Как ссылаться на ячейку, чтобы ссылка не менялась
ни при каких условиях ? 23.01.2010
- Как создать ссылку на ячейку с использованием переменной
(имя книги, листа, номер строки и т.п.) ? 25.03.2011
- Как с помощью формул вывести "число" с заданным числом
десятичных знаков ? 25.03.2011
- Как получить абсолютное значение числа ? 03.04.2011
- Как получить остаток от деления ? 25.03.2012
- Kак перевести Цельсий в Фаренгейт, и наоборот,
Фаренгейт в Цельсий ? NEW 21.02.2016
[1]
[2] [3]
Ответ :
Для того, чтобы с помощью формул преобразовать "число", где в качестве
разделителя целой и дробной части используется . (точка) в число, где
разделителем будет выступать , (запятая) достаточно использовать любую
из нижеприведённых формул.
=ЗНАЧЕН(ПОДСТАВИТЬ(A1;".";","))
=ПОДСТАВИТЬ("123.456";".";",")*1
=ПОДСТАВИТЬ("123.456";".";",")/1
=ПОДСТАВИТЬ("159.217";".";",")+0
=ПОДСТАВИТЬ("159.217";".";",")-0
Комментарий : Этот совет может быть актуален только для тех,
у кого в качестве разделителя целой и дробной части используется запятая ,
Ответ :
Для того, чтобы исходя из текущей даты (или любой другой), получить первый
и последний день недели, т.е. понедельник и воскресенье, соответственно, достаточно
воспользоваться следующими формулами :
=A1-ДЕНЬНЕД(A1;2)+1
=СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();2)+1
=СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();3)
=A1+7-ДЕНЬНЕД(A1;2)
=СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ();2)
Предполагается, что : ячейка A1 содержит дату.
Ответ :
Для того, чтобы получить дату, отстоящую вперёд или назад, на заданное
число месяцев от начальной даты, можно использовать любой из двух вариантов :
Вариант I.
=ДАТАМЕС(A1;C1)
Примечание : Если функция =ДАТАМЕС() возвращает значение ошибки
#ИМЯ? или #ЗНАЧ!, то Вам необходимо установить надстройку Пакет Анализа
[См. примечание]
Вариант II.
=ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1;МИН(ДЕНЬ(A1);ДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1+1;1)-1)))
=ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1;МИН(ДЕНЬ(A1);ДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1+1;0))))
=МИН(ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1;ДЕНЬ(A1));ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1+1;1)-1)
Предполагается, что : ячейка A1 содержит дату, а в ячейке
C1 находится необходимое количество месяцев, которое может быть как
положительным, так и отрицательным числом.
Ответ :
Если в указанном диапазоне нет значений ошибок, и даты не хранятся
в виде текста, то для того, чтобы получить наименьшую и наибольшую дату,
достаточно воспользоваться следующими формулами :
=МИН(A2:A100)
=НАИМЕНЬШИЙ(A2:A100;1)
=МАКС(A2:A100)
=НАИБОЛЬШИЙ(A2:A100;1)
Примечание : При необходимости, ячейку содержащую формулу, можно
отформатировать, т.е. установить нужный формат [FAQ],
например ДД.ММ.ГГГГ
Ответ :
Для того, чтобы определить наиболее часто встречающийся символ,
достаточно воспользоваться любой из нижеприведённых формул.
=СИМВОЛ(МОДА(КОДСИМВ(ПСТР(C3;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(C3)));1))))
=СИМВОЛ(МОДА(КОДСИМВ(ПСТР(C3;СТРОКА(C1:ИНДЕКС(C:C;ДЛСТР(C3)));1))))
=СИМВОЛ(МОДА(КОДСИМВ(ПСТР(C3;СТРОКА(ИНДЕКС(C:C;1):ИНДЕКС(C:C;ДЛСТР(C3)));1))))
=ПСТР(C3;МОДА(НАЙТИ(ПСТР(C3;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(C3)));1);C3));1)
=ПСТР(C3;МОДА(НАЙТИ(ПСТР(C3;СТРОКА(C1:ИНДЕКС(C:C;ДЛСТР(C3)));1);C3));1)
=ПСТР(C3;МОДА(НАЙТИ(ПСТР(C3;СТРОКА(ИНДЕКС(C:C;1):ИНДЕКС(C:C;ДЛСТР(C3)));1);C3));1)
Предполагается, что : в ячейке C3 находятся нужные данные,
содержащие повторяющиеся символ(ы).
Примечание : Обратите внимание на то, что добавление/удаление строк
может привести к тому, что вторая формула возвратит результат, отличный от
ожидаемого. В этом отношении первая формула является более универсальной,
однако, наличие стандартной функции рабочего листа ДВССЫЛ() делает эту формулу
пересчитываемой(volatile), а значит первая формула будет пересчитываться
при изменении данных в любом рабочем листе любой открытой рабочей книги
(Сервис-Параметры-Вычисления-Автоматически ...)
Совет : Если необходимо игнорировать регистр, т.е. буквы
A и a должны считаться одним символом, то либо замените во
втором варианте функцию НАЙТИ() на ПОИСК(), либо преобразуйте исходный
текст к верхнему/нижнему регистру используя функции ПРОПИСН()/СТРОЧН()
Ответ :
Для того, чтобы перемножить значения нескольких ячеек,
пусть для примера, это будут ячейки столбца C (Цена) и D (Кол-во),
можно воспользоваться следующими формулами :
=C2*D2
=ПРОИЗВЕД(C2;D2)
Однако, если любая из ячеек будет содержать текст (а такое
встречается, когда фирма хочет подчеркнуть отсутствие товара и пишет,
к примеру, "-" или "нет" или "временно отсутствует"), то в результате
первая формула возвратит значение ошибки #ЗНАЧ!, а вторая одно из
перемножаемых чисел (или 0, если текст это и цена и кол-во)
И если Вы столкнулись именно с такой таблицей, то в таком случае
можно просто немного изменить формулы, т.е. воспользоваться первым
вариантом, если Вы уверены, что текст может содержать только
столбец D, или вторым вариантом, если такой уверенности нет.
Вариант I.
=C2*Ч(D2)
=ЕСЛИ(ЕЧИСЛО(D2);C2*D2;0)
=C2*ЕСЛИ(ЕЧИСЛО(D2);D2;0)
=ЕСЛИ(ТИП(D2)=1;C2*D2;0)
=C2*ЕСЛИ(ТИП(D2)=1;D2;0)
=ЕСЛИ(СЧЁТ(D2)=1;C2*D2;0)
Вариант II.
=Ч(C2)*Ч(D2)
=ЕСЛИ(СЧЁТ(C2;D2)=2;C2*D2;0)
=ЕСЛИ(ЕЧИСЛО(C2*D2);C2*D2;0)
=ЕСЛИ(ТИП(C2*D2)=1;C2*D2;0)
Ответ :
Для того, чтобы создать ссылку на ячейку/диапазон ячеек, которая
не будет изменяться при добавлении/удалении строк/столбцов, а также копировании,
Вы можете воспользоваться любым из трёх нижеопубликованных вариантов,
естественно, учитывая их особенности.
Вариант I.
=ДВССЫЛ("A3")
=ДВССЫЛ("Лист1!A3")
=ДВССЫЛ(АДРЕС(3;1;;;"Лист1"))
Комментарий : В данном примере предполагается, что в рабочей
книге, содержащей подобные формулы, присутствует рабочий лист с именем "Лист1"
и это имя, в дальнейшем, меняться не будет.
=ДВССЫЛ("[Имя_открытой_рабочей_книги.xls]Лист1!A3")
=ДВССЫЛ(АДРЕС(3;1;;;"[Имя_открытой_рабочей_книги.xls]Лист1"))
Комментарий : В этом примере также предполагается, что в рабочей
книге, содержится рабочий лист "Лист1", кроме того рабочая книга обязательно
должна быть открыта.
Вариант II.
=ИНДЕКС(1:65536;3;1)
=ИНДЕКС(Лист1!1:65536;3;1)
=ИНДЕКС([Имя_открытой_рабочей_книги.xls]Лист1!$1:$65536;3;1)
=ИНДЕКС('C:\Папка\[Имя_закрытой_рабочей_книги.xls]Лист1'!$1:$65536;3;1)
Комментарий : Используя этот вариант Вы можете получить данные
(но не ссылку) ячейки/диапазона закрытой рабочей книги.
В MS Excel 95 необходимо заменить 65536 на 16384
Вариант III.
В меню Вставка выберите пункт Имя, далее выберите
команду Присвоить. В диалоговом окне Присвоение имени в поле
Имя введите нужное имя, например ССЫЛКА,
а в поле Формула введите следущую формулу :
=!$A$3
и нажмите кнопку ОК. Затем, выберите нужную ячейку и введите именованную
формулу =ССЫЛКА
Комментарий : Подобная формула не будет пересчитываться
при изменении данных в ячейке, однако, этого можно избежать, если немного
изменить формулу =СМЕЩ(!$A$3;0;0)
Ответ :
Для того, чтобы создать ссылку на ячейку с использованием переменных :
имя листа, номер строки и буквенное обозначение столбца, достаточно
воспользоваться стандартной функцией рабочего листа =ДВССЫЛ()
К примеру, если в рабочей книге, содержащей нижеприведённые формулы,
существует рабочий лист с именем Лист№1 (и это имя, в дальнейшем, меняться
не будет) и нам необходимо получить значение его ячейки A7, то
в таком случае, введите необходимые данные() в указанные ячейки и примените
следующую формулу :
=ДВССЫЛ("'"&A1&"'!"&A2&A3)
=ДВССЫЛ(СЦЕПИТЬ("'";A1;"'!";A2;A3))
Предполагается, что :
Ячейка A1 содержит имя листа - Лист№1
Ячейка A2 содержит название столбца - A
А ячейка A3 содержит номер строки - 7
Вариант II.
Если столбец также необходимо указать в виде номера и/или Вы не хотите
явно указывать апострофы, хотя в предыдущем варианте они необходимы, то в
таком случае, можно просто добавить стандартную функцию =АДРЕС()
=ДВССЫЛ(АДРЕС(A3;A2;;;A1))
Предполагается, что :
Ячейка A1 содержит имя листа - Лист№1
Ячейка A2 содержит номер столбца - 1
А ячейка A3 содержит номер строки - 7
Комментарий : Если необходимо получить ссылку на ячейку/диапазон
открытой рабочей книги, то используйте следующий синтасис :
=ДВССЫЛ("'["&A1&"]"&A2&"'!"&A3)
=ДВССЫЛ(АДРЕС(A4;A5;;;"["&A1&"]"&A2))
=ДВССЫЛ(АДРЕС(4;2;;;"["&A1&"]"&A2))
В этих примерах предполагается, что :
A1 содержит имя книги - Архив.xls
A2 содержит имя листа - Расходы 2005
A3 содержит адрес ячейки - B4
A4 содержит номер строки - 4 (формула#2)
A5 содержит номер столбца - 2 (формула#2)
Ответ :
Для того, чтобы с помощью формул получить "число" с заданным числом
знаков после запятой, достаточно воспользоваться любым из нижеопубликованных
вариантов.
Вариант I.
=ФИКСИРОВАННЫЙ(15,789;5)
=ФИКСИРОВАННЫЙ(A1;B1)
Вариант II.
=ТЕКСТ(15,789;","&ПОВТОР(0;5))
=ТЕКСТ(A1;","&ПОВТОР(0;B1))
Предполагается, что :
Ячейка A1 содержит исходное число
Ячейка B1 содержит число = необходимое количество знаков,
после запятой
Комментарий : Обратите внимание на то, что обе формулы
возвращают текст, так что если Вам необходим только визуальный эффект,
то в таком случае, вполне достаточно просто установить у ячейки с числом
нужный формат, а именно 0,00000
Ответ :
Для того, чтобы получить абсолютную величину числа (число без знака),
можно воспользоваться любой из нижеопубликованных формул :
=ABS(A1)
=A1*ЗНАК(A1)
=ЕСЛИ(A1>0;A1;-A1)
=ЕСЛИ(A1>0;A1;A1*-1)
=A1*ЕСЛИ(A1<0;-1;1)
Предполагается, что :
Ячейка A1 содержит число, модуль которого необходимо получить
Ответ :
Для того, чтобы получить остаток от деления, можно
воспользоваться любой из нижеопубликованных формул :
=ОСТАТ(A1;A2)
=A1-A2*ЦЕЛОЕ(A1/A2)
Предполагается, что :
Ячейка A1 содержит число, остаток от деления которого необходимо получить
Ячейка A2 содержит делитель, т.е. число, на которое нужно разделить.
Комментарий : Несмотря на то, что вторая формула более
"громоздкая", она позволяет получить результат даже в тех случаях,
когда стандартная функция рабочего листа =ОСТАТ() возвращает значение
ошибки #ЧИСЛО!
Ответ :
Перевод Цельсий °C в Фаренгейт °F :
Вариант I.
=A1*1,8+32
Вариант II.
=ПРЕОБР(A1;"C";"F")
=ПРЕОБР(A1;"cel";"fah")
Перевод Фаренгейт °F в Цельсий °C :
Вариант I.
=(B1-32)/1,8
Вариант II.
=ПРЕОБР(B1;"F";"C")
=ПРЕОБР(B1;"fah";"cel")
Предполагается, что :
Ячейки A1 и B1 содержат градусы(числа), которые необходимо
перевести(преобразовать) из одной системы в другую.
Примечание : Если функция =ПРЕОБР() возвращает значение ошибки
#ИМЯ? или #ЗНАЧ!, то Вам необходимо установить надстройку Пакет Анализа
[См. примечание]
|
|