|
|
|
Примечание : Наличие фигурных скобок {...} означает, что речь идёт о формулах массива
Для того, чтобы с помощью формул преобразовать "число", где в качестве разделителя целой и дробной части используется . (точка) в число, где разделителем будет выступать , (запятая) достаточно использовать любую из нижеприведённых формул. =ЗНАЧЕН(ПОДСТАВИТЬ(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)) Предполагается, что : Вариант II. Если столбец также необходимо указать в виде номера и/или Вы не хотите явно указывать апострофы, хотя в предыдущем варианте они необходимы, то в таком случае, можно просто добавить стандартную функцию =АДРЕС() =ДВССЫЛ(АДРЕС(A3;A2;;;A1)) Предполагается, что : Комментарий : Если необходимо получить ссылку на ячейку/диапазон открытой рабочей книги, то используйте следующий синтасис : =ДВССЫЛ("'["&A1&"]"&A2&"'!"&A3) =ДВССЫЛ(АДРЕС(A4;A5;;;"["&A1&"]"&A2)) =ДВССЫЛ(АДРЕС(4;2;;;"["&A1&"]"&A2)) В этих примерах предполагается, что : Для того, чтобы с помощью формул получить "число" с заданным числом знаков после запятой, достаточно воспользоваться любым из нижеопубликованных вариантов. Вариант I. =ФИКСИРОВАННЫЙ(15,789;5) =ФИКСИРОВАННЫЙ(A1;B1) Вариант II. =ТЕКСТ(15,789;","&ПОВТОР(0;5)) =ТЕКСТ(A1;","&ПОВТОР(0;B1)) Предполагается, что : Комментарий : Обратите внимание на то, что обе формулы возвращают текст, так что если Вам необходим только визуальный эффект, то в таком случае, вполне достаточно просто установить у ячейки с числом нужный формат, а именно 0,00000 Для того, чтобы получить абсолютную величину числа (число без знака), можно воспользоваться любой из нижеопубликованных формул : =ABS(A1) =A1*ЗНАК(A1) =ЕСЛИ(A1>0;A1;-A1) =ЕСЛИ(A1>0;A1;A1*-1) =A1*ЕСЛИ(A1<0;-1;1) Предполагается, что : Для того, чтобы получить остаток от деления, можно воспользоваться любой из нижеопубликованных формул : =ОСТАТ(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") Предполагается, что : Примечание : Если функция =ПРЕОБР() возвращает значение ошибки #ИМЯ? или #ЗНАЧ!, то Вам необходимо установить надстройку Пакет Анализа [См. примечание]
|
Вопросы - Синонимы
Воспроизведение любых опубликованных здесь материалов возможно только с
письменного разрешения автора :