Пʼятниця, 6 вересня 2002 р. 22:40:07 UTC+3 користувач John Avitt написав:
I want to do bitwise comparisons in Excel *without* using VBA.
This is simple with VBA, but I want to do it without VBA, so
users won't see the disable/enable macros dialog on open.

Thank you.

John

in 2007 (transp view ;)

IP-Address 4.69.17.254
Subnet Mask 255.255.255.248
addr_dot_1 =FIND(".";Table4[[#This row];[IP-Address]];1)
mask_dot_1 =FIND(".";Table4[[#This row];[Subnet Mask]];1)
addr_dot_2 =FIND(".";Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_1]]+1)
mask_dot_2 =FIND(".";Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_1]]+1)
addr_dot_3 =FIND(".";Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_2]]+1)
mask_dot_3 =FIND(".";Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_2]]+1)
addr_first =MID(Table4[[#This row];[IP-Address]];1;Table4[[#This row];[addr_dot_1]]-1)
mask_first =MID(Table4[[#This row];[Subnet Mask]];1;Table4[[#This row];[mask_dot_1]]-1)
addr_second =MID(Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_1]]+1;Table4[[#This row];[addr_dot_2]]-Table4[[#This row];[addr_dot_1]]-1)
mask_second =MID(Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_1]]+1;Table4[[#This row];[mask_dot_2]]-Table4[[#This row];[mask_dot_1]]-1)
addr_third =MID(Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_2]]+1;Table4[[#This row];[addr_dot_3]]-Table4[[#This row];[addr_dot_2]]-1)
mask_third =MID(Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_2]]+1;Table4[[#This row];[mask_dot_3]]-Table4[[#This row];[mask_dot_2]]-1)
addr_fourth =MID(Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_3]]+1;LEN(Table4[[#This row];[IP-Address]])-Table4[[#This row];[addr_dot_3]])
mask_fourth =MID(Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_3]]+1;LEN(Table4[[#This row];[Subnet Mask]])-Table4[[#This row];[mask_dot_3]])
addr_first2 =DEC2BIN(Table4[[#This row];[addr_first]];8)
mask_first3 =DEC2BIN(Table4[[#This row];[mask_first]];8)
addr_second4 =DEC2BIN(Table4[[#This row];[addr_second]];8)
mask_second5 =DEC2BIN(Table4[[#This row];[mask_second]];8)
addr_third6 =DEC2BIN(Table4[[#This row];[addr_third]];8)
mask_third7 =DEC2BIN(Table4[[#This row];[mask_third]];8)
addr_fourth8 =DEC2BIN(Table4[[#This row];[addr_fourth]];8)
mask_fourth9 =DEC2BIN(Table4[[#This row];[mask_fourth]];8)
addr_bin_concatenate =CONCATENATE(Table4[[#This row];[addr_first2]];Table4[[#This row];[addr_second4]];Table4[[#This row];[addr_third6]];Table4[[#This row];[addr_fourth8]])
mask_bin_concatenate =CONCATENATE(Table4[[#This row];[mask_first3]];Table4[[#This row];[mask_second5]];Table4[[#This row];[mask_third7]];Table4[[#This row];[mask_fourth9]])
0_pos_in_mask =FIND(0;Table4[[#This row];[mask_bin_concatenate]];1)
network_bin_arrd_part =LEFT(Table4[[#This row];[addr_bin_concatenate]];Table4[[#This row];[0_pos_in_mask]]-1)
network_bin_mask_part =RIGHT(Table4[[#This row];[mask_bin_concatenate]];33-Table4[[#This row];[0_pos_in_mask]])
network_bin_ =CONCATENATE(Table4[[#This row];[network_bin_arrd_part]];Table4[[#This row];[network_bin_mask_part]])
network_len =LEN(Table4[[#This row];[network_bin_]])
network_bin_1 =MID(Table4[[#This row];[network_bin_]];1;8)
network_bin_2 =MID(Table4[[#This row];[network_bin_]];9;8)
network_bin_3 =MID(Table4[[#This row];[network_bin_]];17;8)
network_bin_4 =MID(Table4[[#This row];[network_bin_]];25;32)
network_dec_1 =BIN2DEC(Table4[[#This row];[network_bin_1]])
network_dec_2 =BIN2DEC(Table4[[#This row];[network_bin_2]])
network_dec_3 =BIN2DEC(Table4[[#This row];[network_bin_3]])
network_dec_4 =BIN2DEC(Table4[[#This row];[network_bin_4]])
network_address =CONCATENATE(Table4[[#This row];[network_dec_1]];".";Table4[[#This row];[network_dec_2]];".";Table4[[#This row];[network_dec_3]];".";Table4[[#This row];[network_dec_4]])